test/cost.test

   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 TABLE t3 USING COVERING INDEX i3
  29   `--SEARCH TABLE 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 TABLE 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 TABLE t5 USING INDEX t5b (b=?)
  63   |  |--INDEX 2
  64   |  |  `--SEARCH TABLE t5 USING INDEX t5c (c=?)
  65   |  `--INDEX 3
  66   |     `--SEARCH TABLE 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 TABLE 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 TABLE 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 TABLE 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 TABLE 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 TABLE t3 USING INDEX t3i1 (b>? AND b<?)
 132   |  `--INDEX 2
 133   |     `--SEARCH TABLE 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 TABLE t1 USING INDEX t1b (b>? AND b<?)
 159   |  `--INDEX 2
 160   |     `--SEARCH TABLE 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 TABLE 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 TABLE 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 TABLE track
 206   |--SEARCH TABLE album USING INTEGER PRIMARY KEY (rowid=?)
 207   |--SEARCH TABLE 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 TABLE 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 TABLE 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 TABLE 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 TABLE t6 USING INDEX t6i1 (a=? AND b=?)}
 287 }
 288
 289 finish_test