/ Check-in [35f46a55]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update test script analyze3.test to account for the fact that SQLite now prefers a full-table scan over a non-covering index scan that visits a large percentage of the table rows.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental-costs
Files: files | file ages | folders
SHA1: 35f46a55d866b9a87c1321aab8e0cfe86ccadb93
User & Date: dan 2014-04-28 10:00:59
Context
2014-04-28
12:08
Add an extra column to a table in analyze9.test to give the planner a little more reason to select an index. check-in: 1b95544f user: dan tags: experimental-costs
10:00
Update test script analyze3.test to account for the fact that SQLite now prefers a full-table scan over a non-covering index scan that visits a large percentage of the table rows. check-in: 35f46a55 user: dan tags: experimental-costs
09:35
Modify internal function whereLoopAdjustCost() so that it does not prefer a skip-scan over a regular index scan even if the regular scan uses a subset of the WHERE terms used by the skip-scan. check-in: 88a5758d user: dan tags: experimental-costs
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/analyze3.test.

    99     99     ifcapable stat4 {
   100    100       execsql { SELECT count(*)>0 FROM sqlite_stat4; }
   101    101     } else {
   102    102       execsql { SELECT count(*)>0 FROM sqlite_stat3; }
   103    103     }
   104    104   } {1}
   105    105   
          106  +do_execsql_test analyze3-1.1.x {
          107  +  SELECT count(*) FROM t1 WHERE x>200 AND x<300;
          108  +  SELECT count(*) FROM t1 WHERE x>0 AND x<1100;
          109  +} {99 1000}
          110  +
          111  +# The first of the following two SELECT statements visits 99 rows. So
          112  +# it is better to use the index. But the second visits every row in 
          113  +# the table (1000 in total) so it is better to do a full-table scan.
          114  +#
   106    115   do_eqp_test analyze3-1.1.2 {
   107    116     SELECT sum(y) FROM t1 WHERE x>200 AND x<300
   108    117   } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}
   109    118   do_eqp_test analyze3-1.1.3 {
   110    119     SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 
   111         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}
          120  +} {0 0 0 {SCAN TABLE t1}}
   112    121   
   113    122   do_test analyze3-1.1.4 {
   114    123     sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
   115    124   } {199 0 14850}
   116    125   do_test analyze3-1.1.5 {
   117    126     set l [string range "200" 0 end]
   118    127     set u [string range "300" 0 end]
................................................................................
   121    130   do_test analyze3-1.1.6 {
   122    131     set l [expr int(200)]
   123    132     set u [expr int(300)]
   124    133     sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
   125    134   } {199 0 14850}
   126    135   do_test analyze3-1.1.7 {
   127    136     sf_execsql { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 }
   128         -} {2000 0 499500}
          137  +} {999 999 499500}
   129    138   do_test analyze3-1.1.8 {
   130    139     set l [string range "0" 0 end]
   131    140     set u [string range "1100" 0 end]
   132    141     sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
   133         -} {2000 0 499500}
          142  +} {999 999 499500}
   134    143   do_test analyze3-1.1.9 {
   135    144     set l [expr int(0)]
   136    145     set u [expr int(1100)]
   137    146     sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
   138         -} {2000 0 499500}
          147  +} {999 999 499500}
   139    148   
   140    149   
   141    150   # The following tests are similar to the block above. The difference is
   142    151   # that the indexed column has TEXT affinity in this case. In the tests
   143    152   # above the affinity is INTEGER.
   144    153   #
   145    154   do_test analyze3-1.2.1 {
................................................................................
   148    157         CREATE TABLE t2(x TEXT, y);
   149    158         INSERT INTO t2 SELECT * FROM t1;
   150    159         CREATE INDEX i2 ON t2(x);
   151    160       COMMIT;
   152    161       ANALYZE;
   153    162     }
   154    163   } {}
          164  +do_execsql_test analyze3-2.1.x {
          165  +  SELECT count(*) FROM t2 WHERE x>1 AND x<2;
          166  +  SELECT count(*) FROM t2 WHERE x>0 AND x<99;
          167  +} {200 990}
   155    168   do_eqp_test analyze3-1.2.2 {
   156    169     SELECT sum(y) FROM t2 WHERE x>1 AND x<2
   157    170   } {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?)}}
   158    171   do_eqp_test analyze3-1.2.3 {
   159    172     SELECT sum(y) FROM t2 WHERE x>0 AND x<99
   160         -} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?)}}
          173  +} {0 0 0 {SCAN TABLE t2}}
          174  +
   161    175   do_test analyze3-1.2.4 {
   162    176     sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 }
   163    177   } {161 0 4760}
   164    178   do_test analyze3-1.2.5 {
   165    179     set l [string range "12" 0 end]
   166    180     set u [string range "20" 0 end]
   167    181     sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
................................................................................
   169    183   do_test analyze3-1.2.6 {
   170    184     set l [expr int(12)]
   171    185     set u [expr int(20)]
   172    186     sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
   173    187   } {161 0 integer integer 4760}
   174    188   do_test analyze3-1.2.7 {
   175    189     sf_execsql { SELECT sum(y) FROM t2 WHERE x>0 AND x<99 }
   176         -} {1981 0 490555}
          190  +} {999 999 490555}
   177    191   do_test analyze3-1.2.8 {
   178    192     set l [string range "0" 0 end]
   179    193     set u [string range "99" 0 end]
   180    194     sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
   181         -} {1981 0 text text 490555}
          195  +} {999 999 text text 490555}
   182    196   do_test analyze3-1.2.9 {
   183    197     set l [expr int(0)]
   184    198     set u [expr int(99)]
   185    199     sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
   186         -} {1981 0 integer integer 490555}
          200  +} {999 999 integer integer 490555}
   187    201   
   188    202   # Same tests a third time. This time, column x has INTEGER affinity and
   189    203   # is not the leftmost column of the table. This triggered a bug causing
   190    204   # SQLite to use sub-optimal query plans in 3.6.18 and earlier.
   191    205   #
   192    206   do_test analyze3-1.3.1 {
   193    207     execsql {
................................................................................
   195    209         CREATE TABLE t3(y TEXT, x INTEGER);
   196    210         INSERT INTO t3 SELECT y, x FROM t1;
   197    211         CREATE INDEX i3 ON t3(x);
   198    212       COMMIT;
   199    213       ANALYZE;
   200    214     }
   201    215   } {}
          216  +do_execsql_test analyze3-1.3.x {
          217  +  SELECT count(*) FROM t3 WHERE x>200 AND x<300;
          218  +  SELECT count(*) FROM t3 WHERE x>0 AND x<1100
          219  +} {99 1000}
   202    220   do_eqp_test analyze3-1.3.2 {
   203    221     SELECT sum(y) FROM t3 WHERE x>200 AND x<300
   204    222   } {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?)}}
   205    223   do_eqp_test analyze3-1.3.3 {
   206    224     SELECT sum(y) FROM t3 WHERE x>0 AND x<1100
   207         -} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?)}}
          225  +} {0 0 0 {SCAN TABLE t3}}
   208    226   
   209    227   do_test analyze3-1.3.4 {
   210    228     sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
   211    229   } {199 0 14850}
   212    230   do_test analyze3-1.3.5 {
   213    231     set l [string range "200" 0 end]
   214    232     set u [string range "300" 0 end]
................................................................................
   217    235   do_test analyze3-1.3.6 {
   218    236     set l [expr int(200)]
   219    237     set u [expr int(300)]
   220    238     sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
   221    239   } {199 0 14850}
   222    240   do_test analyze3-1.3.7 {
   223    241     sf_execsql { SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 }
   224         -} {2000 0 499500}
          242  +} {999 999 499500}
   225    243   do_test analyze3-1.3.8 {
   226    244     set l [string range "0" 0 end]
   227    245     set u [string range "1100" 0 end]
   228    246     sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
   229         -} {2000 0 499500}
          247  +} {999 999 499500}
   230    248   do_test analyze3-1.3.9 {
   231    249     set l [expr int(0)]
   232    250     set u [expr int(1100)]
   233    251     sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
   234         -} {2000 0 499500}
          252  +} {999 999 499500}
   235    253   
   236    254   #-------------------------------------------------------------------------
   237    255   # Test that the values of bound SQL variables may be used for the LIKE
   238    256   # optimization.
   239    257   #
   240    258   drop_all_tables
   241    259   do_test analyze3-2.1 {