| blob | a42ff2d0578de69155c38a3a36c25a8d703d64a9 |
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 #
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
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 }
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 */}
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 */}
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}
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 */}
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 */}
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}
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 */}
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=?)}}
205 finish_test