Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not reduce the number of rows scanned at all for "IS NOT NULL" constraints. Fix a bug in calculating the number of rows visited by scans of partial indicies. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | experimental-costs |
| Files: | files | file ages | folders |
| SHA1: |
a8ae93f0cffa116df0ba34d46a53f49d |
| User & Date: | dan 2014-04-28 20:11:20 |
Context
|
2014-04-29
| ||
| 12:01 | Fix a test case so that it updates sqlite_stat1 consistently. check-in: 2dc5a0b5 user: dan tags: experimental-costs | |
|
2014-04-28
| ||
| 20:11 | Do not reduce the number of rows scanned at all for "IS NOT NULL" constraints. Fix a bug in calculating the number of rows visited by scans of partial indicies. check-in: a8ae93f0 user: dan tags: experimental-costs | |
| 19:34 | The trunk assumes that an open range constraint on an indexed term (col>?) term matches 1/4 of the indexed rows, and that a closed constraint (col BETWEEN ? AND ?) matches 1/64. Change this branch to do the same. check-in: 4047ac75 user: dan tags: experimental-costs | |
Changes
Changes to src/where.c.
1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 .... 2149 2150 2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 .... 4442 4443 4444 4445 4446 4447 4448 4449 4450 4451 4452 4453 4454 4455 |
** has a likelihood of 0.50, and any other term a likelihood of 0.25.
*/
static LogEst whereRangeAdjust(WhereTerm *pTerm, LogEst nNew){
LogEst nRet = nNew;
if( pTerm ){
if( pTerm->truthProb<=0 ){
nRet += pTerm->truthProb;
}else if( pTerm->wtFlags & TERM_VNULL ){
nRet -= 10; assert( 10==sqlite3LogEst(2) );
}else{
nRet -= 20; assert( 20==sqlite3LogEst(4) );
}
}
return nRet;
}
/*
................................................................................
}
}
#else
UNUSED_PARAMETER(pParse);
UNUSED_PARAMETER(pBuilder);
#endif
assert( pLower || pUpper );
nNew = whereRangeAdjust(pLower, nOut);
nNew = whereRangeAdjust(pUpper, nNew);
/* TUNING: If there is both an upper and lower limit, assume the range is
** reduced by an additional 75%. This means that, by default, an open-ended
** range query (e.g. col > ?) is assumed to match 1/4 of the rows in the
** index. While a closed range (e.g. col BETWEEN ? AND ?) is estimated to
** match 1/64 of the index. */
if( pLower && pUpper ) nNew -= 20;
nOut -= (pLower!=0) + (pUpper!=0);
if( nNew<10 ) nNew = 10;
if( nNew<nOut ) nOut = nNew;
pLoop->nOut = (LogEst)nOut;
return rc;
}
................................................................................
/* Loop over all indices
*/
for(; rc==SQLITE_OK && pProbe; pProbe=pProbe->pNext, iSortIdx++){
if( pProbe->pPartIdxWhere!=0
&& !whereUsablePartialIndex(pNew->iTab, pWC, pProbe->pPartIdxWhere) ){
continue; /* Partial index inappropriate for this query */
}
pNew->u.btree.nEq = 0;
pNew->u.btree.nSkip = 0;
pNew->nLTerm = 0;
pNew->iSortIdx = 0;
pNew->rSetup = 0;
pNew->prereq = mExtra;
pNew->nOut = rSize;
|
| < < > > > > |
1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 .... 2147 2148 2149 2150 2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 .... 4443 4444 4445 4446 4447 4448 4449 4450 4451 4452 4453 4454 4455 4456 4457 |
** has a likelihood of 0.50, and any other term a likelihood of 0.25.
*/
static LogEst whereRangeAdjust(WhereTerm *pTerm, LogEst nNew){
LogEst nRet = nNew;
if( pTerm ){
if( pTerm->truthProb<=0 ){
nRet += pTerm->truthProb;
}else if( (pTerm->wtFlags & TERM_VNULL)==0 ){
nRet -= 20; assert( 20==sqlite3LogEst(4) );
}
}
return nRet;
}
/*
................................................................................
}
}
#else
UNUSED_PARAMETER(pParse);
UNUSED_PARAMETER(pBuilder);
#endif
assert( pLower || pUpper );
assert( pUpper==0 || (pUpper->wtFlags & TERM_VNULL)==0 );
nNew = whereRangeAdjust(pLower, nOut);
nNew = whereRangeAdjust(pUpper, nNew);
/* TUNING: If there is both an upper and lower limit, assume the range is
** reduced by an additional 75%. This means that, by default, an open-ended
** range query (e.g. col > ?) is assumed to match 1/4 of the rows in the
** index. While a closed range (e.g. col BETWEEN ? AND ?) is estimated to
** match 1/64 of the index. */
if( pLower && pUpper ) nNew -= 20;
nOut -= (pLower!=0) + (pUpper!=0);
if( nNew<10 ) nNew = 10;
if( nNew<nOut ) nOut = nNew;
pLoop->nOut = (LogEst)nOut;
return rc;
}
................................................................................
/* Loop over all indices
*/
for(; rc==SQLITE_OK && pProbe; pProbe=pProbe->pNext, iSortIdx++){
if( pProbe->pPartIdxWhere!=0
&& !whereUsablePartialIndex(pNew->iTab, pWC, pProbe->pPartIdxWhere) ){
continue; /* Partial index inappropriate for this query */
}
rSize = pProbe->aiRowLogEst[0];
pNew->u.btree.nEq = 0;
pNew->u.btree.nSkip = 0;
pNew->nLTerm = 0;
pNew->iSortIdx = 0;
pNew->rSetup = 0;
pNew->prereq = mExtra;
pNew->nOut = rSize;
|
Changes to test/cost.test.
84 85 86 87 88 89 90 91 92 93 94 95 |
}
do_eqp_test 4.3 {
SELECT * FROM t1 WHERE likelihood(a=?, 0.067) AND b BETWEEN ? AND ?;
} {
0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b>? AND b<?)}
}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
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 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 |
}
do_eqp_test 4.3 {
SELECT * FROM t1 WHERE likelihood(a=?, 0.067) AND b BETWEEN ? AND ?;
} {
0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b>? AND b<?)}
}
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 5.1 {
CREATE TABLE t2(x, y);
CREATE INDEX t2i1 ON t2(x);
}
do_eqp_test 5.2 {
SELECT * FROM t2 ORDER BY x, y;
} {}
#exit
# TODO: Check this one out!!
# set sqlite_where_trace 0xfff
do_eqp_test 5.3 {
SELECT * FROM t2 WHERE x BETWEEN ? AND ? ORDER BY rowid;
} {}
#exit
# where7.test, where8.test:
#
do_execsql_test 6.1 {
CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c);
CREATE INDEX t3i1 ON t3(b);
CREATE INDEX t3i2 ON t3(c);
}
#set sqlite_where_trace 0xfff
# eqp.test
do_eqp_test 6.2 {
SELECT a FROM t3 WHERE (b BETWEEN 2 AND 4) OR c=100 ORDER BY a
} {
}
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 7.1 {
CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
CREATE INDEX t1b ON t1(b);
CREATE INDEX t1c ON t1(c);
CREATE INDEX t1d ON t1(d);
CREATE INDEX t1e ON t1(e);
CREATE INDEX t1f ON t1(f);
CREATE INDEX t1g ON t1(g);
}
do_eqp_test 7.2 {
SELECT a FROM t1
WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
ORDER BY a
} {
}
#set sqlite_where_trace 0xfff
do_eqp_test 7.3 {
SELECT rowid FROM t1
WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
OR (b NOT NULL AND c IS NULL AND d NOT NULL)
OR (b NOT NULL AND c NOT NULL AND d IS NULL)
} {}
#exit
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 8.1 {
CREATE TABLE composer(
cid INTEGER PRIMARY KEY,
cname TEXT
);
CREATE TABLE album(
aid INTEGER PRIMARY KEY,
aname TEXT
);
CREATE TABLE track(
tid INTEGER PRIMARY KEY,
cid INTEGER REFERENCES composer,
aid INTEGER REFERENCES album,
title TEXT
);
CREATE INDEX track_i1 ON track(cid);
CREATE INDEX track_i2 ON track(aid);
}
do_eqp_test 8.2 {
SELECT DISTINCT aname
FROM album, composer, track
WHERE cname LIKE '%bach%'
AND unlikely(composer.cid=track.cid)
AND unlikely(album.aid=track.aid);
} {
}
finish_test
|
Changes to test/index6.test.
141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 |
# Queries use partial indices as appropriate times.
#
do_test index6-2.1 {
execsql {
CREATE TABLE t2(a,b);
INSERT INTO t2(a,b) SELECT value, value FROM nums WHERE value<1000;
UPDATE t2 SET a=NULL WHERE b%5==0;
CREATE INDEX t2a1 ON t2(a) WHERE a IS NOT NULL;
SELECT count(*) FROM t2 WHERE a IS NOT NULL;
}
} {800}
do_test index6-2.2 {
execsql {
EXPLAIN QUERY PLAN
SELECT * FROM t2 WHERE a=5;
}
} {/.* TABLE t2 USING INDEX t2a1 .*/}
ifcapable stat4||stat3 {
do_test index6-2.3stat4 {
execsql {
EXPLAIN QUERY PLAN
SELECT * FROM t2 WHERE a IS NOT NULL;
}
} {/.* TABLE t2 USING INDEX t2a1 .*/}
} else {
|
| | > |
141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 |
# Queries use partial indices as appropriate times.
#
do_test index6-2.1 {
execsql {
CREATE TABLE t2(a,b);
INSERT INTO t2(a,b) SELECT value, value FROM nums WHERE value<1000;
UPDATE t2 SET a=NULL WHERE b%2==0;
CREATE INDEX t2a1 ON t2(a) WHERE a IS NOT NULL;
SELECT count(*) FROM t2 WHERE a IS NOT NULL;
}
} {500}
do_test index6-2.2 {
execsql {
EXPLAIN QUERY PLAN
SELECT * FROM t2 WHERE a=5;
}
} {/.* TABLE t2 USING INDEX t2a1 .*/}
ifcapable stat4||stat3 {
execsql ANALYZE
do_test index6-2.3stat4 {
execsql {
EXPLAIN QUERY PLAN
SELECT * FROM t2 WHERE a IS NOT NULL;
}
} {/.* TABLE t2 USING INDEX t2a1 .*/}
} else {
|