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 |
Timelines: | family | ancestors | descendants | both | nextgen-query-plan-logcost |
Files: | files | file ages | folders |
SHA1: |
36373b85f9a97840aa06e24ae31c12fc |
User & Date: | drh 2013-06-11 02:32:50.482 |
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: b777b1097d 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: 36373b85f9 user: drh tags: nextgen-query-plan-logcost) | |
01:50 | Handle virtual tables correctly when using logarithmic costs. Fixes to test cases. (check-in: e612664aa2 user: drh tags: nextgen-query-plan-logcost) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
3880 3881 3882 3883 3884 3885 3886 | #ifndef SQLITE_OMIT_EXPLAIN static void explainSimpleCount( Parse *pParse, /* Parse context */ Table *pTab, /* Table being queried */ Index *pIdx /* Index used to optimize scan, or NULL */ ){ if( pParse->explain==2 ){ | | | | < | 3880 3881 3882 3883 3884 3885 3886 3887 3888 3889 3890 3891 3892 3893 3894 3895 3896 3897 | #ifndef SQLITE_OMIT_EXPLAIN static void explainSimpleCount( Parse *pParse, /* Parse context */ Table *pTab, /* Table being queried */ Index *pIdx /* Index used to optimize scan, or NULL */ ){ if( pParse->explain==2 ){ char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s%s%s", pTab->zName, pIdx ? " USING COVERING INDEX " : "", pIdx ? pIdx->zName : "" ); sqlite3VdbeAddOp4( pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC ); } } #else |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
4304 4305 4306 4307 4308 4309 4310 | #endif if( pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK) ){ /* Step cost for each output row */ pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut); }else{ /* Each row involves a step of the index, then a binary search of ** the main table */ | | | 4304 4305 4306 4307 4308 4309 4310 4311 4312 4313 4314 4315 4316 4317 4318 | #endif if( pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK) ){ /* Step cost for each output row */ pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut); }else{ /* Each row involves a step of the index, then a binary search of ** the main table */ WhereCost rStepAndSearch = whereCostAdd(10, rLogSize>17 ? rLogSize-17 : 1); pNew->rRun = whereCostAdd(pNew->rRun, rStepAndSearch); } /* TBD: Adjust nOut for additional constraints */ rc = whereLoopInsert(pBuilder, pNew); if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 && pNew->u.btree.nEq<=pProbe->nColumn && pProbe->zName!=0 |
︙ | ︙ |
Changes to test/like.test.
︙ | ︙ | |||
162 163 164 165 166 167 168 | set ::sqlite_sort_count 0 set data [execsql $sql] if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x set eqp [execsql "EXPLAIN QUERY PLAN $sql"] # puts eqp=$eqp foreach {a b c x} $eqp { | | | | | 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 | set ::sqlite_sort_count 0 set data [execsql $sql] if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x set eqp [execsql "EXPLAIN QUERY PLAN $sql"] # puts eqp=$eqp foreach {a b c x} $eqp { if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\y} \ $x all as tab idx]} { lappend data {} $idx } elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \ $x all as tab idx]} { lappend data $tab $idx } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} { lappend data $tab * } } return $data } # Perform tests on the like optimization. |
︙ | ︙ |
Changes to test/where.test.
︙ | ︙ | |||
63 64 65 66 67 68 69 | # small we can be assured that indices are being used properly. # do_test where-1.1.1 { count {SELECT x, y, w FROM t1 WHERE w=10} } {3 121 10 3} do_eqp_test where-1.1.2 { SELECT x, y, w FROM t1 WHERE w=10 | | | | | | | | | | 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 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 | # small we can be assured that indices are being used properly. # do_test where-1.1.1 { count {SELECT x, y, w FROM t1 WHERE w=10} } {3 121 10 3} do_eqp_test where-1.1.2 { SELECT x, y, w FROM t1 WHERE w=10 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} do_test where-1.1.3 { db status step } {0} do_test where-1.1.4 { db eval {SELECT x, y, w FROM t1 WHERE +w=10} } {3 121 10} do_test where-1.1.5 { db status step } {99} do_eqp_test where-1.1.6 { SELECT x, y, w FROM t1 WHERE +w=10 } {*SCAN TABLE t1*} do_test where-1.1.7 { count {SELECT x, y, w AS abc FROM t1 WHERE abc=10} } {3 121 10 3} do_eqp_test where-1.1.8 { SELECT x, y, w AS abc FROM t1 WHERE abc=10 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} do_test where-1.1.9 { db status step } {0} do_test where-1.2.1 { count {SELECT x, y, w FROM t1 WHERE w=11} } {3 144 11 3} do_test where-1.2.2 { count {SELECT x, y, w AS abc FROM t1 WHERE abc=11} } {3 144 11 3} do_test where-1.3.1 { count {SELECT x, y, w AS abc FROM t1 WHERE 11=w} } {3 144 11 3} do_test where-1.3.2 { count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc} } {3 144 11 3} do_test where-1.4.1 { count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2} } {11 3 144 3} do_eqp_test where-1.4.2 { SELECT w, x, y FROM t1 WHERE 11=w AND x>2 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} do_test where-1.4.3 { count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2} } {11 3 144 3} do_eqp_test where-1.4.4 { SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} do_test where-1.5 { count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2} } {3 144 3} do_eqp_test where-1.5.2 { SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} do_test where-1.6 { count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11} } {3 144 3} do_test where-1.7 { count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2} } {3 144 3} do_test where-1.8 { count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3} } {3 144 3} do_eqp_test where-1.8.2 { SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3 } {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?)*} do_eqp_test where-1.8.3 { SELECT x, y FROM t1 WHERE y=144 AND x=3 } {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?)*} do_test where-1.9 { count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3} } {3 144 3} do_test where-1.10 { count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121} } {3 121 3} do_test where-1.11 { |
︙ | ︙ |
Changes to test/where2.test.
︙ | ︙ | |||
72 73 74 75 76 77 78 | set ::sqlite_sort_count 0 set data [execsql $sql] if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x set eqp [execsql "EXPLAIN QUERY PLAN $sql"] # puts eqp=$eqp foreach {a b c x} $eqp { | | | | 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 | set ::sqlite_sort_count 0 set data [execsql $sql] if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x set eqp [execsql "EXPLAIN QUERY PLAN $sql"] # puts eqp=$eqp foreach {a b c x} $eqp { if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \ $x all as tab idx]} { lappend data $tab $idx } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} { lappend data $tab * } } return $data } |
︙ | ︙ |
Changes to test/where3.test.
︙ | ︙ | |||
107 108 109 110 111 112 113 | # proc queryplan {sql} { set ::sqlite_sort_count 0 set data [execsql $sql] set eqp [execsql "EXPLAIN QUERY PLAN $sql"] # puts eqp=$eqp foreach {a b c x} $eqp { | | | | 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | # proc queryplan {sql} { set ::sqlite_sort_count 0 set data [execsql $sql] set eqp [execsql "EXPLAIN QUERY PLAN $sql"] # puts eqp=$eqp foreach {a b c x} $eqp { if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \ $x all as tab idx]} { lappend data $tab $idx } elseif {[regexp { TABLE (\w+ AS )?(\w+)\y} $x all as tab]} { lappend data $tab * } } return $data } |
︙ | ︙ |
Changes to test/whereE.test.
︙ | ︙ | |||
43 44 45 46 47 48 49 | INSERT INTO t2 SELECT x+32, (x+32)*11 FROM t2; INSERT INTO t2 SELECT x+64, (x+32)*11 FROM t2; ALTER TABLE t2 ADD COLUMN z; UPDATE t2 SET z=2; CREATE UNIQUE INDEX t2zx ON t2(z,x); EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x; | | | | | | 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | INSERT INTO t2 SELECT x+32, (x+32)*11 FROM t2; INSERT INTO t2 SELECT x+64, (x+32)*11 FROM t2; ALTER TABLE t2 ADD COLUMN z; UPDATE t2 SET z=2; CREATE UNIQUE INDEX t2zx ON t2(z,x); EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x; } {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/} do_execsql_test 1.2 { EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x; } {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/} do_execsql_test 1.3 { ANALYZE; EXPLAIN QUERY PLAN SELECT x FROM t1, t2 WHERE a=z AND c=x; } {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/} do_execsql_test 1.4 { EXPLAIN QUERY PLAN SELECT x FROM t2, t1 WHERE a=z AND c=x; } {/.*SCAN TABLE t1.*SEARCH TABLE t2.*/} finish_test |