Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | In cases where stat4 data is available but cannot be used because the rhs of a range constraint is too complex a expression, fall back to using the default estimates for number of rows scanned. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
e06dc6f0c35f87c44292c71677111b74 |
| User & Date: | dan 2014-08-25 18:29:38 |
Context
|
2014-08-25
| ||
| 20:11 | Allow CAST expressions and unary "+" operators to be used in the DEFAULT argument of an ALTER TABLE ADD COLUMN and to be understand on the RHS of range constraints interpreted by STAT3/4. This involves a rewrite of the implementation of the CAST operator. (check-in: 91d8a8d0 user: drh tags: trunk) | |
| 18:29 | In cases where stat4 data is available but cannot be used because the rhs of a range constraint is too complex a expression, fall back to using the default estimates for number of rows scanned. (check-in: e06dc6f0 user: dan tags: trunk) | |
| 11:33 | Remove the pager_lookup() function since it is redundant with sqlite3PagerLookup(). (check-in: 54164ce4 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
....
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
|
rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk);
if( rc==SQLITE_OK && bOk ){
tRowcnt iNew;
whereKeyStats(pParse, p, pRec, 0, a);
iNew = a[0] + ((pLower->eOperator & WO_GT) ? a[1] : 0);
if( iNew>iLower ) iLower = iNew;
nOut--;
}
}
/* If possible, improve on the iUpper estimate using ($P:$U). */
if( pUpper ){
int bOk; /* True if value is extracted from pExpr */
Expr *pExpr = pUpper->pExpr->pRight;
................................................................................
rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk);
if( rc==SQLITE_OK && bOk ){
tRowcnt iNew;
whereKeyStats(pParse, p, pRec, 1, a);
iNew = a[0] + ((pUpper->eOperator & WO_LE) ? a[1] : 0);
if( iNew<iUpper ) iUpper = iNew;
nOut--;
}
}
pBuilder->pRec = pRec;
if( rc==SQLITE_OK ){
if( iUpper>iLower ){
nNew = sqlite3LogEst(iUpper - iLower);
}else{
nNew = 10; assert( 10==sqlite3LogEst(2) );
}
if( nNew<nOut ){
nOut = nNew;
}
pLoop->nOut = (LogEst)nOut;
WHERETRACE(0x10, ("range scan regions: %u..%u est=%d\n",
(u32)iLower, (u32)iUpper, nOut));
return SQLITE_OK;
}
}else{
int bDone = 0;
rc = whereRangeSkipScanEst(pParse, pLower, pUpper, pLoop, &bDone);
if( bDone ) return rc;
}
}
#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
|
>
>
<
<
<
>
|
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
....
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
|
rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk);
if( rc==SQLITE_OK && bOk ){
tRowcnt iNew;
whereKeyStats(pParse, p, pRec, 0, a);
iNew = a[0] + ((pLower->eOperator & WO_GT) ? a[1] : 0);
if( iNew>iLower ) iLower = iNew;
nOut--;
pLower = 0;
}
}
/* If possible, improve on the iUpper estimate using ($P:$U). */
if( pUpper ){
int bOk; /* True if value is extracted from pExpr */
Expr *pExpr = pUpper->pExpr->pRight;
................................................................................
rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk);
if( rc==SQLITE_OK && bOk ){
tRowcnt iNew;
whereKeyStats(pParse, p, pRec, 1, a);
iNew = a[0] + ((pUpper->eOperator & WO_LE) ? a[1] : 0);
if( iNew<iUpper ) iUpper = iNew;
nOut--;
pUpper = 0;
}
}
pBuilder->pRec = pRec;
if( rc==SQLITE_OK ){
if( iUpper>iLower ){
nNew = sqlite3LogEst(iUpper - iLower);
}else{
nNew = 10; assert( 10==sqlite3LogEst(2) );
}
if( nNew<nOut ){
nOut = nNew;
}
WHERETRACE(0x10, ("range scan regions: %u..%u est=%d\n",
(u32)iLower, (u32)iUpper, nOut));
}
}else{
int bDone = 0;
rc = whereRangeSkipScanEst(pParse, pLower, pUpper, pLoop, &bDone);
if( bDone ) return rc;
}
}
#else
UNUSED_PARAMETER(pParse);
UNUSED_PARAMETER(pBuilder);
assert( pLower || pUpper );
#endif
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
|
Changes to test/analyze9.test.
1083 1084 1085 1086 1087 1088 1089 1090 1091 |
2 "d=0 AND a='z' AND b='n' AND e<100" {/*t5e (e<?)*/}
3 "d=0 AND e<300" {/*t5d (d=?)*/}
4 "d=0 AND e<200" {/*t5e (e<?)*/}
} {
do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp
}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 |
2 "d=0 AND a='z' AND b='n' AND e<100" {/*t5e (e<?)*/}
3 "d=0 AND e<300" {/*t5d (d=?)*/}
4 "d=0 AND e<200" {/*t5e (e<?)*/}
} {
do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp
}
#-------------------------------------------------------------------------
# Test that if stat4 data is available but cannot be used because the
# rhs of a range constraint is a complex expression, the default estimates
# are used instead.
ifcapable stat4&&cte {
do_execsql_test 25.1 {
CREATE TABLE t6(a, b);
WITH ints(i,j) AS (
SELECT 1,1 UNION ALL SELECT i+1,j+1 FROM ints WHERE i<100
) INSERT INTO t6 SELECT * FROM ints;
CREATE INDEX aa ON t6(a);
CREATE INDEX bb ON t6(b);
ANALYZE;
}
# Term (b<?) is estimated at 25%. Better than (a<30) but not as
# good as (a<20).
do_eqp_test 25.2.1 { SELECT * FROM t6 WHERE a<30 AND b<? } {
0 0 0 {SEARCH TABLE t6 USING INDEX bb (b<?)}
}
do_eqp_test 25.2.2 { SELECT * FROM t6 WHERE a<20 AND b<? } {
0 0 0 {SEARCH TABLE t6 USING INDEX aa (a<?)}
}
# Term (b BETWEEN ? AND ?) is estimated at 1/64.
do_eqp_test 25.3.1 {
SELECT * FROM t6 WHERE a BETWEEN 5 AND 10 AND b BETWEEN ? AND ?
} {
0 0 0 {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)}
}
# Term (b BETWEEN ? AND 60) is estimated to return roughly 15 rows -
# 60 from (b<=60) multiplied by 0.25 for the b>=? term. Better than
# (a<20) but not as good as (a<10).
do_eqp_test 25.4.1 {
SELECT * FROM t6 WHERE a < 10 AND (b BETWEEN ? AND 60)
} {
0 0 0 {SEARCH TABLE t6 USING INDEX aa (a<?)}
}
do_eqp_test 25.4.2 {
SELECT * FROM t6 WHERE a < 20 AND (b BETWEEN ? AND 60)
} {
0 0 0 {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)}
}
}
finish_test
|