test/skipscan2.test

   1 # 2013-11-27
   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 # This file implements tests of the "skip-scan" query strategy.
  13 #
  14 # The test cases in this file are derived from the description of
  15 # the skip-scan query strategy in the "optoverview.html" document.
  16 #
  17
  18 set testdir [file dirname $argv0]
  19 source $testdir/tester.tcl
  20
  21 do_execsql_test skipscan2-1.1 {
  22   CREATE TABLE people(
  23     name TEXT PRIMARY KEY,
  24     role TEXT NOT NULL,
  25     height INT NOT NULL, -- in cm
  26     CHECK( role IN ('student','teacher') )
  27   );
  28   CREATE INDEX people_idx1 ON people(role, height);
  29 } {}
  30 do_execsql_test skipscan2-1.2 {
  31   INSERT INTO people VALUES('Alice','student',156);
  32   INSERT INTO people VALUES('Bob','student',161);
  33   INSERT INTO people VALUES('Cindy','student',155);
  34   INSERT INTO people VALUES('David','student',181);
  35   INSERT INTO people VALUES('Emily','teacher',158);
  36   INSERT INTO people VALUES('Fred','student',163);
  37   INSERT INTO people VALUES('Ginny','student',169);
  38   INSERT INTO people VALUES('Harold','student',172);
  39   INSERT INTO people VALUES('Imma','student',179);
  40   INSERT INTO people VALUES('Jack','student',181);
  41   INSERT INTO people VALUES('Karen','student',163);
  42   INSERT INTO people VALUES('Logan','student',177);
  43   INSERT INTO people VALUES('Megan','teacher',159);
  44   INSERT INTO people VALUES('Nathan','student',163);
  45   INSERT INTO people VALUES('Olivia','student',161);
  46   INSERT INTO people VALUES('Patrick','teacher',180);
  47   INSERT INTO people VALUES('Quiana','student',182);
  48   INSERT INTO people VALUES('Robert','student',159);
  49   INSERT INTO people VALUES('Sally','student',166);
  50   INSERT INTO people VALUES('Tom','student',171);
  51   INSERT INTO people VALUES('Ursula','student',170);
  52   INSERT INTO people VALUES('Vance','student',179);
  53   INSERT INTO people VALUES('Willma','student',175);
  54   INSERT INTO people VALUES('Xavier','teacher',185);
  55   INSERT INTO people VALUES('Yvonne','student',149);
  56   INSERT INTO people VALUES('Zach','student',170);
  57 }
  58
  59 # Without ANALYZE, a skip-scan is not used
  60 #
  61 do_execsql_test skipscan2-1.3 {
  62   SELECT name FROM people WHERE height>=180 ORDER BY +name;
  63 } {David Jack Patrick Quiana Xavier}
  64 do_execsql_test skipscan2-1.3eqp {
  65   EXPLAIN QUERY PLAN
  66   SELECT name FROM people WHERE height>=180 ORDER BY +name;
  67 } {~/*INDEX people_idx1 */}
  68
  69 # Now do an ANALYZE.  A skip-scan can be used after ANALYZE.
  70 #
  71 do_execsql_test skipscan2-1.4 {
  72   ANALYZE;
  73   -- We do not have enough people above to actually force the use
  74   -- of a skip-scan.  So make a manual adjustment to the stat1 table
  75   -- to make it seem like there are many more.
  76   UPDATE sqlite_stat1 SET stat='10000 5000 20' WHERE idx='people_idx1';
  77   UPDATE sqlite_stat1 SET stat='10000 1' WHERE idx='sqlite_autoindex_people_1';
  78   ANALYZE sqlite_master;
  79 }
  80 db cache flush
  81 do_execsql_test skipscan2-1.5 {
  82   SELECT name FROM people WHERE height>=180 ORDER BY +name;
  83 } {David Jack Patrick Quiana Xavier}
  84 do_execsql_test skipscan2-1.5eqp {
  85   EXPLAIN QUERY PLAN
  86   SELECT name FROM people WHERE height>=180 ORDER BY +name;
  87 } {/*INDEX people_idx1 */}
  88
  89 # Same answer with other formulations of the same query
  90 #
  91 do_execsql_test skipscan2-1.6 {
  92   SELECT name FROM people
  93    WHERE role IN (SELECT DISTINCT role FROM people)
  94      AND height>=180 ORDER BY +name;
  95 } {David Jack Patrick Quiana Xavier}
  96 do_execsql_test skipscan2-1.7 {
  97   SELECT name FROM people WHERE role='teacher' AND height>=180
  98   UNION ALL
  99   SELECT name FROM people WHERE role='student' AND height>=180
 100   ORDER BY 1;
 101 } {David Jack Patrick Quiana Xavier}
 102
 103 # Add 8 more people, bringing the total to 34.  Then the number of
 104 # duplicates in the left-column of the index will be 17 and
 105 # skip-scan should not be used after an (unfudged) ANALYZE.
 106 #
 107 do_execsql_test skipscan2-1.8 {
 108   INSERT INTO people VALUES('Angie','student',166);
 109   INSERT INTO people VALUES('Brad','student',176);
 110   INSERT INTO people VALUES('Claire','student',168);
 111   INSERT INTO people VALUES('Donald','student',162);
 112   INSERT INTO people VALUES('Elaine','student',177);
 113   INSERT INTO people VALUES('Frazier','student',159);
 114   INSERT INTO people VALUES('Grace','student',179);
 115   INSERT INTO people VALUES('Horace','student',166);
 116   ANALYZE;
 117   SELECT stat FROM sqlite_stat1 WHERE idx='people_idx1';
 118 } {{34 17 2}}
 119 db cache flush
 120 do_execsql_test skipscan2-1.9 {
 121   SELECT name FROM people WHERE height>=180 ORDER BY +name;
 122 } {David Jack Patrick Quiana Xavier}
 123 do_execsql_test skipscan2-1.9eqp {
 124   EXPLAIN QUERY PLAN
 125   SELECT name FROM people WHERE height>=180 ORDER BY +name;
 126 } {~/*INDEX people_idx1 */}
 127
 128 # Add 2 more people, bringing the total to 36.  Then the number of
 129 # duplicates in the left-column of the index will be 18 and
 130 # skip-scan will be used after an (unfudged) ANALYZE.
 131 #
 132 do_execsql_test skipscan2-1.10 {
 133   INSERT INTO people VALUES('Ingrad','student',155);
 134   INSERT INTO people VALUES('Jacob','student',179);
 135   ANALYZE;
 136   SELECT stat FROM sqlite_stat1 WHERE idx='people_idx1';
 137 } {{36 18 2}}
 138 db cache flush
 139 do_execsql_test skipscan2-1.11 {
 140   SELECT name FROM people WHERE height>=180 ORDER BY +name;
 141 } {David Jack Patrick Quiana Xavier}
 142 do_execsql_test skipscan2-1.11eqp {
 143   EXPLAIN QUERY PLAN
 144   SELECT name FROM people WHERE height>=180 ORDER BY +name;
 145 } {/*INDEX people_idx1 */}
 146
 147
 148 # Repeat using a WITHOUT ROWID table.
 149 #
 150 do_execsql_test skipscan2-2.1 {
 151   CREATE TABLE peoplew(
 152     name TEXT PRIMARY KEY,
 153     role TEXT NOT NULL,
 154     height INT NOT NULL, -- in cm
 155     CHECK( role IN ('student','teacher') )
 156   ) WITHOUT ROWID;
 157   CREATE INDEX peoplew_idx1 ON peoplew(role, height);
 158   INSERT INTO peoplew(name,role,height)
 159      SELECT name, role, height FROM  people;
 160   ALTER TABLE people RENAME TO old_people;
 161   SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
 162 } {David Jack Patrick Quiana Xavier}
 163 do_execsql_test skipscan2-2.2 {
 164   SELECT name FROM peoplew
 165    WHERE role IN (SELECT DISTINCT role FROM peoplew)
 166      AND height>=180 ORDER BY +name;
 167 } {David Jack Patrick Quiana Xavier}
 168 do_execsql_test skipscan2-2.2 {
 169   SELECT name FROM peoplew WHERE role='teacher' AND height>=180
 170   UNION ALL
 171   SELECT name FROM peoplew WHERE role='student' AND height>=180
 172   ORDER BY 1;
 173 } {David Jack Patrick Quiana Xavier}
 174
 175 # Now do an ANALYZE.  A skip-scan can be used after ANALYZE.
 176 #
 177 do_execsql_test skipscan2-2.4 {
 178   ANALYZE;
 179 }
 180 db cache flush
 181 do_execsql_test skipscan2-2.5 {
 182   SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
 183 } {David Jack Patrick Quiana Xavier}
 184 do_execsql_test skipscan2-2.5eqp {
 185   EXPLAIN QUERY PLAN
 186   SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
 187 } {/*INDEX peoplew_idx1 */}
 188
 189 # A skip-scan on a PK index of a WITHOUT ROWID table.
 190 #
 191 do_execsql_test skipscan2-3.1 {
 192   CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID;
 193 }
 194 do_test skipscan2-3.2 {
 195   for {set i 0} {$i < 1000} {incr i} {
 196     execsql { INSERT INTO t3 VALUES($i%2, $i, 'xyz') }
 197   }
 198   execsql { ANALYZE }
 199 } {}
 200 do_eqp_test skipscan2-3.3eqp {
 201   SELECT * FROM t3 WHERE b=42;
 202 } {0 0 0 {SEARCH TABLE t3 USING PRIMARY KEY (ANY(a) AND b=?)}}
 203
 204
 205 finish_test