diff options
Diffstat (limited to 'www/eqp.html')
| -rw-r--r-- | www/eqp.html | 458 |
1 files changed, 458 insertions, 0 deletions
diff --git a/www/eqp.html b/www/eqp.html new file mode 100644 index 0000000..673947b --- /dev/null +++ b/www/eqp.html @@ -0,0 +1,458 @@ +<!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>EXPLAIN QUERY PLAN</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"> +EXPLAIN QUERY PLAN +</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="#the_explain_query_plan_command">1. The EXPLAIN QUERY PLAN Command</a></div> +<div class="fancy-toc2"><a href="#table_and_index_scans">1.1. Table and Index Scans</a></div> +<div class="fancy-toc2"><a href="#temporary_sorting_b_trees">1.2. Temporary Sorting B-Trees</a></div> +<div class="fancy-toc2"><a href="#subqueries">1.3. Subqueries</a></div> +<div class="fancy-toc2"><a href="#compound_queries">1.4. Compound Queries</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="the_explain_query_plan_command"><span>1. </span>The EXPLAIN QUERY PLAN Command</h1> + +<p style="margin-left:10ex;margin-right:10ex"> +<b>Warning:</b> The data returned by the EXPLAIN QUERY PLAN command is +intended for interactive debugging only. The output format may change +between SQLite releases. Applications should not depend on the output +format of the EXPLAIN QUERY PLAN command. + +</p><p style="margin-left:10ex;margin-right:10ex"> +<b>Alert:</b> As warned above, the EXPLAIN QUERY PLAN output format did +change substantially with the version 3.24.0 release (2018-06-04). +Additional minor changes occurred in version 3.36.0 (2021-06-18). +Further changes are possible in subsequent releases. + + +</p><p>The <a href="lang_explain.html">EXPLAIN QUERY PLAN</a> SQL command is used to obtain a high-level +description of the strategy or plan that SQLite uses to implement a specific +SQL query. Most significantly, EXPLAIN QUERY PLAN reports on the way in +which the query uses database indices. This document is a guide to +understanding and interpreting the EXPLAIN QUERY PLAN output. Background +information is available separately: + +</p><ul> +<li> A primer on <a href="howitworks.html">How SQLite Works</a>. +</li><li> Notes on the <a href="optoverview.html">query optimizer</a>. +</li><li> How <a href="queryplanner.html">indexing</a> works. +</li><li> The <a href="queryplanner-ng.html">next generation query planner</a>. +</li></ul> + +<p>A query plan is represented as a tree. +In raw form, as returned by <a href="c3ref/step.html">sqlite3_step()</a>, each node of the tree +consists of four fields: An integer node id, an integer parent id, +an auxiliary integer field that is not currently used, and a description +of the node. +The entire tree is therefore a table with four columns and zero or more +rows. +The <a href="cli.html">command-line shell</a> will usually intercept this table and renders +it as an ASCII-art graph for more convenient viewing. To disable the +shells automatic graph rendering and to display EXPLAIN QUERY PLAN +output in its tabular format, run the command ".explain off" to set +the "EXPLAIN formatting mode" to off. To restore automatic graph rendering, +run ".explain auto". You can see the current "EXPLAIN formatting mode" +setting using the ".show" command. + +</p><p>One can also set the <a href="cli.html">CLI</a> into automatic EXPLAIN QUERY PLAN mode +using the ".eqp on" command: + +</p><div class="codeblock"><pre>sqlite> .eqp on +</pre></div> + +<p> In automatic EXPLAIN QUERY PLAN mode, the shell automatically runs +a separate EXPLAIN QUERY PLAN query for each statement you enter and +displays the result before actually running the query. Use the +".eqp off" command to turn automatic EXPLAIN QUERY PLAN mode back off. + +</p><p>EXPLAIN QUERY PLAN is most useful on a SELECT statement, +but may also appear with other statements that read data from database +tables (e.g. UPDATE, DELETE, INSERT INTO ... SELECT). + +</p><h2 id="table_and_index_scans"><span>1.1. </span>Table and Index Scans</h2> + +<p> + When processing a SELECT (or other) statement, SQLite may retrieve data from + database tables in a variety of ways. It may scan through all the records in + a table (a full-table scan), scan a contiguous subset of the records in a + table based on the rowid index, scan a contiguous subset of the entries in a + database <a href="lang_createtable.html">index</a>, or use a combination of the above strategies + in a single scan. The various ways in which SQLite may retrieve data from a + table or index are described in detail <a href="queryplanner.html#searching">here</a>. + +</p><p> + For each table read by the query, the output of EXPLAIN QUERY + PLAN includes a record for which the value in the "detail" column begins + with either "SCAN" or "SEARCH". "SCAN" is used for a full-table scan, + including cases where SQLite iterates through all records in a table + in an order defined by an index. "SEARCH" indicates that only a subset of + the table rows are visited. Each SCAN or SEARCH record includes the + following information: + +</p><ul> + <li> The name of the table, view, or subquery that data is read from. + </li><li> Whether or not an index or <a href="optoverview.html#autoindex">automatic index</a> is used. + </li><li> Whether or not the <a href="queryplanner.html#covidx">covering index</a> optimization applies. + </li><li> Which terms of the WHERE clause are used for indexing. +</li></ul> + +<p> + For example, the following EXPLAIN QUERY PLAN command operates on a SELECT + statement that is implemented by performing a full-table scan on table t1: +</p><div class="codeblock"><pre>sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; +QUERY PLAN +`--SCAN t1 +</pre></div> + +<p> + The example above shows + SQLite picking full-table scan will visit all rows in the table. + If the query were able to use an index, then the + SCAN/SEARCH record would include the name of the index and, for a + SEARCH record, an indication of how the subset of rows visited is + identified. For example: +</p><div class="codeblock"><pre>sqlite> CREATE INDEX i1 ON t1(a); +sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; +QUERY PLAN +`--SEARCH t1 USING INDEX i1 (a=?) +</pre></div> + +<p> + The previous example, SQLite uses index "i1" to optimize + a WHERE clause term of the form (a=?) - in this case "a=1". + The previous example could not use a <a href="queryplanner.html#covidx">covering index</a>, but the following + example can, and that fact is reflected in the output: +</p><div class="codeblock"><pre>sqlite> CREATE INDEX i2 ON t1(a, b); +sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; +QUERY PLAN +`--SEARCH t1 USING COVERING INDEX i2 (a=?) +</pre></div> + +<p> + All joins in SQLite are <a href="optoverview.html#table_order">implemented using nested scans</a>. When a + SELECT query that features a join is analyzed using EXPLAIN QUERY PLAN, one + SCAN or SEARCH record is output for each nested loop. For example: +</p><div class="codeblock"><pre>sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; +QUERY PLAN +|--SEARCH t1 USING INDEX i2 (a=? AND b>?) +`--SCAN t2 +</pre></div> + +<p> + The order of the entries indicates the nesting order. In + this case, the scan of table t1 using index i2 is the outer loop (since it + appears first) + and the full-table scan of table t2 is the inner loop (since it appears + last). + In the following example, the positions of t1 and t2 in the FROM + clause of the SELECT are reversed. The query strategy remains the same. + The output from EXPLAIN QUERY PLAN shows how the query is actually + evaluated, not how it is specified in the SQL statement. +</p><div class="codeblock"><pre>sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; +QUERY PLAN +|--SEARCH t1 USING INDEX i2 (a=? AND b>?) +`--SCAN t2 +</pre></div> + +<a name="or-opt"></a> + +<p> + If the WHERE clause of a query contains an OR expression, then SQLite might + use the <a href="queryplanner.html#or_in_where">"OR by union"</a> strategy (also known as the + <a href="optoverview.html#or_opt">OR optimization</a>). In this case there will be single top-level record + for the search, with two sub-records, one for each index: +</p><div class="codeblock"><pre>sqlite> CREATE INDEX i3 ON t1(b); +sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2; +QUERY PLAN +`--MULTI-INDEX OR + |--SEARCH t1 USING COVERING INDEX i2 (a=?) + `--SEARCH t1 USING INDEX i3 (b=?) +</pre></div> + +<h2 id="temporary_sorting_b_trees"><span>1.2. </span>Temporary Sorting B-Trees</h2> + +<p> + If a SELECT query contains an ORDER BY, GROUP BY or DISTINCT clause, + SQLite may need to use a temporary b-tree structure to sort the output + rows. Or, it might <a href="queryplanner.html#sorting">use an index</a>. Using an index is + almost always much more efficient than performing a sort. + If a temporary b-tree is required, a record is added to the EXPLAIN + QUERY PLAN output with the "detail" field set to a string value of + the form "USE TEMP B-TREE FOR xxx", where xxx is one of "ORDER BY", + "GROUP BY" or "DISTINCT". For example: + +</p><div class="codeblock"><pre>sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; +QUERY PLAN +|--SCAN t2 +`--USE TEMP B-TREE FOR ORDER BY +</pre></div> + +<p> + In this case using the temporary b-tree can be avoided by creating an index + on t2(c), as follows: + +</p><div class="codeblock"><pre>sqlite> CREATE INDEX i4 ON t2(c); +sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; +QUERY PLAN +`--SCAN t2 USING INDEX i4 +</pre></div> + +<h2 id="subqueries"><span>1.3. </span>Subqueries</h2> + +<p> + In all the examples above, there has only been a single SELECT statement. + If a query contains sub-selects, those are shown as being children of + the outer SELECT. For example: + +</p><div class="codeblock"><pre>sqlite> EXPLAIN QUERY PLAN SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2; +|--SCAN TABLE t2 USING COVERING INDEX i4 +|--SCALAR SUBQUERY +| `--SEARCH t1 USING COVERING INDEX i2 (a=?) +`--CORRELATED SCALAR SUBQUERY + `--SEARCH t1 USING INDEX i3 (b=?) +</pre></div> + +<p> + The example above contains two "SCALAR" subqueries. The subqueries + are SCALAR in the sense that they return a single value - a one-row, + one-column table. If the actual query returns more than that, then + only the first column of the first row is used. +</p><p> + The first subquery above is constant with respect to the outer query. + The value for the first subquery can be computed once and then reused + for each row of the outer SELECT. The second subquery, however, is + "CORRELATED". The value of the second subquery changes depending + on values in the current row of the outer query. Hence, the second + subquery must be run once for each output row in the outer SELECT. + +</p><p> + Unless the <a href="optoverview.html#flattening">flattening optimization</a> is applied, if a subquery appears in + the FROM clause of a SELECT statement, SQLite can either run the subquery and + stores the results in a temporary table, or it can run the subquery as a + co-routine. The following query is an example of the latter. The subquery + is run by a co-routine. The outer query blocks whenever it needs another + row of input from the subquery. Control switches to the co-routine which + produces the desired output row, then control switches back to the main + routine which continues processing. + +</p><div class="codeblock"><pre>sqlite> EXPLAIN QUERY PLAN SELECT count(*) + > FROM (SELECT max(b) AS x FROM t1 GROUP BY a) AS qqq + > GROUP BY x; +QUERY PLAN +|--CO-ROUTINE qqq +| `--SCAN t1 USING COVERING INDEX i2 +|--SCAN qqqq +`--USE TEMP B-TREE FOR GROUP BY +</pre></div> + +<p> + + If the <a href="optoverview.html#flattening">flattening optimization</a> is used on a subquery in the FROM clause + of a SELECT statement, that effectively merges the subquery into the outer + query. The output of EXPLAIN QUERY PLAN reflects this, as in the following + example: + +</p><div class="codeblock"><pre>sqlite> EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1) AS t3, t1; +QUERY PLAN +|--SEARCH t2 USING INDEX i4 (c=?) +`--SCAN t1 +</pre></div> + +<p> + + If the content of a subquery might need to be visited more than once, then + the use of a co-routine is undesirable, as the co-routine would then have to + compute the data more than once. And if the subquery cannot be flattened, + that means the subquery must be manifested into a transient table. + +</p><div class="codeblock"><pre>sqlite> SELECT * FROM + > (SELECT * FROM t1 WHERE a=1 ORDER BY b LIMIT 2) AS x, + > (SELECT * FROM t2 WHERE c=1 ORDER BY d LIMIT 2) AS y; +QUERY PLAN +|--MATERIALIZE x +| `--SEARCH t1 USING COVERING INDEX i2 (a=?) +|--MATERIALIZE y +| |--SEARCH t2 USING INDEX i4 (c=?) +| `--USE TEMP B-TREE FOR ORDER BY +|--SCAN x +`--SCAN y +</pre></div> + +<h2 id="compound_queries"><span>1.4. </span>Compound Queries</h2> + +<p> + Each component query of a <a href="lang_select.html#compound">compound query</a> (UNION, UNION ALL, EXCEPT or + INTERSECT) is assigned computed separately and is given its own line in + the EXPLAIN QUERY PLAN output. + +</p><div class="codeblock"><pre>sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2; +QUERY PLAN +`--COMPOUND QUERY + |--LEFT-MOST SUBQUERY + | `--SCAN t1 USING COVERING INDEX i1 + `--UNION USING TEMP B-TREE + `--SCAN t2 USING COVERING INDEX i4 +</pre></div> + +<p> + The "USING TEMP B-TREE" clause in the above output indicates that a + temporary b-tree structure is used to implement the UNION of the results + of the two sub-selects. An alternative method of computing a compound + is to run each subquery as a co-routine, arrange for their outputs to + appear in sorted order, and merge the results together. When the query + planner chooses this latter approach, the EXPLAIN QUERY PLAN output + looks like this: + +</p><div class="codeblock"><pre>sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1; +QUERY PLAN +`--MERGE (EXCEPT) + |--LEFT + | `--SCAN t1 USING COVERING INDEX i1 + `--RIGHT + |--SCAN t2 + `--USE TEMP B-TREE FOR ORDER BY +</pre></div> +<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/eqp.in?m=719c38ad7e31ec3d8">2021-03-23 20:19:29</a> UTC </small></i></p> + |