gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/cost.test (about) 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 RIGHT PART 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 album USING INTEGER PRIMARY KEY (rowid=?) 207 |--SEARCH composer 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