Adding upstream version 3.40.1.upstream/3.40.1 upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-05 17:28:19 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-05 17:28:19 +0000
commit: 18657a960e125336f704ea058e25c27bd3900dcb (patch)
tree: 17b438b680ed45a996d7b59951e6aa34023783f2 /www/eqp.html
parent: Initial commit. (diff)
download: sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.tar.xz
sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.zip
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">&#x25ba;</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 = "&#x25bc;";
+} else {
+sub.style.display = "none";
+mk.innerHTML = "&#x25ba;";
+}
+}
+</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&gt; .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&gt; 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&gt; CREATE INDEX i1 ON t1(a);
+sqlite&gt; 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&gt; CREATE INDEX i2 ON t1(a, b);
+sqlite&gt; 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&gt; 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&gt; 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&gt; CREATE INDEX i3 ON t1(b);
+sqlite&gt; 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&gt; 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&gt; CREATE INDEX i4 ON t2(c);
+sqlite&gt; 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&gt; 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&gt; EXPLAIN QUERY PLAN SELECT count(*)
+      &gt; FROM (SELECT max(b) AS x FROM t1 GROUP BY a) AS qqq
+      &gt; 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&gt; 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&gt; SELECT * FROM
+      &gt;   (SELECT * FROM t1 WHERE a=1 ORDER BY b LIMIT 2) AS x,
+      &gt;   (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&gt; 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&gt; 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>
+
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-05 17:28:19 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-05 17:28:19 +0000
commit	18657a960e125336f704ea058e25c27bd3900dcb (patch)
tree	17b438b680ed45a996d7b59951e6aa34023783f2 /www/eqp.html
parent	Initial commit. (diff)
download	sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.tar.xz sqlite3-18657a960e125336f704ea058e25c27bd3900dcb.zip