SQLite

Check-in [32924446db]
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
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.015
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: 350f29ea1c 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: 32924446db 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: 5a12db75d1 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
2038
2039
2040
2041
2042
2043
2044























2045
2046
2047
2048
2049
2050
2051

    if( (p->prereq & pTemplate->prereq)==p->prereq    /* (1)  */
     && p->rSetup<=pTemplate->rSetup                  /* (2a) */
     && p->rRun<=pTemplate->rRun                      /* (2b) */
     && p->nOut<=pTemplate->nOut                      /* (2c) */
    ){
      return 0;  /* Discard pTemplate */
    }
























    /* If pTemplate is always better than p, then cause p to be overwritten
    ** with pTemplate.  pTemplate is better than p if:
    **   (1)  pTemplate has no more dependences than p, and
    **   (2)  pTemplate has an equal or lower cost than p.
    */
    if( (p->prereq & pTemplate->prereq)==pTemplate->prereq   /* (1)  */








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>








2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074

    if( (p->prereq & pTemplate->prereq)==p->prereq    /* (1)  */
     && p->rSetup<=pTemplate->rSetup                  /* (2a) */
     && p->rRun<=pTemplate->rRun                      /* (2b) */
     && p->nOut<=pTemplate->nOut                      /* (2c) */
    ){
      return 0;  /* Discard pTemplate */
    }

    /* If pTemplate:
    **
    **   (1) uses the same index as existing where-loop p, 
    **   (2) requires the same or a superset of tables to be scanned first, 
    **   (3) constraints the same or fewer columns with ==, and
    **   (4) skips more leading columns (skip-scan optimization).
    **
    ** the discard the template. This ensures that if stat4 data shows that:
    **
    **   WHERE (a=1 AND b=2)
    **
    ** is prohibitively expensive the planner does not instead do:
    **
    **   WHERE (ANY(a) AND b=2)
    */
    if( pTemplate->nSkip>p->nSkip                            /* (4) */
     && pTemplate->u.btree.pIndex==p->u.btree.pIndex         /* (1) */
     && pTemplate->u.btree.nEq<=p->u.btree.nEq               /* (3) */
     && (pTemplate->prereq & p->prereq)==p->prereq           /* (2) */
    ){
      return 0;  /* Discard pTemplate */
    }

    /* If pTemplate is always better than p, then cause p to be overwritten
    ** with pTemplate.  pTemplate is better than p if:
    **   (1)  pTemplate has no more dependences than p, and
    **   (2)  pTemplate has an equal or lower cost than p.
    */
    if( (p->prereq & pTemplate->prereq)==pTemplate->prereq   /* (1)  */
︙︙
Added test/analyzeG.test. 














































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

# 2018-07-04
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix analyzeG

ifcapable {!stat4} {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c, d);
  CREATE INDEX t1abc ON t1(a, b, c);
  WITH s(i) AS (
    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100000
  )
  INSERT INTO t1 SELECT 1,1,1,1 FROM s;

  WITH s(i) AS (
    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000
  )
  INSERT INTO t1 SELECT i%5,i,i,i FROM s;
}

do_execsql_test 1.1 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 AND b=1 AND c>0
} {
  3 0 0 {SEARCH TABLE t1 USING INDEX t1abc (a=? AND b=? AND c>?)}
}

do_execsql_test 1.3 { 
  ANALYZE 
} {}

do_execsql_test 1.4 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 AND b=1 AND c>0
} {
  2 0 0 {SCAN TABLE t1}
}



finish_test