/ Check-in [c7b59afa]
Login

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

Overview
Comment:Update ANALYZE test cases to check out the use of histograms for equality constraints.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | stat2-enhancement
Files: files | file ages | folders
SHA1:c7b59afaf0c0bf85dbaf0a122cc8d65fca93680f
User & Date: drh 2011-01-20 20:36:13
Context
2011-01-21
14:37
Add the ability to use indices when a range contraint is bounded on the lower end by NULL. check-in: f73a167b user: drh tags: stat2-enhancement
2011-01-20
20:36
Update ANALYZE test cases to check out the use of histograms for equality constraints. check-in: c7b59afa user: drh tags: stat2-enhancement
16:52
Use histogram data to improve the row-count estimates on equality constraints. check-in: 6bfc5c69 user: drh tags: stat2-enhancement
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/analyze5.test.

113
114
115
116
117
118
119


































120
121
122
123
124
125
126
...
176
177
178
179
180
181
182





















183
184
185
186
187
188
189
...
224
225
226
227
228
229
230














231
232
233

} {
  do_test analyze5-1.$testid {
    eqp "SELECT * FROM t1 WHERE $where"
  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z>? AND z<?) (~%d rows)}} \
       $rows]
}



































# Change the table values from integer to floating point and then
# repeat the same sequence of tests.  We should get the same results.
#
do_test analyze5-2.0 {
  db eval {
    UPDATE t1 SET z=z+0.0;
................................................................................
 52  {z>=-100 AND z<3.0}   900
} {
  do_test analyze5-2.$testid {
    eqp "SELECT * FROM t1 WHERE $where"
  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z>? AND z<?) (~%d rows)}} \
       $rows]
}






















# Repeat the same range query tests using TEXT columns.
#
do_test analyze5-3.0 {
  db eval {
    UPDATE t1 SET y=CASE z WHEN 0 THEN 'alpha' WHEN 1 THEN 'bravo'
                           WHEN 2 THEN 'charlie' ELSE 'delta' END;
................................................................................
 30  {y>='echo' AND y<x'00'}          50
} {
  do_test analyze5-3.$testid {
    eqp "SELECT * FROM t1 WHERE $where"
  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1y (y>? AND y<?) (~%d rows)}} \
       $rows]
}
















finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>



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
...
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
...
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302

} {
  do_test analyze5-1.$testid {
    eqp "SELECT * FROM t1 WHERE $where"
  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z>? AND z<?) (~%d rows)}} \
       $rows]
}
foreach {testid where rows} {
  101  {z=-1}           50
  102  {z=0}            400
  103  {z=1}            300
  104  {z=2}            200
  105  {z=3}            100
  106  {z=4}             50
  107  {z=-10.0}         50
  108  {z=0.0}          400
  109  {z=1.0}          300
  110  {z=2.0}          200
  111  {z=3.0}          100
  112  {z=4.0}           50
  113  {z=1.5}           50
  114  {z=2.5}           50
} {
  do_test analyze5-1.$testid {
    eqp "SELECT * FROM t1 WHERE $where"
  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z=?) (~%d rows)}} $rows]
}

# For the t1.y column, most entries are known to be zero.  So do a 
# full table scan for y=0 but use the index for any other constraint on
# y.
#
do_test analyze5-201 {
  eqp {SELECT * FROM t1 WHERE y=0}
} {0 0 0 {SCAN TABLE t1 (~100 rows)}}
do_test analyze5-202 {
  eqp {SELECT * FROM t1 WHERE y=1}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1y (y=?) (~50 rows)}}
do_test analyze5-203 {
  eqp {SELECT * FROM t1 WHERE y=0.1}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1y (y=?) (~50 rows)}}

# Change the table values from integer to floating point and then
# repeat the same sequence of tests.  We should get the same results.
#
do_test analyze5-2.0 {
  db eval {
    UPDATE t1 SET z=z+0.0;
................................................................................
 52  {z>=-100 AND z<3.0}   900
} {
  do_test analyze5-2.$testid {
    eqp "SELECT * FROM t1 WHERE $where"
  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z>? AND z<?) (~%d rows)}} \
       $rows]
}
foreach {testid where rows} {
  101  {z=-1}           50
  102  {z=0}            400
  103  {z=1}            300
  104  {z=2}            200
  105  {z=3}            100
  106  {z=4}             50
  107  {z=-10.0}         50
  108  {z=0.0}          400
  109  {z=1.0}          300
  110  {z=2.0}          200
  111  {z=3.0}          100
  112  {z=4.0}           50
  113  {z=1.5}           50
  114  {z=2.5}           50
} {
  do_test analyze5-2.$testid {
    eqp "SELECT * FROM t1 WHERE $where"
  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z=?) (~%d rows)}} $rows]
}


# Repeat the same range query tests using TEXT columns.
#
do_test analyze5-3.0 {
  db eval {
    UPDATE t1 SET y=CASE z WHEN 0 THEN 'alpha' WHEN 1 THEN 'bravo'
                           WHEN 2 THEN 'charlie' ELSE 'delta' END;
................................................................................
 30  {y>='echo' AND y<x'00'}          50
} {
  do_test analyze5-3.$testid {
    eqp "SELECT * FROM t1 WHERE $where"
  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1y (y>? AND y<?) (~%d rows)}} \
       $rows]
}
foreach {testid where rows} {
  101  {y=0}                  50
  102  {y='alpha'}            400
  103  {y='bravo'}            300
  104  {y='charlie'}          200
  105  {y='delta'}            100
  106  {y='echo'}             50
  107  {y=''}                 50
  108  {y=x'0102'}            50
} {
  do_test analyze5-3.$testid {
    eqp "SELECT * FROM t1 WHERE $where"
  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1y (y=?) (~%d rows)}} $rows]
}


finish_test