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. |
|---|---|
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
fa4e2ba5b26137d978abead933a186c5 |
| User & Date: | drh 2017-06-29 14:53:11 |
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
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
...
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
<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
................................................................................
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
|
|
>
|
<
<
>
>
>
>
|
<
<
<
<
|
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
...
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
<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
................................................................................
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
|