Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a query planner problem that only occurs when covering-index-scan is disabled. Fix to tests whose output changed due to the new and more aggressive ORDER BY optimization. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | qp-enhancements |
| Files: | files | file ages | folders |
| SHA1: |
0f9bb90100aa304a7f28023ca4173e68 |
| User & Date: | drh 2012-10-03 12:38:19 |
Context
|
2012-10-03
| ||
| 12:56 | Update the query planner to recognize more cases where ORDER BY clauses can be optimized out. Add test cases to verify correct behavior of the ORDER BY optimization when the covering-index-scan optimization is disabled. Fix a harmless compiler warning in the TCL interface. (check-in: 956e4d7f user: drh tags: trunk) | |
| 12:38 | Fix a query planner problem that only occurs when covering-index-scan is disabled. Fix to tests whose output changed due to the new and more aggressive ORDER BY optimization. (Closed-Leaf check-in: 0f9bb901 user: drh tags: qp-enhancements) | |
| 00:25 | Further attempts to optimize out unnecessary ORDER BY clauses. (check-in: 6744d9a3 user: drh tags: qp-enhancements) | |
Changes
Changes to src/where.c.
3362 3363 3364 3365 3366 3367 3368 3369 3370 3371 3372 3373 3374 3375 |
** the cost function to err on the side of choosing an index over
** choosing a full scan. This 4x full-scan penalty is an arguable
** decision and one which we expect to revisit in the future. But
** it seems to be working well enough at the moment.
*/
pc.rCost = aiRowEst[0]*4;
pc.plan.wsFlags &= ~WHERE_IDX_ONLY;
}else{
log10N = estLog(aiRowEst[0]);
pc.rCost = pc.plan.nRow;
if( pIdx ){
if( bLookup ){
/* For an index lookup followed by a table lookup:
** nInMul index searches to find the start of each index range
|
> |
3362 3363 3364 3365 3366 3367 3368 3369 3370 3371 3372 3373 3374 3375 3376 |
** the cost function to err on the side of choosing an index over
** choosing a full scan. This 4x full-scan penalty is an arguable
** decision and one which we expect to revisit in the future. But
** it seems to be working well enough at the moment.
*/
pc.rCost = aiRowEst[0]*4;
pc.plan.wsFlags &= ~WHERE_IDX_ONLY;
if( pIdx ) pc.plan.wsFlags &= ~WHERE_ORDERED;
}else{
log10N = estLog(aiRowEst[0]);
pc.rCost = pc.plan.nRow;
if( pIdx ){
if( bLookup ){
/* For an index lookup followed by a table lookup:
** nInMul index searches to find the start of each index range
|
Changes to test/where.test.
1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 |
CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE);
INSERT INTO t8 VALUES(1,'one');
INSERT INTO t8 VALUES(4,'four');
}
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
}
} {1/4 1/1 4/4 4/1 sort}
do_test where-14.2 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
}
} {1/1 1/4 4/1 4/4 sort}
do_test where-14.3 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
}
} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.4 {
cksort {
|
| | |
1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 |
CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE);
INSERT INTO t8 VALUES(1,'one');
INSERT INTO t8 VALUES(4,'four');
}
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
}
} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.2 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
}
} {1/1 1/4 4/1 4/4 nosort}
do_test where-14.3 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
}
} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.4 {
cksort {
|