Documentation Source Text

Check-in [360fe45245]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add evidence marks to examples on lang_explain.html.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 360fe452452b77c8b79ab8229c3784fbf6b6f451
User & Date: dan 2010-11-12 17:40:53
Context
2010-11-13
17:10
Update EXPLAIN QUERY PLAN examples to match changes to sqlite. check-in: 3f8c0a0c1f user: dan tags: trunk
2010-11-12
17:40
Add evidence marks to examples on lang_explain.html. check-in: 360fe45245 user: dan tags: trunk
2010-11-11
17:47
Add extra documentation for EXPLAIN QUERY PLAN. check-in: 759dfb93a1 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to pages/lang.in.

  1407   1407     <li> The selectivity of the subset of records scanned.
  1408   1408     <li> The estimated number of rows that SQLite expects the scan to visit.
  1409   1409   </ul>
  1410   1410   
  1411   1411   <p>
  1412   1412     For example, the following EXPLAIN QUERY PLAN command operates on a SELECT
  1413   1413     statement that is implemented by performing a full-table scan on table t1:
  1414         -<pre>
         1414  +<pre>^(
  1415   1415       sqlite&gt; EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
  1416         -    0|0|0|SCAN TABLE t1 (~1000000 rows)
  1417         -</pre>
         1416  +    0|0|0|SCAN TABLE t1 (~100000 rows)
         1417  +)^</pre>
  1418   1418   
  1419   1419   <p>
  1420   1420     SQLite estimates that the full-table scan will visit approximately 
  1421   1421     1,000,000 records. If the query were able to use an index, then the SCAN
  1422   1422     record would include the name of the index and a description of how
  1423   1423     it is used by the query. For example:
  1424         -<pre>
         1424  +<pre>^(
  1425   1425       sqlite&gt; CREATE INDEX i1 ON t1(a);
  1426   1426       sqlite&gt; EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
  1427   1427       0|0|0|SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)
  1428         -</pre>
         1428  +)^</pre>
  1429   1429   
  1430   1430   <p>
  1431   1431     The output above shows that in this case, SQLite uses index "i1" to optimize
  1432   1432     a WHERE clause filter of the form (a=?) - in this case "a=1". SQLite 
  1433   1433     estimates that scanning the subset of index entries that match the "a=1"
  1434   1434     filter means scanning through approximately 10 records. In this case it is
  1435   1435     not possible to use index i1 as a [covering index], but if it were, the
  1436   1436     SCAN record would report that as well. For example:
  1437         -<pre>
         1437  +<pre>^(
  1438   1438       sqlite&gt; CREATE INDEX i2 ON t1(a, b);
  1439   1439       sqlite&gt; EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
  1440   1440       0|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)
  1441         -</pre>
         1441  +)^</pre>
  1442   1442   
  1443   1443   <p>
  1444   1444     All joins in SQLite are [join order|implemented using nested scans]. When a
  1445   1445     SELECT query that features a join is analyzed using EXPLAIN QUERY PLAN, one
  1446   1446     SCAN record is output for each nested scan. For example:
  1447         -<pre>
         1447  +<pre>^(
  1448   1448       sqlite&gt; EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
  1449   1449       0|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (a=? AND b>?) (~3 rows)
  1450   1450       0|1|1|SCAN TABLE t2 (~1000000 rows)
  1451         -</pre>
         1451  +)^</pre>
  1452   1452   
  1453   1453   <p>
  1454   1454     The second column of output (column "order") indicates the nesting order. In
  1455   1455     this case, the scan of table t1 using index i2 is the outer loop (order=0)
  1456   1456     and the full-table scan of table t2 (order=1) is the inner loop. The third
  1457   1457     column (column "from"), indicates the position in the FROM clause of the
  1458   1458     SELECT statement that the table associated with each scan occurs in. In the
  1459   1459     case above, table t1 occupies the first position in the FROM clause, so the
  1460   1460     value of column "from" is 0 in the first SCAN record. Table t2 is in the
  1461   1461     second position, so the "from" column for the corresponding SCAN record is
  1462   1462     set to 1. In the following example, the positions of t1 and t2 in the FROM 
  1463   1463     clause of the SELECT are reversed. The query strategy remains the same, but
  1464   1464     the values in the "from" column of the output are adjusted accordingly.
  1465         -<pre>
         1465  +<pre>^(
  1466   1466       sqlite&gt; EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
  1467   1467       0|0|1|SCAN TABLE t1 BY COVERING INDEX i2 (a=? AND b>?) (~3 rows)
  1468   1468       0|1|0|SCAN TABLE t2 (~1000000 rows)
  1469         -</pre>
         1469  +)^</pre>
  1470   1470   
  1471   1471   <p>
  1472   1472     In the example above, SQLite estimates that the outer loop scan will visit
  1473   1473     approximately 3 rows, and the inner loop scan approximately 1,000,000. If
  1474   1474     you observe that SQLite's estimates are wildly inaccurate (and appear to be
  1475   1475     causing it to generate sub-optimal query plans), your queries may benefit
  1476   1476     from running the [ANALYZE] command on the database.
................................................................................
  1477   1477   
  1478   1478   <p>
  1479   1479     If the WHERE clause of a query contains an OR expression, then SQLite might
  1480   1480     use the [or-connected-terms|"OR by union"] strategy (also described 
  1481   1481     [or optimization|here]). In this case there will be two SCAN records, one
  1482   1482     for each index scan, with the same values in both the "order" and "from" 
  1483   1483     columns. For example:
  1484         -<pre>
         1484  +<pre>^(
  1485   1485       sqlite&gt; CREATE INDEX i3 ON t1(b);
  1486   1486       sqlite&gt; EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
  1487         -    0|0|0|SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)
         1487  +    0|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)
  1488   1488       0|0|0|SCAN TABLE t1 BY INDEX i3 (b=?) (~10 rows)
  1489         -</pre>
         1489  +)^</pre>
  1490   1490   
  1491   1491   <h4>Temporary Sorting B-Trees</h4>
  1492   1492   
  1493   1493   <p>
  1494   1494     If a SELECT query contains an ORDER BY, GROUP BY or DISTINCT clause, 
  1495   1495     SQLite may need to use a temporary b-tree structure to perform an 
  1496   1496     <a href="http://en.wikipedia.org/wiki/Insertion_sort">insertion sort</a> 
................................................................................
  1497   1497     of the output rows. Or, it may [sorting|use an index]. Using an index is 
  1498   1498     almost always much more efficient than performing an online insertion sort.
  1499   1499     If a temporary b-tree is required, a record is added to the EXPLAIN
  1500   1500     QUERY PLAN output with the "detail" field set to a string value of
  1501   1501     the form "USE TEMP B-TREE FOR xxx", where xxx is one of "ORDER BY",
  1502   1502     "GROUP BY" or "DISTINCT". For example:
  1503   1503   
  1504         -<pre>
         1504  +<pre>^(
  1505   1505       sqlite&gt; EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 
  1506   1506       0|0|0|SCAN TABLE t2 (~1000000 rows)
  1507   1507       0|0|0|USE TEMP B-TREE FOR ORDER BY
  1508         -</pre>
         1508  +)^</pre>
  1509   1509   
  1510   1510   <p>
  1511   1511     In this case using the temporary b-tree can be avoided by creating an index
  1512   1512     on t2(c), as follows:
  1513   1513   
  1514         -<pre>
         1514  +<pre>^(
  1515   1515       sqlite&gt; CREATE INDEX i4 ON t2(c);
  1516   1516       sqlite&gt; EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 
  1517   1517       0|0|0|SCAN TABLE t2 BY INDEX i4 (~1000000 rows)
  1518         -</pre>
         1518  +)^</pre>
  1519   1519   
  1520   1520   <h4>Subqueries</h4>
  1521   1521   
  1522   1522   <p>
  1523   1523     In all the examples above, the first column (column "selectid") is always
  1524   1524     set to 0. If a query contains sub-selects, either as part of the FROM
  1525   1525     clause or as part of SQL expressions, then the output of EXPLAIN QUERY
  1526   1526     PLAN also includes a report for each sub-select. Each sub-select is assigned
  1527   1527     a distinct, non-zero "selectid" value. The top-level SELECT statement is
  1528   1528     always assigned the selectid value 0. For example:
  1529   1529   
  1530         -<pre>
         1530  +<pre>^(
  1531   1531       sqlite&gt; EXPLAIN QUERY PLAN SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
  1532   1532       0|0|0|SCAN TABLE t2 (~1000000 rows)
  1533   1533       0|0|0|EXECUTE SCALAR SUBQUERY 1
  1534   1534       1|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)
  1535   1535       0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
  1536   1536       2|0|0|SCAN TABLE t1 BY INDEX i3 (b=?) (~10 rows)
  1537         -</pre>
         1537  +)^</pre>
  1538   1538   
  1539   1539   <p>
  1540   1540     The example above contains a pair of scalar subqueries assigned selectid 
  1541   1541     values 1 and 2. As well as a SCAN record, there are also 2 "EXECUTE" 
  1542   1542     records associated with the top level subquery (selectid 0), indicating
  1543   1543     that subqueries 1 and 2 are executed by the top level query in a scalar
  1544   1544     context. The CORRELATED qualifier present in the EXECUTE record associated
................................................................................
  1554   1554     the FROM clause of a SELECT statement, SQLite executes the subquery and
  1555   1555     stores the results in a temporary table. It then uses the contents of the 
  1556   1556     temporary table in place of the subquery to execute the parent query. This
  1557   1557     is shown in the output of EXPLAIN QUERY PLAN by substituting a 
  1558   1558     "SCAN SUBQUERY" record for the "SCAN TABLE" record that normally appears
  1559   1559     for each element in the FROM clause. For example:
  1560   1560   
  1561         -<pre>
         1561  +<pre>^(
  1562   1562       sqlite&gt; EXPLAIN QUERY PLAN SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
  1563   1563       1|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (~1000000 rows)
  1564   1564       0|0|0|SCAN SUBQUERY 1 (~1000000 rows)
  1565   1565       0|0|0|USE TEMP B-TREE FOR GROUP BY
  1566         -</pre>
         1566  +)^</pre>
  1567   1567   
  1568   1568   <p>
  1569   1569     If the [flattening optimization] is used on a subquery in the FROM clause
  1570   1570     of a SELECT statement, then the output of EXPLAIN QUERY PLAN reflects this.
  1571   1571     For example, in the following there is no "SCAN SUBQUERY" record even though
  1572   1572     there is a subquery in the FROM clause of the top level SELECT. Instead, since
  1573   1573     the flattening optimization does apply in this case, the EXPLAIN QUERY PLAN
  1574   1574     report shows that the top level query is implemented using a nested loop join
  1575   1575     of tables t1 and t2.
  1576   1576   
  1577         -<pre>
         1577  +<pre>^(
  1578   1578       sqlite&gt; EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;
  1579   1579       0|0|0|SCAN TABLE t2 BY INDEX i4 (c=?) (~10 rows)
  1580   1580       0|1|1|SCAN TABLE t1 (~1000000 rows)
  1581         -</pre>
         1581  +)^</pre>
  1582   1582   
  1583   1583   <h4>Compound Queries</h4>
  1584   1584   
  1585   1585   <p>
  1586   1586     Each component query of a [compound query] (UNION, UNION ALL, EXCEPT or 
  1587   1587     INTERSECT) is assigned its own selectid and reported on separately. A
  1588   1588     single record is output for the parent (compound query) identifying the
  1589   1589     operation, and whether or not a temporary b-tree is used to implement
  1590   1590     it. For example:
  1591   1591   
  1592         -<pre>
         1592  +<pre>^(
  1593   1593       sqlite&gt; EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
  1594   1594       1|0|0|SCAN TABLE t1 (~1000000 rows)
  1595   1595       2|0|0|SCAN TABLE t2 (~1000000 rows)
  1596   1596       0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
  1597         -</pre>
         1597  +)^</pre>
  1598   1598   
  1599   1599   <p>
  1600   1600     The "USING TEMP B-TREE" clause in the above output indicates that a 
  1601   1601     temporary b-tree structure is used to implement the UNION of the results
  1602   1602     of the two sub-selects. If the temporary b-tree were not required, as
  1603   1603     in the following example, the clause is not present.
  1604   1604   
  1605         -<pre>
         1605  +<pre>^(
  1606   1606       sqlite&gt; EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
  1607   1607       1|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (~1000000 rows)
  1608   1608       2|0|0|SCAN TABLE t2 (~1000000 rows)
  1609   1609       2|0|0|USE TEMP B-TREE FOR ORDER BY
  1610   1610       0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
  1611         -</pre>
         1611  +)^</pre>
  1612   1612   
  1613   1613   
  1614   1614   <tcl>
  1615   1615   ##############################################################################
  1616   1616   Section expression expr {*expression {expression syntax}}
  1617   1617   
  1618   1618   BubbleDiagram expr 1