Documentation Source Text

Check-in [04ab4fa5ec]
Login

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

Overview
Comment:Add documentation for STAT4.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 04ab4fa5eccfca54e8618c3513751d4edb616a00
User & Date: drh 2013-08-28 14:13:39
Context
2013-08-29
16:51
If the user tries a download hyperlink without javascript, send them to an explanation page. check-in: 32aeca2648 user: drh tags: trunk
2013-08-28
14:13
Add documentation for STAT4. check-in: 04ab4fa5ec user: drh tags: trunk
2013-08-27
13:34
Fix typos in the queryplanner-ng document. check-in: 9747293881 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/compile.in.

590
591
592
593
594
595
596











597
598
599
600
601
602
603
  This option adds additional logic to the [ANALYZE] command and to
  the [query planner] that can help SQLite to chose a better query plan
  under certain situations.  The [ANALYZE] command is enhanced to collect
  histogram data from each index and store that data
  in the <b>sqlite_stat3</b> table.  The query planner will then use the
  histogram data to help it make better index choices.
}












COMPILE_OPTION {SQLITE_ENABLE_TREE_EXPLAIN} {
  This option adds support for the [SQLITE_TESTCTRL_EXPLAIN_STMT] test-control
  in the SQLite core.  When the [command-line shell] is also compiled with
  this option, the ".explain" dot-command enables a mode that uses the
  [SQLITE_TESTCTRL_EXPLAIN_STMT] interface to display an ASCII-art diagram
  of the parse tree for each SQL query statement that is run in the shell.







>
>
>
>
>
>
>
>
>
>
>







590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
  This option adds additional logic to the [ANALYZE] command and to
  the [query planner] that can help SQLite to chose a better query plan
  under certain situations.  The [ANALYZE] command is enhanced to collect
  histogram data from each index and store that data
  in the <b>sqlite_stat3</b> table.  The query planner will then use the
  histogram data to help it make better index choices.
}

COMPILE_OPTION {SQLITE_ENABLE_STAT4} {
  This option adds additional logic to the [ANALYZE] command and to
  the [query planner] that can help SQLite to chose a better query plan
  under certain situations.  The [ANALYZE] command is enhanced to collect
  histogram data from each index and store that data
  in the <b>sqlite_stat4</b> table.  The query planner will then use the
  histogram data to help it make better index choices.
  <p>
  [SQLITE_ENABLE_STAT3] is a no-op if this compile-time option is used.
}

COMPILE_OPTION {SQLITE_ENABLE_TREE_EXPLAIN} {
  This option adds support for the [SQLITE_TESTCTRL_EXPLAIN_STMT] test-control
  in the SQLite core.  When the [command-line shell] is also compiled with
  this option, the ".explain" dot-command enables a mode that uses the
  [SQLITE_TESTCTRL_EXPLAIN_STMT] interface to display an ASCII-art diagram
  of the parse tree for each SQL query statement that is run in the shell.

Changes to pages/fileformat2.in.

1233
1234
1235
1236
1237
1238
1239
1240

1241
1242
1243



1244
1245
1246
1247
1248
1249
1250
....
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271

































1272
1273
1274
1275


1276





1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
<p>The format for sqlite_stat2 is recorded here for legacy reference.  
Recent versions of SQLite no longer support sqlite_stat2 and the
sqlite_stat2 table, it is exists, is simply ignored.

<tcl>hd_fragment stat3tab {sqlite_stat3} SQLITE_STAT3</tcl>
<h4>2.5.5 The sqlite_stat3 table</h4>

<p>The sqlite_stat3 is only created and is only used if SQLite is compiled

with [SQLITE_ENABLE_STAT3] and if the SQLite version number is
3.7.9 or greater.  The sqlite_stat3 table is neither read nor written by any
version of SQLite before 3.7.9.



The sqlite_stat3 table contains additional information
about the distribution of keys within an index, information that the
query planner can use to devise better and faster query algorithms.
The schema of the sqlite_stat3 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat3(tbl,idx,nEq,nLt,nDLt,sample);
................................................................................
the sample.  
The sqlite_stat3.nLt holds the approximate number of entries in the
index whose left-most column is less than the sample.
The sqlite_stat3.nDLt column holds the approximate
number of distinct left-most entries in the index that are less than
the sample.

<p>Future versions of SQLite might change to store a string containing
multiple integers values in the sqlite_stat3.nDLt column, a string in which
the first integer will be the number of prior index entries that are
distinct in the left-most column, the second integer is the number of

































prior index entries that are distinct in the first two columns, the
third integer is the number of prior index entries that are distinct
in the first three columns, and so forth  With such an extension, the
nDLt field will become similar in function to the sqlite_stat1.stat field.








<p>There can be an arbitrary number of sqlite_stat3 entries per index.
The [ANALYZE] command will typically generate sqlite_stat3 tables
that contain between 10 and 40 samples that are distributed across
the key space and with large nEq values.

<tcl>hd_fragment rollbackjournal {rollback journal format}</tcl>
<h2>3.0 The Rollback Journal</h2>

<p>The rollback journal is a file associated with each SQLite database
file that hold information used to restore the database file to its initial
state during the course of a transaction.







|
>
|
|

>
>
>







 







|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
<
<
>
>

>
>
>
>
>
|
|









1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
....
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309



1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
<p>The format for sqlite_stat2 is recorded here for legacy reference.  
Recent versions of SQLite no longer support sqlite_stat2 and the
sqlite_stat2 table, it is exists, is simply ignored.

<tcl>hd_fragment stat3tab {sqlite_stat3} SQLITE_STAT3</tcl>
<h4>2.5.5 The sqlite_stat3 table</h4>

<p>The sqlite_stat3 is only only used if SQLite is compiled
with [SQLITE_ENABLE_STAT3] or [SQLITE_ENABLE_STAT4]
and if the SQLite version number is 3.7.9 or greater.
The sqlite_stat3 table is neither read nor written by any
version of SQLite before 3.7.9.
If the [SQLITE_ENABLE_STAT4] compile-time option is used and the
SQLite version number is 3.8.1 or greater, then sqlite_stat3 might
be read but not written.
The sqlite_stat3 table contains additional information
about the distribution of keys within an index, information that the
query planner can use to devise better and faster query algorithms.
The schema of the sqlite_stat3 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat3(tbl,idx,nEq,nLt,nDLt,sample);
................................................................................
the sample.  
The sqlite_stat3.nLt holds the approximate number of entries in the
index whose left-most column is less than the sample.
The sqlite_stat3.nDLt column holds the approximate
number of distinct left-most entries in the index that are less than
the sample.

<p>There can be an arbitrary number of sqlite_stat3 entries per index.
The [ANALYZE] command will typically generate sqlite_stat3 tables
that contain between 10 and 40 samples that are distributed across
the key space and with large nEq values.

<tcl>hd_fragment stat4tab {sqlite_stat4} SQLITE_STAT4</tcl>
<h4>2.5.6 The sqlite_stat4 table</h4>

<p>The sqlite_stat4 is only created and is only used if SQLite is compiled
with [SQLITE_ENABLE_STAT4] and if the SQLite version number is
3.8.1 or greater.  The sqlite_stat4 table is neither read nor written by any
version of SQLite before 3.8.1.
The sqlite_stat4 table contains additional information
about the distribution of keys within an index, information that the
query planner can use to devise better and faster query algorithms.
The schema of the sqlite_stat4 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat4(tbl,idx,nEq,nLt,nDLt,sample);
</pre></blockquote>

<p>There are usually multiple entries in the sqlite_stat4 table for each index.
The sqlite_stat4.sample column holds the content of an index entry in
the [record format].  The index entry stored is taken from the
index identified by the sqlite_stat4.idx and the sqlite_stat4.tbl columns.
If the sqlite_stat4.idx and sqlite_stat4.tbl
columns hold the same value, then that row contains a sample from
the [INTEGER PRIMARY KEY] of the table.
The sqlite_stat4.nEq column holds a list of integer where the K-th integer
is the approximate number of entries in the index whose left-most K columns
exactly match the K left-most columns of the sample.
The sqlite_stat4.nLt holds a list of integers where the K-th integer is
the approximate number of entries in the
index whose K left-most columns are collectively less than the 
K left-most columns of the sample.
The sqlite_stat4.nDLt column holds a list of intgers where the K-th 
integers is the approximate
number of entries in the index that are distinct in the first K columns and



that are whose left-most K columns are collectively less than the left-most
K columns of the sample.

<p>The sqlite_stat4 is a generalization of the sqlite_stat3 table.  The
sqlite_stat3 table provides information about the left-most column of an
index whereas the sqlite_stat4 table provides information about all columns
of the index.

<p>There can be an arbitrary number of sqlite_stat4 entries per index.
The [ANALYZE] command will typically generate sqlite_stat4 tables
that contain between 10 and 40 samples that are distributed across
the key space and with large nEq values.

<tcl>hd_fragment rollbackjournal {rollback journal format}</tcl>
<h2>3.0 The Rollback Journal</h2>

<p>The rollback journal is a file associated with each SQLite database
file that hold information used to restore the database file to its initial
state during the course of a transaction.

Changes to pages/lang.in.

197
198
199
200
201
202
203

204
205



206
207
208
209
210
211
212
213
214
215
216
217
218
and indices in that one database are analyzed.  
^If the argument is a table name, then only that table and the
indices associated with that table are analyzed.  ^If the argument
is an index name, then only that one index is analyzed.</p>

<p> ^The default implementation stores all statistics in a single
table named "[sqlite_stat1]".  ^If SQLite is compiled with the

[SQLITE_ENABLE_STAT3] option, then additional histogram data is
collected and stored in [sqlite_stat3].



Older versions of SQLite would make use of the [sqlite_stat2] table
when compiled with [SQLITE_ENABLE_STAT2] but all recent versions of
SQLite ignore the sqlite_stat2 table.
Future enhancements may create
additional [internal tables] with the same name pattern except with
final digit larger than "3".</p>

<p> ^The [ALTER TABLE] command does
not work on the sqlite_stat1 or sqlite_stat3 tables,
but all the content of those tables can be queried using [SELECT]
and can be deleted, augmented, or modified using the [DELETE],
[INSERT], and [UPDATE] commands.
^(The [DROP TABLE] command works on sqlite_stat1 and







>
|

>
>
>





|







197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
and indices in that one database are analyzed.  
^If the argument is a table name, then only that table and the
indices associated with that table are analyzed.  ^If the argument
is an index name, then only that one index is analyzed.</p>

<p> ^The default implementation stores all statistics in a single
table named "[sqlite_stat1]".  ^If SQLite is compiled with the
[SQLITE_ENABLE_STAT3] option and without the [SQLITE_ENABLE_STAT4]
option, then additional histogram data is
collected and stored in [sqlite_stat3].
 ^If SQLite is compiled with the
[SQLITE_ENABLE_STAT4] option, then additional histogram data is
collected and stored in [sqlite_stat4].
Older versions of SQLite would make use of the [sqlite_stat2] table
when compiled with [SQLITE_ENABLE_STAT2] but all recent versions of
SQLite ignore the sqlite_stat2 table.
Future enhancements may create
additional [internal tables] with the same name pattern except with
final digit larger than "4".</p>

<p> ^The [ALTER TABLE] command does
not work on the sqlite_stat1 or sqlite_stat3 tables,
but all the content of those tables can be queried using [SELECT]
and can be deleted, augmented, or modified using the [DELETE],
[INSERT], and [UPDATE] commands.
^(The [DROP TABLE] command works on sqlite_stat1 and

Changes to pages/optoverview.in.

794
795
796
797
798
799
800
801

802
803
804




805
806
807
808
809
810
811
  the range constraint on column x should reduce the search space by
  a factor of 10,000 whereas the range constraint on column y should
  reduce the search space by a factor of only 10.  So the ex2i1 index
  should be preferred.
}
PARAGRAPH {
  ^SQLite will make this determination, but only if it has been compiled
  with [SQLITE_ENABLE_STAT3].  ^The [SQLITE_ENABLE_STAT3] option causes

  the [ANALYZE] command to collect a histogram of column content in the
  [sqlite_stat3] table and to use this histogram to make a better
  guess at the best query to use for range constraints such as the above.




}
PARAGRAPH {
  ^The histogram data is only useful if the right-hand side of the constraint
  is a simple compile-time constant or [parameter] and not an expression.
}
PARAGRAPH {
  ^Another limitation of the histogram data is that it only applies to the







|
>

|
|
>
>
>
>







794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
  the range constraint on column x should reduce the search space by
  a factor of 10,000 whereas the range constraint on column y should
  reduce the search space by a factor of only 10.  So the ex2i1 index
  should be preferred.
}
PARAGRAPH {
  ^SQLite will make this determination, but only if it has been compiled
  with [SQLITE_ENABLE_STAT3] or [SQLITE_ENABLE_STAT4].
  ^The [SQLITE_ENABLE_STAT3] and [SQLITE_ENABLE_STAT4] options causes
  the [ANALYZE] command to collect a histogram of column content in the
  [sqlite_stat3] or [sqlite_stat4] tables and to use this histogram to 
  make a better guess at the best query to use for range constraints
  such as the above.  The main difference between STAT3 and STAT4 is
  that STAT3 records histogram data for only the left-most column of
  an index whereas STAT4 records histogram data for all columns of an
  index.  For single-column indexes, STAT3 and STAT4 work the same.
}
PARAGRAPH {
  ^The histogram data is only useful if the right-hand side of the constraint
  is a simple compile-time constant or [parameter] and not an expression.
}
PARAGRAPH {
  ^Another limitation of the histogram data is that it only applies to the

Changes to pages/queryplanner-ng.in.

99
100
101
102
103
104
105
106

107
108
109
110
111
112
113

<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], and</li>

<li>the same version of SQLite is used.</li>
</ol>
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 







|
>







99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

<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>
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