1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
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
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>ANALYZE</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
ANALYZE
</div>
<div class="fancy_toc">
<a onclick="toggle_toc()">
<span class="fancy_toc_mark" id="toc_mk">►</span>
Table Of Contents
</a>
<div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div>
<div class="fancy-toc1"><a href="#recommended_usage_patterns">2. Recommended usage patterns</a></div>
<div class="fancy-toc2"><a href="#periodically_run_pragma_optimize_">2.1. Periodically run "PRAGMA optimize"</a></div>
<div class="fancy-toc2"><a href="#fixed_results_of_analyze">2.2. Fixed results of ANALYZE</a></div>
<div class="fancy-toc1"><a href="#details">3. Details</a></div>
<div class="fancy-toc1"><a href="#automatically_running_analyze">4. Automatically Running ANALYZE</a></div>
<div class="fancy-toc1"><a href="#approximate_analyze_for_large_databases">5. Approximate ANALYZE For Large Databases</a></div>
<div class="fancy-toc2"><a href="#limitations_of_approximate_analyze">5.1. Limitations of approximate ANALYZE</a></div>
</div>
</div>
<script>
function toggle_toc(){
var sub = document.getElementById("toc_sub")
var mk = document.getElementById("toc_mk")
if( sub.style.display!="block" ){
sub.style.display = "block";
mk.innerHTML = "▼";
} else {
sub.style.display = "none";
mk.innerHTML = "►";
}
}
</script>
</div>
<h1 id="overview"><span>1. </span>Overview</h1>
<p><b><a href="syntax/analyze-stmt.html">analyze-stmt:</a></b>
<button id='x3115000d' onclick='hideorshow("x3115000d","x4b619059")'>hide</button></p>
<div id='x4b619059' class='imgcontainer'>
<div style="max-width:654px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 654.557 140.4">
<circle cx="5" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="32,17 20,21 20,12" style="fill:rgb(0,0,0)"/>
<path d="M9,17L26,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M47,32L111,32A15 15 0 0 0 127 17A15 15 0 0 0 111 2L47,2A15 15 0 0 0 32 17A15 15 0 0 0 47 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="79" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">ANALYZE</text>
<polygon points="150,17 138,21 138,12" style="fill:rgb(0,0,0)"/>
<path d="M127,17L144,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="188,123 176,127 176,118" style="fill:rgb(0,0,0)"/>
<path d="M150,17 L 157,17 Q 165,17 165,32 L 165,108 Q 165,123 173,123 L 182,123" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M203,138L299,138A15 15 0 0 0 314 123A15 15 0 0 0 299 108L203,108A15 15 0 0 0 188 123A15 15 0 0 0 203 138Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="251" y="123" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">schema-name</text>
<polygon points="337,123 326,127 326,118" style="fill:rgb(0,0,0)"/>
<path d="M314,123L332,123" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M352,138A15 15 0 0 0 368 123A15 15 0 0 0 352 108A15 15 0 0 0 337 123A15 15 0 0 0 352 138Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="352" y="123" text-anchor="middle" font-weight="bold" fill="rgb(0,0,0)" dominant-baseline="central">.</text>
<polygon points="391,123 379,127 379,118" style="fill:rgb(0,0,0)"/>
<path d="M368,123L385,123" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M406,138L556,138A15 15 0 0 0 571 123A15 15 0 0 0 556 108L406,108A15 15 0 0 0 391 123A15 15 0 0 0 406 138Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="481" y="123" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">table-or-index-name</text>
<polygon points="594,123 582,127 582,118" style="fill:rgb(0,0,0)"/>
<path d="M571,123L588,123" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="645,17 633,21 633,12" style="fill:rgb(0,0,0)"/>
<path d="M594,123 L 601,123 Q 609,123 609,108 L 609,32 Q 609,17 624,17 L 624,17 L 639,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<circle cx="648" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="397,17 386,21 386,12" style="fill:rgb(0,0,0)"/>
<path d="M150,17L391,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M397,17L633,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="188,47 176,51 176,43" style="fill:rgb(0,0,0)"/>
<path d="M150,17 L 157,17 Q 165,17 165,32 L 165,32 Q 165,47 173,47 L 182,47" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M203,62L299,62A15 15 0 0 0 314 47L314,47A15 15 0 0 0 299 32L203,32A15 15 0 0 0 188 47L188,47A15 15 0 0 0 203 62Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="251" y="47" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">schema-name</text>
<polygon points="594,47 582,51 582,43" style="fill:rgb(0,0,0)"/>
<path d="M314,47L588,47" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M594,47 L 601,47 Q 609,47 609,40 L 609,32" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="188,85 176,89 176,81" style="fill:rgb(0,0,0)"/>
<path d="M150,17 L 157,17 Q 165,17 165,32 L 165,70 Q 165,85 173,85 L 182,85" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M203,100L353,100A15 15 0 0 0 368 85A15 15 0 0 0 353 70L203,70A15 15 0 0 0 188 85A15 15 0 0 0 203 100Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="278" y="85" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">index-or-table-name</text>
<polygon points="594,85 582,89 582,81" style="fill:rgb(0,0,0)"/>
<path d="M368,85L588,85" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M594,85 L 601,85 Q 609,85 609,77 L 609,70" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
</svg>
</div>
</div>
<p> The ANALYZE command gathers statistics about tables and
indices and stores the collected information
in <a href="fileformat2.html#intschema">internal tables</a> of the database where the query optimizer can
access the information and use it to help make better query planning choices.
If no arguments are given, the main database and all attached databases are
analyzed. If a schema name is given as the argument, then all tables
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>
<a name="req"></a>
<h1 id="recommended_usage_patterns"><span>2. </span>Recommended usage patterns</h1>
<p> The use of ANALYZE is never required. However, if an application
makes complex queries that have many possible query plans, the query
planner will be better able to pick the best plan if ANALYZE has
been run. This can result it significant performance improvements for
some queries.
</p><p> Two recommended approaches for when and how to run ANALYZE are
described in the next subsections, in order of preference.
<a name="pragopt"></a>
</p><h2 id="periodically_run_pragma_optimize_"><span>2.1. </span>Periodically run "PRAGMA optimize"</h2>
<p>The <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> command will automatically run ANALYZE when
needed. Suggested use:
</p><ol>
<li><p>
Applications with short-lived database connections should run
"PRAGMA optimize;" once, just prior to closing each database connection.
</p></li><li><p>
Applications that use long-lived database connections should run
"PRAGMA optimize=0x10002;" when the connection is first opened, and then
also run "PRAGMA optimize;" periodically, perhaps once per day, or more if
the database is evolving rapidly.
</p></li><li><p>All applications should run "PRAGMA optimize;" after a schema change,
especially after one or more <a href="lang_createindex.html">CREATE INDEX</a> statements.
</p></li></ol>
<p>
</p><p>The <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> command is usually a no-op but it will occasionally
run one or more ANALYZE subcommands on individual tables of the database
if doing so will be useful to the query planner.
Since SQLite version 3.46.0 (2024-05-23), the "PRAGMA optimize" command
automatically limits the scope of ANALYZE subcommands so that
the overall "PRAGMA optimize" command completes quickly even on enormous
databases. There is no need to use <a href="pragma.html#pragma_analysis_limit">PRAGMA analysis_limit</a>. This is the
recommended way of running ANALYZE moving forward.
</p><p>The <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> command will normally only consider running ANALYZE on
tables that have been previously queried by the same database connection or
that do not have entries in the <a href="fileformat2.html#stat1tab">sqlite_stat1</a> table.
However, if the 0x10000 bit is added to the argument, PRAGMA optimize will
examine all tables to see if they can benefit from ANALYZE, not just those
that have been recently queried.
There is no query history when a database connection first opens, and
that is why adding the 0x10000 bit is recommended when running PRAGMA optimize
on a fresh database connection.
</p><p>See the <a href="lang_analyze.html#autoanalyze">Automatically Running ANALYZE</a> and
<a href="lang_analyze.html#approx">Approximate ANALYZE For Large Databases</a> sections below for additional
information.
<a name="statanal"></a>
</p><h2 id="fixed_results_of_analyze"><span>2.2. </span>Fixed results of ANALYZE</h2>
<p>Running ANALYZE can cause SQLite to choose different query plans
for subsequent queries. This is almost always a positive thing, as the
query plans chosen after ANALYZE will in nearly every case be better than
the query plans picked before ANALYZE. That is the whole point of ANALYZE.
But there can be no proof of running ANALYZE will always be beneficial.
One can construct pathological cases where running
ANALYZE could make some subsequent queries run slower.
</p><p>Some developers prefer that once the design of an application is frozen,
SQLite will always pick the same query plans as it did during
development and testing.
Then if a millions of copies of the application are shipped to customers,
the developers are assured that all of those millions of copies are running
the same query plans regardless of what data the individual customers insert
into their particular databases. This can help in reproducing complaints
of performance problems coming back from the field.
</p><p>To achieve this objection, never run a full ANALYZE nor the
"PRAGMA optimize" command in the application.
Rather, only run ANALYZE during development, manually using the
<a href="cli.html">command-line interface</a> or similar, on a test database
that is similar in size and content to live databases. Then capture
the result of this one-time ANALYZE using a script like the
following:
</p><div class="codeblock"><pre>.mode list
SELECT
'ANALYZE sqlite_schema;' ||
'DELETE FROM sqlite_stat1;' ||
'INSERT INTO sqlite_stat1(tbl,idx,stat)VALUES' ||
(SELECT group_concat(format('(%Q,%Q,%Q)',tbl,idx,stat),',')
FROM sqlite_stat1) ||
';ANALYZE sqlite_schema;';
</pre></div>
<p>When creating a new instance of the database in deployed instances of
the application, or perhaps every time the application is started up in
the case of long-running applications, run the commands generated by
script above. This will populate the <a href="fileformat2.html#stat1tab">sqlite_stat1</a> table exactly as
it was during development and testing and ensure that the query plans
selected in the field are same has those selected during testing in the
lab. Maybe copy/paste the string generated by the script above into
a static string constant named "zStat1Init" and then invoke:
</p><div class="codeblock"><pre>sqlite3_exec(db, zStat1Init, 0, 0, 0);
</pre></div>
<p>Perhaps also add "BEGIN;" at the start of the string constant and
"COMMIT;" at the end, depending on the context in which the script is run.
</p><p>See the <a href="queryplanner-ng.html#qpstab">query planner stability guarantee</a> for addition information.
</p><h1 id="details"><span>3. </span>Details</h1>
<p> The default implementation stores all statistics in a single
table named "<a href="fileformat2.html#stat1tab">sqlite_stat1</a>".
If SQLite is compiled with the
<a href="compile.html#enable_stat4">SQLITE_ENABLE_STAT4</a> option, then additional histogram data is
collected and stored in <a href="fileformat2.html#stat4tab">sqlite_stat4</a>.
Older versions of SQLite would make use of the <a href="fileformat2.html#stat2tab">sqlite_stat2</a> table
or <a href="fileformat2.html#stat3tab">sqlite_stat3</a> table
when compiled with <a href="compile.html#enable_stat2">SQLITE_ENABLE_STAT2</a> or <a href="compile.html#enable_stat3">SQLITE_ENABLE_STAT3</a>,
but all recent versions of
SQLite ignore the sqlite_stat2 and sqlite_stat3 tables.
Future enhancements may create
additional <a href="fileformat2.html#intschema">internal tables</a> with the same name pattern except with
final digit larger than "4".
All of these tables are collectively referred to as "statistics tables".
</p>
<p> The content of the statistics tables can be queried using <a href="lang_select.html">SELECT</a>
and can be changed using the <a href="lang_delete.html">DELETE</a>, <a href="lang_insert.html">INSERT</a>, and <a href="lang_update.html">UPDATE</a> commands.
The <a href="lang_droptable.html">DROP TABLE</a> command works on statistics tables
as of SQLite version 3.7.9. (2011-11-01)
The <a href="lang_altertable.html">ALTER TABLE</a> command does not work on statistics tables.
Appropriate care should be used when changing the content of the statistics
tables as invalid content can cause SQLite to select inefficient
query plans. Generally speaking, one should not modify the content of
the statistics tables by any mechanism other than invoking the
ANALYZE command.
See "<a href="optoverview.html#manctrl">Manual Control Of Query Plans Using SQLITE_STAT Tables</a>" for
further information.</p>
<p> Statistics gathered by ANALYZE are <u>not</u> updated as
the content of the database changes. If the content of the database
changes significantly, or if the database schema changes, then one should
consider rerunning the ANALYZE command in order to update the statistics.</p>
<p> The query planner loads the content of the statistics tables
into memory when the schema is read. Hence, when an application
changes the statistics tables directly, SQLite will not immediately
notice the changes. An application
can force the query planner to reread the statistics tables by running
<b>ANALYZE sqlite_schema</b>. </p>
<a name="autoanalyze"></a>
<h1 id="automatically_running_analyze"><span>4. </span>Automatically Running ANALYZE</h1>
<p>The <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> command will automatically run ANALYZE on individual
tables on an as-needed basis. The recommended practice is for applications
to invoke the <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> statement just before closing each database
connection. Or, if the application keeps a single database connection open
for a long time, then it should run "PRAGMA optimize=0x10002" when the
connection is first opened and run "PRAGMA optimize;" periodically thereafter,
perhaps once per day or even once per hour.</p>
<p>Each SQLite <a href="c3ref/sqlite3.html">database connection</a> records cases when the query planner would
benefit from having accurate results of ANALYZE at hand. These records
are held in memory and accumulate over the life of a database connection.
The <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> command looks at those records and runs ANALYZE on only
those tables for which new or updated ANALYZE data seems likely to be useful.
In most cases <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> will not run ANALYZE, but it will occasionally
do so either for tables that have never before been analyzed, or for tables
that have grown significantly since they were last analyzed.</p>
<p>Since the actions of <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> are determined to some extent by
prior queries that have been evaluated on the same database connection, it
is recommended that <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> be deferred until the database connection
is closing and has thus had an opportunity to accumulate as much usage information
as possible. It is also reasonable to set a timer to run <a href="pragma.html#pragma_optimize">PRAGMA optimize</a>
every few hours, or every few days, for database connections that stay open
for a long time. When running <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> immediately after a
database connection is opened, one can add the 0x10000 bit to the bitmask
argument (thus making the command read "PRAGMA optimize=0x10002") which
causes all tables to be examined, even tables that have not been
queried during the current connection.</p>
<p>The <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> command was first introduced with
SQLite 3.18.0 (2017-03-28) and is a no-op for all prior releases
of SQLite. The <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> command was significantly enhanced
in SQLite 3.46.0 (2024-05-23) and the advice given in this
documentation is based on those enhancements. Applications that
use earlier versions of SQLite should consult the corresponding
documentation for better advice on the best ways to use PRAGMA optimize.</p>
<a name="approx"></a>
<h1 id="approximate_analyze_for_large_databases"><span>5. </span>Approximate ANALYZE For Large Databases</h1>
<p>By default, ANALYZE does a full scan of every index. This can be slow for
large databases. So beginning with SQLite version 3.32.0 (2020-05-22), the
<a href="pragma.html#pragma_analysis_limit">PRAGMA analysis_limit</a> command can be used to limit the amount of
scanning performed by ANALYZE, and thus help ANALYZE to run faster,
even on very large database files. We call this running an
"approximate ANALYZE".
</p><p>The recommended usage pattern for the <a href="pragma.html#pragma_analysis_limit">analysis_limit</a> pragma is
like this:
</p><div class="codeblock"><pre>PRAGMA analysis_limit=1000;
</pre></div>
<p>This pragma tells the ANALYZE command to start a full scan
of the index as it normally would. But when the number of rows visited
reaches 1000 (or whatever other limit is specified by the pragma), the
ANALYZE command will begin taking actions to stop the scan. If
the left-most column of the index has changed at least once during the
previous 1000 steps, then the analysis stops immediately. But if the
left-most column has always been the same, then ANALYZE skips ahead to
the first entry with a different left-most column and reads an additional
1000 rows before terminating.
</p><p>The details of the effects of the analysis limit described in the previous
paragraph are subject to change in future versions of SQLite. But the
core idea will remain the same. An analysis limit of N will strive to
limit the number of rows visited in each index to approximately N.
</p><p>Values of N between 100 and 1000 are recommended.
Or, to disable the analysis limit, causing ANALYZE to do a
complete scan of each index, set the analysis limit to 0. The default
value for the analysis limit is 0 for backwards compatibility.
</p><p>The values placed in the sqlite_stat1 table by an approximate ANALYZE
are not exactly the same as what would be computed by an unrestricted
analysis. But they are usually close enough. The index statistics in
the sqlite_stat1 table are approximations in any case, so the fact that
the results of an approximate ANALYZE are slightly different from
a traditional full scan ANALYZE has little practical impact. It is
possible to construct a pathological case where an approximate ANALYZE
is noticeably inferior to a full-scan ANALYZE, but such cases are rare in
real-world problems.
</p><p>A good rule of thumb seems to be to always set "PRAGMA analysis_limit=N"
for N between 100 and 1000 prior to running either "ANALYZE". It used
to be that this was also recommended prior to running
"<a href="pragma.html#pragma_optimize">PRAGMA optimize</a>", but since version 3.46.0 (2024-05-23) that
happens automatically. The results are not quite as precise when using
PRAGMA analysis_limit, but they are precise enough, and the fact that
the results are computed so much faster means that developers are more
likely to compute them. An approximate ANALYZE is better than not
running ANALYZE at all.
</p><h2 id="limitations_of_approximate_analyze"><span>5.1. </span>Limitations of approximate ANALYZE</h2>
<p>The content in the sqlite_stat4 table cannot be computed with
anything less than a full scan. Hence, if a non-zero analysis limit
is specified, the sqlite_stat4 table is not computed.
</p><p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/lang_analyze.in?m=4607c3507e">2024-05-05 15:23:53</a> UTC </small></i></p>
|
