SQLite Forum

Query planner stability guarantee with SQLITE_ENABLE_STAT4

Query planner stability guarantee with SQLITE_ENABLE_STAT4

(1) By anonymous on 2020-06-14 01:53:06 [link] [source]

If the points below are true:

  • SQLite is compiled with SQLITE_ENABLE_STAT4
  • SQLite is not compiled with SQLITE_ENABLE_QPSG
  • the QPSG is enabled at run-time using the SQLITE_DBCONFIG_ENABLE_QPSG option

Which one (if any) is the result:

  • the QPSG remains disabled and there is no way to enable it
  • the QPSG is enabled but now the ANALYZE command has a greater chance (compared to when SQLITE_ENABLE_STAT4 is not defined) to change the query plan

(2) By Keith Medcalf (kmedcalf) on 2020-06-14 15:54:28 in reply to 1 [source]

Neither. The query plan is not stable in the face of ANALYZE.

In order for the QPSG to hold you must not run additional ANALYZE. That is, the following must remain invariant:

  • the database schema

  • ANALYZE must not be re-run

In addition, if SQLITE_ENABLE_STAT4 has been used then one must also hold the query parameters which may affect the plan invariant.

(3) By Keith Medcalf (kmedcalf) on 2020-06-16 15:13:27 in reply to 1 [link] [source]

See also this post