Debian Codegraph @

test/cost.test

raw
1 # 2014-04-26
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 cost
16
17
18 do_execsql_test 1.1 {
19 CREATE TABLE t3(id INTEGER PRIMARY KEY, b NOT NULL);
20 CREATE TABLE t4(c, d, e);
21 CREATE UNIQUE INDEX i3 ON t3(b);
22 CREATE UNIQUE INDEX i4 ON t4(c, d);
23 }
24 do_eqp_test 1.2 {
25 SELECT e FROM t3, t4 WHERE b=c ORDER BY b, d;
26 } {
27 QUERY PLAN
28 |--SCAN t3 USING COVERING INDEX i3
29 `--SEARCH t4 USING INDEX i4 (c=?)
30 }
31
32
33 do_execsql_test 2.1 {
34 CREATE TABLE t1(a, b);
35 CREATE INDEX i1 ON t1(a);
36 }
37
38 # It is better to use an index for ORDER BY than sort externally, even
39 # if the index is a non-covering index.
40 do_eqp_test 2.2 {
41 SELECT * FROM t1 ORDER BY a;
42 } {SCAN t1 USING INDEX i1}
43
44 do_execsql_test 3.1 {
45 CREATE TABLE t5(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
46 CREATE INDEX t5b ON t5(b);
47 CREATE INDEX t5c ON t5(c);
48 CREATE INDEX t5d ON t5(d);
49 CREATE INDEX t5e ON t5(e);
50 CREATE INDEX t5f ON t5(f);
51 CREATE INDEX t5g ON t5(g);
52 }
53
54 do_eqp_test 3.2 {
55 SELECT a FROM t5
56 WHERE b IS NULL OR c IS NULL OR d IS NULL
57 ORDER BY a;
58 } {
59 QUERY PLAN
60 |--MULTI-INDEX OR
61 | |--INDEX 1
62 | | `--SEARCH t5 USING INDEX t5b (b=?)
63 | |--INDEX 2
64 | | `--SEARCH t5 USING INDEX t5c (c=?)
65 | `--INDEX 3
66 | `--SEARCH t5 USING INDEX t5d (d=?)
67 `--USE TEMP B-TREE FOR ORDER BY
68 }
69
70 #-------------------------------------------------------------------------
71 # If there is no likelihood() or stat3 data, SQLite assumes that a closed
72 # range scan (e.g. one constrained by "col BETWEEN ? AND ?" constraint)
73 # visits 1/64 of the rows in a table.
74 #
75 # Note: 1/63 =~ 0.016
76 # Note: 1/65 =~ 0.015
77 #
78 reset_db
79 do_execsql_test 4.1 {
80 CREATE TABLE t1(a, b);
81 CREATE INDEX i1 ON t1(a);
82 CREATE INDEX i2 ON t1(b);
83 }
84 do_eqp_test 4.2 {
85 SELECT * FROM t1 WHERE likelihood(a=?, 0.014) AND b BETWEEN ? AND ?;
86 } {SEARCH t1 USING INDEX i1 (a=?)}
87
88 do_eqp_test 4.3 {
89 SELECT * FROM t1 WHERE likelihood(a=?, 0.016) AND b BETWEEN ? AND ?;
90 } {SEARCH t1 USING INDEX i2 (b>? AND b<?)}
91
92
93 #-------------------------------------------------------------------------
94 #
95 reset_db
96 do_execsql_test 5.1 {
97 CREATE TABLE t2(x, y);
98 CREATE INDEX t2i1 ON t2(x);
99 }
100
101 do_eqp_test 5.2 {
102 SELECT * FROM t2 ORDER BY x, y;
103 } {
104 QUERY PLAN
105 |--SCAN t2 USING INDEX t2i1
106 `--USE TEMP B-TREE FOR LAST TERM OF ORDER BY
107 }
108
109 do_eqp_test 5.3 {
110 SELECT * FROM t2 WHERE x BETWEEN ? AND ? ORDER BY rowid;
111 } {
112 QUERY PLAN
113 |--SEARCH t2 USING INDEX t2i1 (x>? AND x<?)
114 `--USE TEMP B-TREE FOR ORDER BY
115 }
116
117 # where7.test, where8.test:
118 #
119 do_execsql_test 6.1 {
120 CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c);
121 CREATE INDEX t3i1 ON t3(b);
122 CREATE INDEX t3i2 ON t3(c);
123 }
124
125 do_eqp_test 6.2 {
126 SELECT a FROM t3 WHERE (b BETWEEN 2 AND 4) OR c=100 ORDER BY a
127 } {
128 QUERY PLAN
129 |--MULTI-INDEX OR
130 | |--INDEX 1
131 | | `--SEARCH t3 USING INDEX t3i1 (b>? AND b<?)
132 | `--INDEX 2
133 | `--SEARCH t3 USING INDEX t3i2 (c=?)
134 `--USE TEMP B-TREE FOR ORDER BY
135 }
136
137 #-------------------------------------------------------------------------
138 #
139 reset_db
140 do_execsql_test 7.1 {
141 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
142 CREATE INDEX t1b ON t1(b);
143 CREATE INDEX t1c ON t1(c);
144 CREATE INDEX t1d ON t1(d);
145 CREATE INDEX t1e ON t1(e);
146 CREATE INDEX t1f ON t1(f);
147 CREATE INDEX t1g ON t1(g);
148 }
149
150 do_eqp_test 7.2 {
151 SELECT a FROM t1
152 WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
153 ORDER BY a
154 } {
155 QUERY PLAN
156 |--MULTI-INDEX OR
157 | |--INDEX 1
158 | | `--SEARCH t1 USING INDEX t1b (b>? AND b<?)
159 | `--INDEX 2
160 | `--SEARCH t1 USING INDEX t1b (b=?)
161 `--USE TEMP B-TREE FOR ORDER BY
162 }
163
164 do_eqp_test 7.3 {
165 SELECT rowid FROM t1
166 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
167 OR (b NOT NULL AND c IS NULL AND d NOT NULL)
168 OR (b NOT NULL AND c NOT NULL AND d IS NULL)
169 } {SCAN t1}
170
171 do_eqp_test 7.4 {
172 SELECT rowid FROM t1 WHERE (+b IS NULL AND c NOT NULL) OR c IS NULL
173 } {SCAN t1}
174
175 #-------------------------------------------------------------------------
176 #
177 reset_db
178 do_execsql_test 8.1 {
179 CREATE TABLE composer(
180 cid INTEGER PRIMARY KEY,
181 cname TEXT
182 );
183 CREATE TABLE album(
184 aid INTEGER PRIMARY KEY,
185 aname TEXT
186 );
187 CREATE TABLE track(
188 tid INTEGER PRIMARY KEY,
189 cid INTEGER REFERENCES composer,
190 aid INTEGER REFERENCES album,
191 title TEXT
192 );
193 CREATE INDEX track_i1 ON track(cid);
194 CREATE INDEX track_i2 ON track(aid);
195 }
196
197 do_eqp_test 8.2 {
198 SELECT DISTINCT aname
199 FROM album, composer, track
200 WHERE cname LIKE '%bach%'
201 AND unlikely(composer.cid=track.cid)
202 AND unlikely(album.aid=track.aid);
203 } {
204 QUERY PLAN
205 |--SCAN track
206 |--SEARCH composer USING INTEGER PRIMARY KEY (rowid=?)
207 |--SEARCH album USING INTEGER PRIMARY KEY (rowid=?)
208 `--USE TEMP B-TREE FOR DISTINCT
209 }
210
211 #-------------------------------------------------------------------------
212 #
213 do_execsql_test 9.1 {
214 CREATE TABLE t1(
215 a,b,c,d,e, f,g,h,i,j,
216 k,l,m,n,o, p,q,r,s,t
217 );
218 CREATE INDEX i1 ON t1(k,l,m,n,o,p,q,r,s,t);
219 }
220 do_test 9.2 {
221 for {set i 0} {$i < 100} {incr i} {
222 execsql { INSERT INTO t1 DEFAULT VALUES }
223 }
224 execsql {
225 ANALYZE;
226 CREATE INDEX i2 ON t1(a,b,c,d,e,f,g,h,i,j);
227 }
228 } {}
229
230 set L [list a=? b=? c=? d=? e=? f=? g=? h=? i=? j=?]
231 foreach {tn nTerm nRow} {
232 1 1 10
233 2 2 10
234 3 3 8
235 4 4 7
236 5 5 7
237 6 6 5
238 7 7 5
239 8 8 5
240 9 9 5
241 10 10 5
242 } {
243 set w [join [lrange $L 0 [expr $nTerm-1]] " AND "]
244 set p1 [expr ($nRow-1) / 100.0]
245 set p2 [expr ($nRow+1) / 100.0]
246
247 set sql1 "SELECT * FROM t1 WHERE likelihood(k=?, $p1) AND $w"
248 set sql2 "SELECT * FROM t1 WHERE likelihood(k=?, $p2) AND $w"
249
250 do_eqp_test 9.3.$tn.1 $sql1 {/INDEX i1/}
251 do_eqp_test 9.3.$tn.2 $sql2 {/INDEX i2/}
252 }
253
254
255 #-------------------------------------------------------------------------
256 #
257
258 ifcapable stat4 {
259 do_execsql_test 10.1 {
260 CREATE TABLE t6(a, b, c);
261 CREATE INDEX t6i1 ON t6(a, b);
262 CREATE INDEX t6i2 ON t6(c);
263 }
264
265 do_test 10.2 {
266 for {set i 0} {$i < 16} {incr i} {
267 execsql { INSERT INTO t6 VALUES($i%4, 'xyz', $i%8) }
268 }
269 execsql ANALYZE
270 } {}
271
272 do_eqp_test 10.3 {
273 SELECT rowid FROM t6 WHERE a=0 AND c=0
274 } {SEARCH t6 USING INDEX t6i2 (c=?)}
275
276 do_eqp_test 10.4 {
277 SELECT rowid FROM t6 WHERE a=0 AND b='xyz' AND c=0
278 } {SEARCH t6 USING INDEX t6i2 (c=?)}
279
280 do_eqp_test 10.5 {
281 SELECT rowid FROM t6 WHERE likelihood(a=0, 0.1) AND c=0
282 } {SEARCH t6 USING INDEX t6i1 (a=?)}
283
284 do_eqp_test 10.6 {
285 SELECT rowid FROM t6 WHERE likelihood(a=0, 0.1) AND b='xyz' AND c=0
286 } {SEARCH t6 USING INDEX t6i1 (a=? AND b=?)}
287 }
288
289 finish_test