/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/analyze5.test.

   113    113   
   114    114   } {
   115    115     do_test analyze5-1.$testid {
   116    116       eqp "SELECT * FROM t1 WHERE $where"
   117    117     } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z>? AND z<?) (~%d rows)}} \
   118    118          $rows]
   119    119   }
          120  +foreach {testid where rows} {
          121  +  101  {z=-1}           50
          122  +  102  {z=0}            400
          123  +  103  {z=1}            300
          124  +  104  {z=2}            200
          125  +  105  {z=3}            100
          126  +  106  {z=4}             50
          127  +  107  {z=-10.0}         50
          128  +  108  {z=0.0}          400
          129  +  109  {z=1.0}          300
          130  +  110  {z=2.0}          200
          131  +  111  {z=3.0}          100
          132  +  112  {z=4.0}           50
          133  +  113  {z=1.5}           50
          134  +  114  {z=2.5}           50
          135  +} {
          136  +  do_test analyze5-1.$testid {
          137  +    eqp "SELECT * FROM t1 WHERE $where"
          138  +  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z=?) (~%d rows)}} $rows]
          139  +}
          140  +
          141  +# For the t1.y column, most entries are known to be zero.  So do a 
          142  +# full table scan for y=0 but use the index for any other constraint on
          143  +# y.
          144  +#
          145  +do_test analyze5-201 {
          146  +  eqp {SELECT * FROM t1 WHERE y=0}
          147  +} {0 0 0 {SCAN TABLE t1 (~100 rows)}}
          148  +do_test analyze5-202 {
          149  +  eqp {SELECT * FROM t1 WHERE y=1}
          150  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1y (y=?) (~50 rows)}}
          151  +do_test analyze5-203 {
          152  +  eqp {SELECT * FROM t1 WHERE y=0.1}
          153  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1y (y=?) (~50 rows)}}
   120    154   
   121    155   # Change the table values from integer to floating point and then
   122    156   # repeat the same sequence of tests.  We should get the same results.
   123    157   #
   124    158   do_test analyze5-2.0 {
   125    159     db eval {
   126    160       UPDATE t1 SET z=z+0.0;
................................................................................
   176    210    52  {z>=-100 AND z<3.0}   900
   177    211   } {
   178    212     do_test analyze5-2.$testid {
   179    213       eqp "SELECT * FROM t1 WHERE $where"
   180    214     } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z>? AND z<?) (~%d rows)}} \
   181    215          $rows]
   182    216   }
          217  +foreach {testid where rows} {
          218  +  101  {z=-1}           50
          219  +  102  {z=0}            400
          220  +  103  {z=1}            300
          221  +  104  {z=2}            200
          222  +  105  {z=3}            100
          223  +  106  {z=4}             50
          224  +  107  {z=-10.0}         50
          225  +  108  {z=0.0}          400
          226  +  109  {z=1.0}          300
          227  +  110  {z=2.0}          200
          228  +  111  {z=3.0}          100
          229  +  112  {z=4.0}           50
          230  +  113  {z=1.5}           50
          231  +  114  {z=2.5}           50
          232  +} {
          233  +  do_test analyze5-2.$testid {
          234  +    eqp "SELECT * FROM t1 WHERE $where"
          235  +  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z=?) (~%d rows)}} $rows]
          236  +}
          237  +
   183    238   
   184    239   # Repeat the same range query tests using TEXT columns.
   185    240   #
   186    241   do_test analyze5-3.0 {
   187    242     db eval {
   188    243       UPDATE t1 SET y=CASE z WHEN 0 THEN 'alpha' WHEN 1 THEN 'bravo'
   189    244                              WHEN 2 THEN 'charlie' ELSE 'delta' END;
................................................................................
   224    279    30  {y>='echo' AND y<x'00'}          50
   225    280   } {
   226    281     do_test analyze5-3.$testid {
   227    282       eqp "SELECT * FROM t1 WHERE $where"
   228    283     } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1y (y>? AND y<?) (~%d rows)}} \
   229    284          $rows]
   230    285   }
          286  +foreach {testid where rows} {
          287  +  101  {y=0}                  50
          288  +  102  {y='alpha'}            400
          289  +  103  {y='bravo'}            300
          290  +  104  {y='charlie'}          200
          291  +  105  {y='delta'}            100
          292  +  106  {y='echo'}             50
          293  +  107  {y=''}                 50
          294  +  108  {y=x'0102'}            50
          295  +} {
          296  +  do_test analyze5-3.$testid {
          297  +    eqp "SELECT * FROM t1 WHERE $where"
          298  +  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1y (y=?) (~%d rows)}} $rows]
          299  +}
   231    300   
   232    301   
   233    302   finish_test