/ Check-in [0f9bb901]
Login

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: 0f9bb90100aa304a7f28023ca4173e68b445e8bd
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3362   3362         ** the cost function to err on the side of choosing an index over
  3363   3363         ** choosing a full scan.  This 4x full-scan penalty is an arguable
  3364   3364         ** decision and one which we expect to revisit in the future.  But
  3365   3365         ** it seems to be working well enough at the moment.
  3366   3366         */
  3367   3367         pc.rCost = aiRowEst[0]*4;
  3368   3368         pc.plan.wsFlags &= ~WHERE_IDX_ONLY;
         3369  +      if( pIdx ) pc.plan.wsFlags &= ~WHERE_ORDERED;
  3369   3370       }else{
  3370   3371         log10N = estLog(aiRowEst[0]);
  3371   3372         pc.rCost = pc.plan.nRow;
  3372   3373         if( pIdx ){
  3373   3374           if( bLookup ){
  3374   3375             /* For an index lookup followed by a table lookup:
  3375   3376             **    nInMul index searches to find the start of each index range

Changes to test/where.test.

  1084   1084       CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE);
  1085   1085       INSERT INTO t8 VALUES(1,'one');
  1086   1086       INSERT INTO t8 VALUES(4,'four');
  1087   1087     }
  1088   1088     cksort {
  1089   1089       SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
  1090   1090     } 
  1091         -} {1/4 1/1 4/4 4/1 sort}
         1091  +} {1/4 1/1 4/4 4/1 nosort}
  1092   1092   do_test where-14.2 {
  1093   1093     cksort {
  1094   1094       SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
  1095   1095     } 
  1096         -} {1/1 1/4 4/1 4/4 sort}
         1096  +} {1/1 1/4 4/1 4/4 nosort}
  1097   1097   do_test where-14.3 {
  1098   1098     cksort {
  1099   1099       SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
  1100   1100     } 
  1101   1101   } {1/4 1/1 4/4 4/1 nosort}
  1102   1102   do_test where-14.4 {
  1103   1103     cksort {