/ Check-in [32924446]
Login

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

Overview
Comment:Experimental planner change to avoid a skip-scan if a regular index scan on the same index columns can be done instead.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-avoid-expensive-skipscan
Files: files | file ages | folders
SHA3-256: 32924446db0d07e5b2661a6626136a7bcdda629de23f98f3e1e862dd52d2f8a5
User & Date: dan 2018-07-04 14:28:07
Context
2018-07-10
15:55
Experimental planner change to avoid a skip-scan if a regular index scan on the same index columns can be done instead. Leaf check-in: 350f29ea user: dan tags: exp-begin-concurrent-pnu
2018-07-04
14:28
Experimental planner change to avoid a skip-scan if a regular index scan on the same index columns can be done instead. Leaf check-in: 32924446 user: dan tags: exp-avoid-expensive-skipscan
2018-07-03
20:17
Test that a race condition can cause a "BEGIN EXCLUSIVE" to return SQLITE_BUSY_SNAPSHOT in wal mode. check-in: 5a12db75 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  2038   2038       if( (p->prereq & pTemplate->prereq)==p->prereq    /* (1)  */
  2039   2039        && p->rSetup<=pTemplate->rSetup                  /* (2a) */
  2040   2040        && p->rRun<=pTemplate->rRun                      /* (2b) */
  2041   2041        && p->nOut<=pTemplate->nOut                      /* (2c) */
  2042   2042       ){
  2043   2043         return 0;  /* Discard pTemplate */
  2044   2044       }
         2045  +
         2046  +    /* If pTemplate:
         2047  +    **
         2048  +    **   (1) uses the same index as existing where-loop p, 
         2049  +    **   (2) requires the same or a superset of tables to be scanned first, 
         2050  +    **   (3) constraints the same or fewer columns with ==, and
         2051  +    **   (4) skips more leading columns (skip-scan optimization).
         2052  +    **
         2053  +    ** the discard the template. This ensures that if stat4 data shows that:
         2054  +    **
         2055  +    **   WHERE (a=1 AND b=2)
         2056  +    **
         2057  +    ** is prohibitively expensive the planner does not instead do:
         2058  +    **
         2059  +    **   WHERE (ANY(a) AND b=2)
         2060  +    */
         2061  +    if( pTemplate->nSkip>p->nSkip                            /* (4) */
         2062  +     && pTemplate->u.btree.pIndex==p->u.btree.pIndex         /* (1) */
         2063  +     && pTemplate->u.btree.nEq<=p->u.btree.nEq               /* (3) */
         2064  +     && (pTemplate->prereq & p->prereq)==p->prereq           /* (2) */
         2065  +    ){
         2066  +      return 0;  /* Discard pTemplate */
         2067  +    }
  2045   2068   
  2046   2069       /* If pTemplate is always better than p, then cause p to be overwritten
  2047   2070       ** with pTemplate.  pTemplate is better than p if:
  2048   2071       **   (1)  pTemplate has no more dependences than p, and
  2049   2072       **   (2)  pTemplate has an equal or lower cost than p.
  2050   2073       */
  2051   2074       if( (p->prereq & pTemplate->prereq)==pTemplate->prereq   /* (1)  */

Added test/analyzeG.test.

            1  +# 2018-07-04
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +set ::testprefix analyzeG
           16  +
           17  +ifcapable {!stat4} {
           18  +  finish_test
           19  +  return
           20  +}
           21  +
           22  +do_execsql_test 1.0 {
           23  +  CREATE TABLE t1(a, b, c, d);
           24  +  CREATE INDEX t1abc ON t1(a, b, c);
           25  +  WITH s(i) AS (
           26  +    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100000
           27  +  )
           28  +  INSERT INTO t1 SELECT 1,1,1,1 FROM s;
           29  +
           30  +  WITH s(i) AS (
           31  +    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000
           32  +  )
           33  +  INSERT INTO t1 SELECT i%5,i,i,i FROM s;
           34  +}
           35  +
           36  +do_execsql_test 1.1 {
           37  +  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 AND b=1 AND c>0
           38  +} {
           39  +  3 0 0 {SEARCH TABLE t1 USING INDEX t1abc (a=? AND b=? AND c>?)}
           40  +}
           41  +
           42  +do_execsql_test 1.3 { 
           43  +  ANALYZE 
           44  +} {}
           45  +
           46  +do_execsql_test 1.4 {
           47  +  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 AND b=1 AND c>0
           48  +} {
           49  +  2 0 0 {SCAN TABLE t1}
           50  +}
           51  +
           52  +
           53  +
           54  +finish_test