gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/skipscan2.test (about) 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 SELECT name FROM peoplew WHERE height>=180 ORDER BY +name; 161 } {David Jack Patrick Quiana Xavier} 162 do_execsql_test skipscan2-2.2 { 163 SELECT name FROM peoplew 164 WHERE role IN (SELECT DISTINCT role FROM peoplew) 165 AND height>=180 ORDER BY +name; 166 } {David Jack Patrick Quiana Xavier} 167 do_execsql_test skipscan2-2.2 { 168 SELECT name FROM peoplew WHERE role='teacher' AND height>=180 169 UNION ALL 170 SELECT name FROM peoplew WHERE role='student' AND height>=180 171 ORDER BY 1; 172 } {David Jack Patrick Quiana Xavier} 173 174 # Now do an ANALYZE. A skip-scan can be used after ANALYZE. 175 # 176 do_execsql_test skipscan2-2.4 { 177 ANALYZE; 178 } 179 db cache flush 180 do_execsql_test skipscan2-2.5 { 181 SELECT name FROM peoplew WHERE height>=180 ORDER BY +name; 182 } {David Jack Patrick Quiana Xavier} 183 do_execsql_test skipscan2-2.5eqp { 184 EXPLAIN QUERY PLAN 185 SELECT name FROM peoplew WHERE height>=180 ORDER BY +name; 186 } {/*INDEX peoplew_idx1 */} 187 188 # A skip-scan on a PK index of a WITHOUT ROWID table. 189 # 190 do_execsql_test skipscan2-3.1 { 191 CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID; 192 } 193 do_test skipscan2-3.2 { 194 for {set i 0} {$i < 1000} {incr i} { 195 execsql { INSERT INTO t3 VALUES($i%2, $i, 'xyz') } 196 } 197 execsql { ANALYZE } 198 } {} 199 do_eqp_test skipscan2-3.3eqp { 200 SELECT * FROM t3 WHERE b=42; 201 } {SEARCH t3 USING PRIMARY KEY (ANY(a) AND b=?)} 202 203 204 205 finish_test