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