Debian Codegraph @

www/profile.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>Profiling SQL Queries</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 Profiling SQL Queries
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="#simple_cases_rows_loops_and_cycles">2. Simple Cases - Rows, Loops and Cycles</a></div>
127 <div class="fancy-toc1"><a href="#complex_cases_rows_loops_and_cycles">3. Complex Cases - Rows, Loops and Cycles</a></div>
128 <div class="fancy-toc1"><a href="#planner_estimates">4. Planner Estimates</a></div>
129 <div class="fancy-toc1"><a href="#low_level_profiling_data">5. Low-Level Profiling Data</a></div>
130 </div>
131 </details>
132 </div>
133
134
135
136
137 <h1 id="_overview_"><span>1. </span> Overview </h1>
138
139 <p>SQLite contains built-in support for profiling SQL queries, but it is not
140 enabled by default. In order to enable support for query profiling, SQLite must
141 be compiled with the <a href="compile.html#enable_stmt_scanstatus">following option</a>:
142
143 </p><div class="codeblock"><pre>-DSQLITE_ENABLE_STMT_SCANSTATUS
144 </pre></div>
145
146 <p>Building SQLite with this option enables the <a href="c3ref/stmt_scanstatus.html">sqlite3_stmt_scanstatus_v2()</a>
147 API, which provides access to the various profiling metrics. The remainder of
148 this page discusses the profiling reports generated by the SQLite
149 <a href="cli.html">command-line shell</a> using these metrics, rather than the API directly.
150
151 </p><p>The profiling reports generated by the shell are very similar to the query
152 plan reports generated by the <a href="eqp.html">EXPLAIN QUERY PLAN</a> command. This page assumes
153 that the reader is familiar with this format.
154
155 </p><p>Within a command-line shell compiled with the option above, query profiling
156 is enabled using the ".scanstats on" command:
157
158 </p><div class="codeblock"><pre>sqlite> .scanstats on
159 </pre></div>
160
161 <p>Once enabled, the shell automatically outputs a query profile after each
162 SQL query executed. Query profiling can be disabled using ".scanstats off".
163 For example:
164
165 </p><div class="codeblock"><pre>sqlite> .scanstats on
166 sqlite> SELECT a FROM t1, t2 WHERE a IN (1,2,3) AND a=d+e;
167 Cycles Loops Rows
168 ---------- ----- -----
169 QUERY PLAN 255M 100% 1 3
170 |--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) 60.0M 23% 1 3
171 `--SCAN t2 134M 52% 3 150K
172 </pre></div>
173
174 <h1 id="simple_cases_rows_loops_and_cycles"><span>2. </span>Simple Cases - Rows, Loops and Cycles</h1>
175
176 <p>Consider a database with the following schema:
177
178 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE ft USING fts5(text);
179 CREATE TABLE t1(a, b);
180 CREATE TABLE t2(c INTEGER PRIMARY KEY, d);
181 </pre></div>
182
183 <p>Then, after first executing ".scanstats on":
184
185 </p><div class="codeblock"><pre>sqlite3> SELECT * FROM t1, t2 WHERE t2.c=t1.a;
186 &lt;...query results...&gt;
187 Cycles Loops Rows
188 ---------- ----- -----
189 QUERY PLAN 1.14M 100%
190 |--SCAN t1 455K 40% 1 500
191 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) 620K 54% 500 250
192 </pre></div>
193
194 <p>The text in the example above following the snipped
195 "&lt;...query results...&gt;" is the profile report for the join query just
196 executed. The parts of the profile report that are similar to the
197 <a href="eqp.html">EXPLAIN QUERY PLAN</a> output indicate that the query is implemented by doing a
198 full table-scan of table "t1", with a lookup by INTEGER PRIMARY KEY on table
199 "t2" for each row visited.
200
201 </p><p>The value of "1" in the "Loops" column to the right of the EXPLAIN QUERY
202 PLAN output on the "SCAN t1" line indicates that this loop - the
203 full-table scan of table "t1" - ran exactly once. The value of "500" under
204 the "Rows column indicates that that single scan generates 500 rows.
205
206 </p><p>The "SEARCH t2 USING ..." line contains "500" for "Loops" to indicate
207 that this "loop" (really a lookup by INTEGER PRIMARY KEY) ran 500 times.
208 Which makes sense - it ran once for each row visited by the full-table scan
209 of "t1". The "250" under "Row" means that, altogether, those 500 loops
210 generated 250 rows.
211 In other words, only half of the INTEGER PRIMARY KEY lookups on table t2 were
212 successful, for the other half of the lookups there was no row to find.
213
214 </p><p>The loop-count for a SEARCH or SCAN entry is not necessarily the same as the
215 number of rows output by the outer loop. For example, if the query above
216 were modified as follows:
217
218 </p><div class="codeblock"><pre>sqlite3> SELECT * FROM t1, t2 WHERE t1.b&lt;=100 AND t2.c=t1.a;
219 &lt;...query results...&gt;
220 Cycles Loops Rows
221 ---------- ----- -----
222 QUERY PLAN 561K 100%
223 |--SCAN t1 345K 62% 1 500
224 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) 129K 23% 100 50
225 </pre></div>
226
227 <p>This time, even though the "SCAN t1" loop still visits 500 rows, the
228 "SEARCH t2" lookup is only performed 100 times. This is because SQLite
229 was able to discard rows from t1 that did not match the "t1.b&lt;=100"
230 constraint.
231
232 </p><p>The "cycles" measurements are based on the
233 <a href="https://en.wikipedia.org/wiki/Time_Stamp_Counter">CPU time-stamp counter
234 </a>, and so are a good proxy for wall-clock time. For the query above, the
235 total number of cycles was about 561,000. (All of the values displayed are
236 rounded to three significant digits.) For each of the two loops ("SCAN t1..." and
237 "SEARCH t2..."), the cycles count represents the time spent in operations that
238 can be directly attributed to that loop only. Specifically, this is the time
239 spent navigating and extracting data from the database b-trees for that loop.
240 These values never quite add up to the total cycles for the query, as there are
241 other internal operations performed by SQLite that are not directly
242 attributable to either loop.
243
244 </p><p>The cycles count for the "SCAN t1" loop was 345K - 62% of the total for the
245 query. The 100 lookups peformed by the "SEARCH t1" loop took 129K cycles, 23%
246 of the total.
247
248 </p><p>When a virtual table is used, the "Rows" and "Loops" metrics have the same
249 meaning as for loops on regular SQLite tables. The "Cycles" meaurement is the
250 total cycles consumed within virtual table methods associated with the loop.
251 For example:
252
253 </p><div class="codeblock"><pre>sqlite3> SELECT * FROM ft('sqlite'), t2 WHERE t2.c=ft.rowid;
254 &lt;...query results...&gt;
255 Cycles Loops Rows
256 ---------- ----- -----
257 QUERY PLAN 836K 100%
258 |--SCAN ft VIRTUAL TABLE INDEX 0:M1 740K 91% 1 48
259 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) 62.9K 8% 48 25
260 </pre></div>
261
262 <p>In this case the single query (Loops: 1) on fts5 table "ft" returned 48 rows
263 (Rows: 48) and consumed about 740K cycles, which was roughly 88% of
264 the total query time.
265
266 </p><h1 id="complex_cases_rows_loops_and_cycles"><span>3. </span>Complex Cases - Rows, Loops and Cycles</h1>
267
268 <p>Using the same schema as in the previous section, consider this more
269 complicated example:
270
271 </p><div class="codeblock"><pre>sqlite3&gt; WITH cnt(i) AS (
272 SELECT 1 UNION SELECT i+1 FROM cnt WHERE i&lt;100
273 )
274 SELECT
275 *, (SELECT d FROM t2 WHERE c=ft.rowid)
276 FROM
277 (SELECT count(*), a FROM t1 GROUP BY a) AS v1 CROSS JOIN
278 ft('sqlite'),
279 cnt
280 WHERE cnt.i=ft.rowid AND v1.a=ft.rowid;
281 &lt;...query results...&gt;
282 Cycles Loops Rows
283 ---------- ----- -----
284 QUERY PLAN 178M 100%
285 |--CO-ROUTINE v1
286 | |--SCAN t1 397K 0% 1 500
287 | `--USE TEMP B-TREE FOR GROUP BY
288 |--MATERIALIZE cnt 1.28M 1%
289 | |--SETUP
290 | | `--SCAN CONSTANT ROW
291 | `--RECURSIVE STEP
292 | `--SCAN cnt 129K 0% 100 100
293 |--SCAN v1 4.50M 3% 1 500
294 |--SCAN ft VIRTUAL TABLE INDEX 0:M1= 162M 91% 500 271
295 |--SCAN cnt 7.33M 4% 95 9500
296 `--CORRELATED SCALAR SUBQUERY 3 169K 0% 37
297 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) 94.7K 0% 37 21
298 </pre></div>
299
300 <p>The most complicated part of the example above is understanding the query
301 plan - the portion of the report that would also be generated by an
302 <a href="eqp.html">EXPLAIN QUERY PLAN</a> command.
303
304 </p><p>The following query uses an <a href="optoverview.html#autoindex">automatic index</a> and
305 an external sort:
306
307 </p><div class="codeblock"><pre>sqlite> SELECT * FROM
308 t2,
309 (SELECT count(*) AS cnt, d FROM t2 GROUP BY d) AS v2
310 WHERE v2.d=t2.d AND t2.d>100
311 ORDER BY v2.cnt;
312 &lt;...query results...&gt;
313 Cycles Loops Rows
314 ---------- ----- -----
315 QUERY PLAN 6.23M 100%
316 |--MATERIALIZE v2 2.35M 38%
317 | |--SCAN t2 188K 3% 1 250
318 | `--USE TEMP B-TREE FOR GROUP BY
319 |--SCAN t2 456K 7% 1 250
320 |--CREATE AUTOMATIC INDEX ON v2(d, cnt) 1.67M 27% 1 250
321 |--SEARCH v2 USING AUTOMATIC COVERING INDEX (d=?) 933K 15% 200 200
322 `--USE TEMP B-TREE FOR ORDER BY 662K 11% 1 200
323 </pre></div>
324
325 <p>Points of interest are:
326
327 </p><ul>
328 <li><p> This query materializes the sub-query into a temp table, then creates
329 an automatic (i.e. transient) index on it, then uses that index to optimize
330 the join. All three of these steps - "MATERIALIZE v2", "CREATE AUTOMATIC
331 INDEX" and "SEARCH ... USING AUTOMATIC INDEX" have separate cycle counts.
332 The "rows" associated with the "CREATE AUTOMATIC INDEX" line represents the
333 total number of rows included in the index. The "loops" and "rows" associated
334 with the "SEARCH ... USING AUTOMATIC INDEX" line represent the number of
335 lookups the index was used for and the total number of rows found by those
336 lookups.
337
338 </p></li><li><p> The external sort "USE TEMP B-TREE FOR ORDER BY" is also accounted
339 for separately. The cycles count represents the extra cycles consumed by
340 sorting the returned rows, above those that would have been used if the rows
341 were returned in arbitrary order. The rows count represents the number of
342 rows sorted.
343 </p></li></ul>
344
345 <h1 id="planner_estimates"><span>4. </span>Planner Estimates</h1>
346
347 <p>As well as ".scanstats on" to enable profiling and ".scanstats off" to
348 disable it, the shell also accepts ".scanstats est":
349
350 </p><div class="codeblock"><pre>sqlite> .scanstats est
351 </pre></div>
352
353 <p>This enables a special kind of profiling report that includes two extra
354 values associated with each "SCAN..." and "SEARCH..." element of a query
355 profile. The "Loops" and "Rows" columns show the actual number of invocations
356 of each step and the actual number of rows generated, but now both numbers
357 are also followed by the estimated values for those quantities that the query
358 planner used to pick the algorithm.
359
360 </p><div class="codeblock"><pre>sqlite> SELECT a FROM t1, t2 WHERE a IN (1,2,3) AND a=d+e ORDER BY a;
361 &lt;query results...&gt;
362 Cycles Loops (est) Rows (est)
363 ---------- ------------ ------------
364 QUERY PLAN 264M 100%
365 |--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) 60.5M 23% 1 1 3 3
366 `--SCAN t2 139M 53% 3 1.24K 450K 3.12M
367 </pre></div>
368
369 <p>
370 The output below is from query 15c of the
371 <a href="https://github.com/datalevin/datalevin/tree/master/benchmarks/JOB-bench">Join
372 Order Benchmark</a>.
373
374 </p><div class="codeblock"><pre>.scanstatus est
375 SELECT MIN(chn.name) AS character,
376 MIN(t.title) AS movie_with_american_producer
377 FROM char_name AS chn,
378 cast_info AS ci,
379 company_name AS cn,
380 company_type AS ct,
381 movie_companies AS mc,
382 role_type AS rt,
383 title AS t
384 WHERE ci.note LIKE '%(producer)%'
385 AND cn.country_code = '&#91;us&#93;'
386 AND t.production_year > 1990
387 AND t.id = mc.movie_id
388 AND t.id = ci.movie_id
389 AND ci.movie_id = mc.movie_id
390 AND chn.id = ci.person_role_id
391 AND rt.id = ci.role_id
392 AND cn.id = mc.company_id
393 AND ct.id = mc.company_type_id;
394 &lt;<i>... query results omitted ...</i>&gt;
395 Cycles Loops (est) Rows (est)
396 ---------- ------------ ------------
397 QUERY PLAN 87.1G 100%
398 |--SEARCH cn USING COVERING INDEX extra_cncc (country_code=?) 13.3M 0% 1 1 84.8K 81.9K
399 |--SEARCH mc USING INDEX company_id_movie_companies (company_id=?) 3.11G 4% 84.8K 81.9K 1.15M 983K
400 |--SEARCH t USING INTEGER PRIMARY KEY (rowid=?) 3.10G 4% 1.15M 983K 1.15M 860K
401 |--SEARCH ci USING INDEX movie_id_cast_info (movie_id=?) 77.3G 89% 609K 860K 21.0M 3.01M
402 |--SEARCH rt USING INTEGER PRIMARY KEY (rowid=?) 40.4M 0% 802K 3.01M 802K 3.01M
403 |--SEARCH chn USING INTEGER PRIMARY KEY (rowid=?) 20.7M 0% 802K 3.01M 26 3.01M
404 `--SEARCH ct USING INTEGER PRIMARY KEY (rowid=?) 10.6K 0% 26 3.01M 26 3.01M
405 </pre></div>
406
407 <p>Notice that most of the Loops and Rows values predicted by the
408 query planner are reasonably accurate, except for the case of the
409 join against the "char_name" (alias "chn") table. The join against
410 the "chn" table dramatically reduced the number of rows in the
411 processing chain - a reduction that the query planner failed to predict.
412 If the query planner had known that the join against "chn" would have
413 this effect, it could have picked a different join order that processed
414 the "chn" table sooner, and thus reduced downstream processing.
415 The following output is from a variation of the 15c benchmark
416 that uses <a href="optoverview.html#crossjoin">CROSS JOIN</a> to force the join against "chn" to happen
417 much earlier:
418
419 </p><div class="codeblock"><pre> Cycles Loops (est) Rows (est)
420 ---------- ------------ ------------
421 QUERY PLAN 6.95G 100%
422 |--SCAN ci 3.67G 53% 1 1 36.2M 7.86M
423 |--SEARCH chn USING INTEGER PRIMARY KEY (rowid=?) 36.9M 1% 1.43M 7.86M 25 7.86M
424 |--SEARCH t USING INTEGER PRIMARY KEY (rowid=?) 221K 0% 25 7.86M 25 6.88M
425 |--SEARCH mc USING INDEX movie_id_movie_companies (movie_id=?) 455K 0% 22 6.88M 43 18.9M
426 |--SEARCH cn USING INTEGER PRIMARY KEY (rowid=?) 209K 0% 43 18.9M 43 17.7M
427 |--SEARCH ct USING INTEGER PRIMARY KEY (rowid=?) 6491 0% 26 17.7M 26 17.7M
428 `--SEARCH rt USING INTEGER PRIMARY KEY (rowid=?) 2853 0% 26 17.7M 26 17.7M
429 </pre></div>
430
431 <p>This alternative query plan runs about 12.5 times faster. But based
432 on the row count estimates, the query planner reasoned that this plan should
433 have run about 5.9 times slower, which is what it didn't pick it.
434
435 </p><p>An important use case for the ".scanstatus est" feature is to help the developers
436 quickly see when the query planner is making bad choices based on row-count
437 estimates that differ wildly from the actual query. The question of how
438 the query planner could have known that the join against "chn" would so
439 dramatically reduce the row count is an area of on-going research as of
440 this writing (2026-02-04).
441
442 </p><h1 id="low_level_profiling_data"><span>5. </span>Low-Level Profiling Data</h1>
443
444
445
446 <p> Also supported is the ".scanstats vm" command. This enables a lower-level
447 profiling report showing the number of times each VM instruction was executed
448 and the percentage of clock-cycles that passed while it was executing:
449
450 </p><div class="codeblock"><pre>sqlite> .scanstats vm
451 </pre></div>
452
453 <p>Then:
454
455 </p><div class="codeblock"><pre>sqlite> SELECT count(*) FROM t2 WHERE (d % 5) = 0;
456 &lt;query results...&gt;
457 addr cycles nexec opcode p1 p2 p3 p4 p5 comment
458 ---- ------ ------ ------------- ---- ---- ---- ------------- -- -------------
459 0 0.0% 1 Init 1 18 0 0 Start at 18
460 1 0.0% 1 Null 0 1 1 0 r&#91;1..1]=NULL
461 2 0.0% 1 OpenRead 0 2 0 2 0 root=2 iDb=0; t2
462 3 0.0% 1 ColumnsUsed 0 0 0 2 0
463 4 0.0% 1 Explain 4 0 0 SCAN t2 0
464 5 0.0% 1 CursorHint 0 0 0 EQ(REM(c1,5),0) 0
465 6 0.0% 1 Rewind 0 14 0 0
466 7 46.86% 150000 Column 0 1 3 0 r&#91;3]= cursor 0 column 1
467 8 18.94% 150000 Remainder 4 3 2 0 r&#91;2]=r&#91;3]%r&#91;4]
468 9 5.41% 150000 ReleaseReg 3 1 0 0 release r&#91;3] mask 0
469 10 12.09% 150000 Ne 5 13 2 80 if r&#91;2]!=r&#91;5] goto 13
470 11 1.02% 30000 ReleaseReg 2 1 0 0 release r&#91;2] mask 0
471 12 2.95% 30000 AggStep1 0 0 1 count(0) 0 accum=r&#91;1] step(r&#91;0])
472 13 12.72% 150000 Next 0 7 0 1
473 14 0.0% 1 AggFinal 1 0 0 count(0) 0 accum=r&#91;1] N=0
474 15 0.0% 1 Copy 1 6 0 0 r&#91;6]=r&#91;1]
475 16 0.0% 1 ResultRow 6 1 0 0 output=r&#91;6]
476 17 0.01% 1 Halt 0 0 0 0
477 18 0.0% 1 Transaction 0 0 1 0 1 usesStmtJournal=0
478 19 0.0% 1 Integer 5 4 0 0 r&#91;4]=5
479 20 0.0% 1 Integer 0 5 0 0 r&#91;5]=0
480 21 0.0% 1 Goto 0 1 0 0
481 </pre></div>
482 <p align="center"><small><i>This page was last updated on 2026-02-04 23:39:31Z </small></i></p>
483