/ Check-in [a2a9f640]
Login

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

Overview
Comment:Reactivate the analyze5.test script.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | stat2-enhancement
Files: files | file ages | folders
SHA1: a2a9f6401c927f6259cda3ba35219cabef24e84d
User & Date: drh 2011-01-28 03:13:58
Context
2011-02-04
06:36
Merge the stat2 query planner enhancements into the trunk. check-in: 499edcbc user: drh tags: trunk
2011-01-28
03:13
Reactivate the analyze5.test script. Closed-Leaf check-in: a2a9f640 user: drh tags: stat2-enhancement
01:57
Change the weighting of binary searches on tables to 1/10th the cost of a search on an index. Change the assumed reduction in search space from a indexed range constraint from 1/3rd to 1/4th. Do not let the estimated number of rows drop below 1. check-in: 4847c6cb user: drh tags: stat2-enhancement
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to test/analyze5.test.

    10     10   #***********************************************************************
    11     11   #
    12     12   # This file implements tests for SQLite library.  The focus of the tests
    13     13   # in this file is the use of the sqlite_stat2 histogram data on tables
    14     14   # with many repeated values and only a few distinct values.
    15     15   #
    16     16   
    17         -return
    18         -
    19     17   set testdir [file dirname $argv0]
    20     18   source $testdir/tester.tcl
    21     19   
    22     20   ifcapable !stat2 {
    23     21     finish_test
    24     22     return
    25     23   }
................................................................................
   100     98      14  {z>3 AND z<100}       t1z   50
   101     99      15  {z>=4 AND z<100}      t1z   50
   102    100      16  {z>=-100 AND z<=-1}   t1z   50
   103    101      17  {z>=-100 AND z<=0}    t1z  400
   104    102      18  {z>=-100 AND z<0}     t1z   50
   105    103      19  {z>=-100 AND z<=1}    t1z  700
   106    104      20  {z>=-100 AND z<2}     t1z  700
   107         -   21  {z>=-100 AND z<=2}    t1z  900
   108         -   22  {z>=-100 AND z<3}     t1z  900
          105  +   21  {z>=-100 AND z<=2}    {}   111
          106  +   22  {z>=-100 AND z<3}     {}   111
   109    107     
   110    108      31  {z>=0.0 AND z<=0.0}   t1z  400
   111    109      32  {z>=1.0 AND z<=1.0}   t1z  300
   112    110      33  {z>=2.0 AND z<=2.0}   t1z  200
   113    111      34  {z>=3.0 AND z<=3.0}   t1z  100
   114    112      35  {z>=4.0 AND z<=4.0}   t1z   50
   115    113      36  {z>=-1.0 AND z<=-1.0} t1z   50
................................................................................
   123    121      44  {z>3.2 AND z<100}     t1z   50
   124    122      45  {z>=4.0 AND z<100}    t1z   50
   125    123      46  {z>=-100 AND z<=-1.0} t1z   50
   126    124      47  {z>=-100 AND z<=0.0}  t1z  400
   127    125      48  {z>=-100 AND z<0.0}   t1z   50
   128    126      49  {z>=-100 AND z<=1.0}  t1z  700
   129    127      50  {z>=-100 AND z<2.0}   t1z  700
   130         -   51  {z>=-100 AND z<=2.0}  t1z  900
   131         -   52  {z>=-100 AND z<3.0}   t1z  900
          128  +   51  {z>=-100 AND z<=2.0}  {}   111
          129  +   52  {z>=-100 AND z<3.0}   {}   111
   132    130     
   133    131     101  {z=-1}                t1z   50
   134    132     102  {z=0}                 t1z  400
   135    133     103  {z=1}                 t1z  300
   136    134     104  {z=2}                 t1z  200
   137    135     105  {z=3}                 t1z  100
   138    136     106  {z=4}                 t1z   50
................................................................................
   149    147     202  {z IN (0)}            t1z  400
   150    148     203  {z IN (1)}            t1z  300
   151    149     204  {z IN (2)}            t1z  200
   152    150     205  {z IN (3)}            t1z  100
   153    151     206  {z IN (4)}            t1z   50
   154    152     207  {z IN (0.5)}          t1z   50
   155    153     208  {z IN (0,1)}          t1z  700
   156         -  209  {z IN (0,1,2)}        t1z  900
          154  +  209  {z IN (0,1,2)}        {}   100
   157    155     210  {z IN (0,1,2,3)}      {}   100
   158    156     211  {z IN (0,1,2,3,4,5)}  {}   100
   159    157     212  {z IN (1,2)}          t1z  500
   160    158     213  {z IN (2,3)}          t1z  300
   161    159     214  {z=3 OR z=2}          t1z  300
   162    160     215  {z IN (-1,3)}         t1z  150
   163    161     216  {z=-1 OR z=3}         t1z  150
................................................................................
   187    185         set res ok
   188    186       } else {
   189    187         set res "a1=\[$a1\] a2=\[$a2\]"
   190    188       }
   191    189       set res
   192    190     } {ok}
   193    191   }
   194         -exit
   195         -
   196         -# Change the table values from integer to floating point and then
   197         -# repeat the same sequence of tests.  We should get the same results.
   198         -#
   199         -do_test analyze5-2.0 {
   200         -  db eval {
   201         -    UPDATE t1 SET z=z+0.0;
   202         -    ANALYZE;
   203         -    SELECT sample FROM sqlite_stat2 WHERE idx='t1z' ORDER BY sampleno;
   204         -  }
   205         -} {0.0 0.0 0.0 0.0 1.0 1.0 1.0 2.0 2.0 3.0}
   206         -foreach {testid where rows} {
   207         -  1  {z>=0 AND z<=0}     400
   208         -  2  {z>=1 AND z<=1}     300
   209         -  3  {z>=2 AND z<=2}     200
   210         -  4  {z>=3 AND z<=3}     100
   211         -  5  {z>=4 AND z<=4}      50
   212         -  6  {z>=-1 AND z<=-1}    50
   213         -  7  {z>1 AND z<3}       200
   214         -  8  {z>0 AND z<100}     600
   215         -  9  {z>=1 AND z<100}    600
   216         - 10  {z>1 AND z<100}     300
   217         - 11  {z>=2 AND z<100}    300
   218         - 12  {z>2 AND z<100}     100
   219         - 13  {z>=3 AND z<100}    100
   220         - 14  {z>3 AND z<100}      50
   221         - 15  {z>=4 AND z<100}     50
   222         - 16  {z>=-100 AND z<=-1}  50
   223         - 17  {z>=-100 AND z<=0}  400
   224         - 18  {z>=-100 AND z<0}    50
   225         - 19  {z>=-100 AND z<=1}  700
   226         - 20  {z>=-100 AND z<2}   700
   227         - 21  {z>=-100 AND z<=2}  900
   228         - 22  {z>=-100 AND z<3}   900
   229         -
   230         - 31  {z>=0.0 AND z<=0.0}   400
   231         - 32  {z>=1.0 AND z<=1.0}   300
   232         - 33  {z>=2.0 AND z<=2.0}   200
   233         - 34  {z>=3.0 AND z<=3.0}   100
   234         - 35  {z>=4.0 AND z<=4.0}    50
   235         - 36  {z>=-1.0 AND z<=-1.0}  50
   236         - 37  {z>1.5 AND z<3.0}     200
   237         - 38  {z>0.5 AND z<100}     600
   238         - 39  {z>=1.0 AND z<100}    600
   239         - 40  {z>1.5 AND z<100}     300
   240         - 41  {z>=2.0 AND z<100}    300
   241         - 42  {z>2.1 AND z<100}     100
   242         - 43  {z>=3.0 AND z<100}    100
   243         - 44  {z>3.2 AND z<100}      50
   244         - 45  {z>=4.0 AND z<100}     50
   245         - 46  {z>=-100 AND z<=-1.0}  50
   246         - 47  {z>=-100 AND z<=0.0}  400
   247         - 48  {z>=-100 AND z<0.0}    50
   248         - 49  {z>=-100 AND z<=1.0}  700
   249         - 50  {z>=-100 AND z<2.0}   700
   250         - 51  {z>=-100 AND z<=2.0}  900
   251         - 52  {z>=-100 AND z<3.0}   900
   252         -} {
   253         -  do_test analyze5-2.$testid {
   254         -    eqp "SELECT * FROM t1 WHERE $where"
   255         -  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z>? AND z<?) (~%d rows)}} \
   256         -       $rows]
   257         -}
   258         -foreach {testid where rows} {
   259         -  101  {z=-1}           50
   260         -  102  {z=0}            400
   261         -  103  {z=1}            300
   262         -  104  {z=2}            200
   263         -  105  {z=3}            100
   264         -  106  {z=4}             50
   265         -  107  {z=-10.0}         50
   266         -  108  {z=0.0}          400
   267         -  109  {z=1.0}          300
   268         -  110  {z=2.0}          200
   269         -  111  {z=3.0}          100
   270         -  112  {z=4.0}           50
   271         -  113  {z=1.5}           50
   272         -  114  {z=2.5}           50
   273         -} {
   274         -  do_test analyze5-2.$testid {
   275         -    eqp "SELECT * FROM t1 WHERE $where"
   276         -  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1z (z=?) (~%d rows)}} $rows]
   277         -}
   278         -
   279         -
   280         -# Repeat the same range query tests using TEXT columns.
   281         -#
   282         -do_test analyze5-3.0 {
   283         -  db eval {
   284         -    UPDATE t1 SET y=CASE z WHEN 0 THEN 'alpha' WHEN 1 THEN 'bravo'
   285         -                           WHEN 2 THEN 'charlie' ELSE 'delta' END;
   286         -    ANALYZE;
   287         -    SELECT sample FROM sqlite_stat2 WHERE idx='t1y' ORDER BY sampleno;
   288         -  }
   289         -} {alpha alpha alpha alpha bravo bravo bravo charlie charlie delta}
   290         -foreach {testid where rows} {
   291         -  1  {y>='alpha' AND y<='alpha'}     400
   292         -  2  {y>='bravo' AND y<='bravo'}     300
   293         -  3  {y>='charlie' AND y<='charlie'} 200
   294         -  4  {y>='delta' AND y<='delta'}     100
   295         -  5  {y>='echo' AND y<='echo'}        50
   296         -  6  {y>='' AND y<=''}                50
   297         -  7  {y>'bravo' AND y<'delta'}       200
   298         -  8  {y>'alpha' AND y<'zzz'}         600
   299         -  9  {y>='bravo' AND y<'zzz'}        600
   300         - 10  {y>'bravo' AND y<'zzz'}         300
   301         - 11  {y>='charlie' AND y<'zzz'}      300
   302         - 12  {y>'charlie' AND y<'zzz'}       100
   303         - 13  {y>='delta' AND y<'zzz'}        100
   304         - 14  {y>'delta' AND y<'zzz'}          50
   305         - 15  {y>='echo' AND y<'zzz'}          50
   306         - 16  {y>=0 AND y<=''}                 50
   307         - 17  {y>=0 AND y<='alpha'}           400
   308         - 18  {y>=0 AND y<'alpha'}             50
   309         - 19  {y>=0 AND y<='bravo'}           700
   310         - 20  {y>=0 AND y<'charlie'}          700
   311         - 21  {y>=0 AND y<='charlie'}         900
   312         - 22  {y>=0 AND y<'delta'}            900
   313         - 23  {y>'alpha' AND y<x'00'}         600
   314         - 24  {y>='bravo' AND y<x'00'}        600
   315         - 25  {y>'bravo' AND y<x'00'}         300
   316         - 26  {y>='charlie' AND y<x'00'}      300
   317         - 27  {y>'charlie' AND y<x'00'}       100
   318         - 28  {y>='delta' AND y<x'00'}        100
   319         - 29  {y>'delta' AND y<x'00'}          50
   320         - 30  {y>='echo' AND y<x'00'}          50
   321         -} {
   322         -  do_test analyze5-3.$testid {
   323         -    eqp "SELECT * FROM t1 WHERE $where"
   324         -  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1y (y>? AND y<?) (~%d rows)}} \
   325         -       $rows]
   326         -}
   327         -foreach {testid where rows} {
   328         -  101  {y=0}                  50
   329         -  102  {y='alpha'}            400
   330         -  103  {y='bravo'}            300
   331         -  104  {y='charlie'}          200
   332         -  105  {y='delta'}            100
   333         -  106  {y='echo'}             50
   334         -  107  {y=''}                 50
   335         -  108  {y=x'0102'}            50
   336         -} {
   337         -  do_test analyze5-3.$testid {
   338         -    eqp "SELECT * FROM t1 WHERE $where"
   339         -  } [format {0 0 0 {SEARCH TABLE t1 USING INDEX t1y (y=?) (~%d rows)}} $rows]
   340         -}
   341    192   
   342    193   
   343    194   finish_test