/ Check-in [36373b85]
Login

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

Overview
Comment:Fixes to EXPLAIN QUERY PLAN output. Change weights back to something closer to what they are in legacy. More test case fixes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-logcost
Files: files | file ages | folders
SHA1: 36373b85f9a97840aa06e24ae31c12fcfbae084e
User & Date: drh 2013-06-11 02:32:50
Context
2013-06-11
02:36
Use a logarithmic rather than linear cost and row-count measures. Do not report row count estimates in EQP output. check-in: b777b109 user: drh tags: nextgen-query-plan-exp
02:32
Fixes to EXPLAIN QUERY PLAN output. Change weights back to something closer to what they are in legacy. More test case fixes. Closed-Leaf check-in: 36373b85 user: drh tags: nextgen-query-plan-logcost
01:50
Handle virtual tables correctly when using logarithmic costs. Fixes to test cases. check-in: e612664a user: drh tags: nextgen-query-plan-logcost
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  3880   3880   #ifndef SQLITE_OMIT_EXPLAIN
  3881   3881   static void explainSimpleCount(
  3882   3882     Parse *pParse,                  /* Parse context */
  3883   3883     Table *pTab,                    /* Table being queried */
  3884   3884     Index *pIdx                     /* Index used to optimize scan, or NULL */
  3885   3885   ){
  3886   3886     if( pParse->explain==2 ){
  3887         -    char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s %s%s(~%d rows)",
         3887  +    char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s%s%s",
  3888   3888           pTab->zName, 
  3889         -        pIdx ? "USING COVERING INDEX " : "",
  3890         -        pIdx ? pIdx->zName : "",
  3891         -        pTab->nRowEst
         3889  +        pIdx ? " USING COVERING INDEX " : "",
         3890  +        pIdx ? pIdx->zName : ""
  3892   3891       );
  3893   3892       sqlite3VdbeAddOp4(
  3894   3893           pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC
  3895   3894       );
  3896   3895     }
  3897   3896   }
  3898   3897   #else

Changes to src/where.c.

  4304   4304   #endif
  4305   4305       if( pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK) ){
  4306   4306         /* Step cost for each output row */
  4307   4307         pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut);
  4308   4308       }else{
  4309   4309         /* Each row involves a step of the index, then a binary search of
  4310   4310         ** the main table */
  4311         -      WhereCost rStepAndSearch = rLogSize>80 ? rLogSize-80 : 1;
         4311  +      WhereCost rStepAndSearch = whereCostAdd(10, rLogSize>17 ? rLogSize-17 : 1);
  4312   4312         pNew->rRun =  whereCostAdd(pNew->rRun, rStepAndSearch);
  4313   4313       }
  4314   4314       /* TBD: Adjust nOut for additional constraints */
  4315   4315       rc = whereLoopInsert(pBuilder, pNew);
  4316   4316       if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0
  4317   4317        && pNew->u.btree.nEq<=pProbe->nColumn
  4318   4318        && pProbe->zName!=0

Changes to test/like.test.

   162    162     set ::sqlite_sort_count 0
   163    163     set data [execsql $sql]
   164    164     if {$::sqlite_sort_count} {set x sort} {set x nosort}
   165    165     lappend data $x
   166    166     set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
   167    167     # puts eqp=$eqp
   168    168     foreach {a b c x} $eqp {
   169         -    if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\W} \
          169  +    if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\y} \
   170    170           $x all as tab idx]} {
   171    171         lappend data {} $idx
   172         -    } elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
          172  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \
   173    173           $x all as tab idx]} {
   174    174         lappend data $tab $idx
   175         -    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
          175  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} {
   176    176         lappend data $tab *
   177    177       }
   178    178     }
   179    179     return $data   
   180    180   }
   181    181   
   182    182   # Perform tests on the like optimization.

Changes to test/where.test.

    63     63   # small we can be assured that indices are being used properly.
    64     64   #
    65     65   do_test where-1.1.1 {
    66     66     count {SELECT x, y, w FROM t1 WHERE w=10}
    67     67   } {3 121 10 3}
    68     68   do_eqp_test where-1.1.2 {
    69     69     SELECT x, y, w FROM t1 WHERE w=10
    70         -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *}
           70  +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
    71     71   do_test where-1.1.3 {
    72     72     db status step
    73     73   } {0}
    74     74   do_test where-1.1.4 {
    75     75     db eval {SELECT x, y, w FROM t1 WHERE +w=10}
    76     76   } {3 121 10}
    77     77   do_test where-1.1.5 {
    78     78     db status step
    79     79   } {99}
    80     80   do_eqp_test where-1.1.6 {
    81     81     SELECT x, y, w FROM t1 WHERE +w=10
    82         -} {*SCAN TABLE t1 *}
           82  +} {*SCAN TABLE t1*}
    83     83   do_test where-1.1.7 {
    84     84     count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}
    85     85   } {3 121 10 3}
    86     86   do_eqp_test where-1.1.8 {
    87     87     SELECT x, y, w AS abc FROM t1 WHERE abc=10
    88         -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *}
           88  +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
    89     89   do_test where-1.1.9 {
    90     90     db status step
    91     91   } {0}
    92     92   do_test where-1.2.1 {
    93     93     count {SELECT x, y, w FROM t1 WHERE w=11}
    94     94   } {3 144 11 3}
    95     95   do_test where-1.2.2 {
................................................................................
   102    102     count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
   103    103   } {3 144 11 3}
   104    104   do_test where-1.4.1 {
   105    105     count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
   106    106   } {11 3 144 3}
   107    107   do_eqp_test where-1.4.2 {
   108    108     SELECT w, x, y FROM t1 WHERE 11=w AND x>2
   109         -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *}
          109  +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
   110    110   do_test where-1.4.3 {
   111    111     count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
   112    112   } {11 3 144 3}
   113    113   do_eqp_test where-1.4.4 {
   114    114     SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2
   115         -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *}
          115  +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
   116    116   do_test where-1.5 {
   117    117     count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
   118    118   } {3 144 3}
   119    119   do_eqp_test where-1.5.2 {
   120    120     SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2
   121         -} {*SEARCH TABLE t1 USING INDEX i1w (w=?) *}
          121  +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
   122    122   do_test where-1.6 {
   123    123     count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
   124    124   } {3 144 3}
   125    125   do_test where-1.7 {
   126    126     count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
   127    127   } {3 144 3}
   128    128   do_test where-1.8 {
   129    129     count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
   130    130   } {3 144 3}
   131    131   do_eqp_test where-1.8.2 {
   132    132     SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3
   133         -} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?) *}
          133  +} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?)*}
   134    134   do_eqp_test where-1.8.3 {
   135    135     SELECT x, y FROM t1 WHERE y=144 AND x=3
   136         -} {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?) *}
          136  +} {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?)*}
   137    137   do_test where-1.9 {
   138    138     count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
   139    139   } {3 144 3}
   140    140   do_test where-1.10 {
   141    141     count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
   142    142   } {3 121 3}
   143    143   do_test where-1.11 {

Changes to test/where2.test.

    72     72     set ::sqlite_sort_count 0
    73     73     set data [execsql $sql]
    74     74     if {$::sqlite_sort_count} {set x sort} {set x nosort}
    75     75     lappend data $x
    76     76     set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
    77     77     # puts eqp=$eqp
    78     78     foreach {a b c x} $eqp {
    79         -    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
           79  +    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \
    80     80           $x all as tab idx]} {
    81     81         lappend data $tab $idx
    82         -    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
           82  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} {
    83     83         lappend data $tab *
    84     84       }
    85     85     }
    86     86     return $data   
    87     87   }
    88     88   
    89     89   

Changes to test/where3.test.

   107    107   #
   108    108   proc queryplan {sql} {
   109    109     set ::sqlite_sort_count 0
   110    110     set data [execsql $sql]
   111    111     set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
   112    112     # puts eqp=$eqp
   113    113     foreach {a b c x} $eqp {
   114         -    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
          114  +    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \
   115    115           $x all as tab idx]} {
   116    116         lappend data $tab $idx
   117         -    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
          117  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} {
   118    118         lappend data $tab *
   119    119       }
   120    120     }
   121    121     return $data   
   122    122   }
   123    123   
   124    124   

Changes to test/whereE.test.

    43     43     INSERT INTO t2 SELECT x+32, (x+32)*11 FROM t2;
    44     44     INSERT INTO t2 SELECT x+64, (x+32)*11 FROM t2;
    45     45     ALTER TABLE t2 ADD COLUMN z;
    46     46     UPDATE t2 SET z=2;
    47     47     CREATE UNIQUE INDEX t2zx ON t2(z,x);
    48     48   
    49     49     EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x;
    50         -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/}
           50  +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/}
    51     51   do_execsql_test 1.2 {
    52     52     EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x;
    53         -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/}
           53  +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/}
    54     54   do_execsql_test 1.3 {
    55     55     ANALYZE;
    56     56     EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x;
    57         -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/}
           57  +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/}
    58     58   do_execsql_test 1.4 {
    59     59     EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x;
    60         -} {/.*SCAN TABLE t1 .*SEARCH TABLE t2 .*/}
           60  +} {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/}
    61     61   
    62     62   finish_test