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: |
36373b85f9a97840aa06e24ae31c12fc |
| 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
Changes to src/select.c.
3880 3881 3882 3883 3884 3885 3886 3887 3888 3889 3890 3891 3892 3893 3894 3895 3896 3897 3898 |
#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(~%d rows)",
pTab->zName,
pIdx ? "USING COVERING INDEX " : "",
pIdx ? pIdx->zName : "",
pTab->nRowEst
);
sqlite3VdbeAddOp4(
pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC
);
}
}
#else
|
| | | < |
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 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 = rLogSize>80 ? rLogSize-80 : 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
|
| |
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 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+)\W} \
$x all as tab idx]} {
lappend data {} $idx
} elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
$x all as tab idx]} {
lappend data $tab $idx
} elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
lappend data $tab *
}
}
return $data
}
# Perform tests on the like optimization.
|
| | | |
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
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
...
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 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 {
|
|
|
|
|
|
|
|
|
|
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
...
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 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 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+)\W} \
$x all as tab idx]} {
lappend data $tab $idx
} elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
lappend data $tab *
}
}
return $data
}
|
| | |
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 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+)\W} \
$x all as tab idx]} {
lappend data $tab $idx
} elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
lappend data $tab *
}
}
return $data
}
|
| | |
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 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
|
| | | | |
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
|