Debian Codegraph @

www/lang_analyze.html

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