Documentation Source Text

Check-in [fa4e2ba5b2]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Updates to the query planner stability guarantee description to make it clear that it is off by default.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: fa4e2ba5b26137d978abead933a186c543bd90a362b9c5a61fdf5530be6a6973
User & Date: drh 2017-06-29 14:53:11.608
Context
2017-06-29
17:33
Mention the sqlite3_prepare_v3() enhancement in the change log. (check-in: 9421ab387b user: drh tags: trunk)
14:53
Updates to the query planner stability guarantee description to make it clear that it is off by default. (check-in: fa4e2ba5b2 user: drh tags: trunk)
14:47
Initial draft of release notes for version 3.20.0. (check-in: fe054454f3 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/queryplanner-ng.in.
︙︙
92
93
94
95
96
97
98
99
100
101

102
103
104
105
106
107
108
109
110
111
112
113



114
115
116
117
118
119
120
121
122

<li> Picking the nested order of the various loops
<li> Choosing good indices for each loop
</ol>
<p>Picking the nesting order is generally the more challenging problem.
Once the nesting order of the join is established, the choice of indices
for each loop is normally obvious.</p>

<tcl>hd_fragment qpstab {query planner stability guarantee}</tcl>
<h2> The SQLite Query Planner Stability Guarantee</h2>


<p>SQLite will always pick the same query plan for any
given SQL statement as long as:

<ol type="a">
<li>the database schema does not change in significant ways such as 
    adding or dropping indices,</li>
<li>the ANALYZE command is not rerun, </li>
<li>SQLite is not compiled with [SQLITE_ENABLE_STAT3]
    or [SQLITE_ENABLE_STAT4], and</li>
<li>the same version of SQLite is used.</li>
</ol>




<p>The SQLite stability guarantee means that if all of your queries run 
efficiently
during testing, and if your application does not change the schema,
then SQLite will not suddenly decide to start using a different
query plan, possibly causing a performance problem, after your application 
is released to users.  If your application works in the lab, it
will continue working the same way after deployment.</p>

<p>Enterprise-class client/server SQL database engines do not normally 








|


>
|






<
<



>
>
>
|
|








92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109


110
111
112
113
114
115
116
117
118
119
120
121
122
123
124

<li> Picking the nested order of the various loops
<li> Choosing good indices for each loop
</ol>
<p>Picking the nesting order is generally the more challenging problem.
Once the nesting order of the join is established, the choice of indices
for each loop is normally obvious.</p>

<tcl>hd_fragment qpstab {query planner stability guarantee} QPSG</tcl>
<h2> The SQLite Query Planner Stability Guarantee</h2>

<p>When the Query Planner Stability Guarantee (QPSG) is enabled
SQLite will always pick the same query plan for any
given SQL statement as long as:

<ol type="a">
<li>the database schema does not change in significant ways such as 
    adding or dropping indices,</li>
<li>the ANALYZE command is not rerun, </li>


<li>the same version of SQLite is used.</li>
</ol>

<p>The QPSG is disabled by default.  It can be enabled at compile-time
using the [SQLITE_ENABLE_QPSG] compile-time option, or at run-time by
invoking [sqlite3_db_config](db,[SQLITE_DBCONFIG_ENABLE_QPSG],1,0).

<p>The QPSG means that if all of your queries run efficiently
during testing, and if your application does not change the schema,
then SQLite will not suddenly decide to start using a different
query plan, possibly causing a performance problem, after your application 
is released to users.  If your application works in the lab, it
will continue working the same way after deployment.</p>

<p>Enterprise-class client/server SQL database engines do not normally
︙︙
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146

for the evolving structure of the data.  But sometimes the new query plan will
cause a performance reduction.  With a client/server database engine, there
is typically a Database Administrator (DBA) on hand to deal with these 
rare problems as they come up.  But DBAs are not available to fix problems 
in an embedded database like SQLite, and hence SQLite is careful to
ensure that plans do not change unexpectedly after deployment.</p>

<p>The SQLite stability guarantee applies equally to the legacy query planner 
and to the NGQP.</p>

<p>It is important to note that changing versions of SQLite might cause
changes in query plans.
The same version of SQLite will
always pick the same query plan, but if you relink your application to use
a different version of SQLite, then query plans might change.  In rare
cases, an SQLite version change might lead to a performance regression.  
This is one reason








<
<
<








132
133
134
135
136
137
138



139
140
141
142
143
144
145

for the evolving structure of the data.  But sometimes the new query plan will
cause a performance reduction.  With a client/server database engine, there
is typically a Database Administrator (DBA) on hand to deal with these 
rare problems as they come up.  But DBAs are not available to fix problems 
in an embedded database like SQLite, and hence SQLite is careful to
ensure that plans do not change unexpectedly after deployment.</p>




<p>It is important to note that changing versions of SQLite might cause
changes in query plans.
The same version of SQLite will
always pick the same query plan, but if you relink your application to use
a different version of SQLite, then query plans might change.  In rare
cases, an SQLite version change might lead to a performance regression.  
This is one reason
︙︙