/ Check-in [85b9beb4]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add a small cost penalty to sorting to bias the query planner in favor of plans that do not require a final sorting pass.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 85b9beb4605eb0cfe2ed063c2a1925186c9e37031f78c875e60a347cce891638
User & Date: drh 2018-07-28 21:01:55
Context
2018-07-29
18:56
In the command-line shell, always exit if realloc() fails. check-in: e390023c user: drh tags: trunk
2018-07-28
21:01
Add a small cost penalty to sorting to bias the query planner in favor of plans that do not require a final sorting pass. check-in: 85b9beb4 user: drh tags: trunk
16:24
Do not allow a column reference that is converted into a constant by the WHERE-clause constant propagation optimization to be moved to the init-time constant expression list, as the table reference will not work there. This fixes a problem found by OSSFuzz. check-in: d30b2a94 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  4052   4052           }
  4053   4053           if( isOrdered>=0 && isOrdered<nOrderBy ){
  4054   4054             if( aSortCost[isOrdered]==0 ){
  4055   4055               aSortCost[isOrdered] = whereSortingCost(
  4056   4056                   pWInfo, nRowEst, nOrderBy, isOrdered
  4057   4057               );
  4058   4058             }
  4059         -          rCost = sqlite3LogEstAdd(rUnsorted, aSortCost[isOrdered]);
         4059  +          /* TUNING:  Add a small extra penalty (5) to sorting as an
         4060  +          ** extra encouragment to the query planner to select a plan
         4061  +          ** where the rows emerge in the correct order without any sorting
         4062  +          ** required. */
         4063  +          rCost = sqlite3LogEstAdd(rUnsorted, aSortCost[isOrdered]) + 5;
  4060   4064   
  4061   4065             WHERETRACE(0x002,
  4062   4066                 ("---- sort cost=%-3d (%d/%d) increases cost %3d to %-3d\n",
  4063   4067                  aSortCost[isOrdered], (nOrderBy-isOrdered), nOrderBy, 
  4064   4068                  rUnsorted, rCost));
  4065   4069           }else{
  4066   4070             rCost = rUnsorted;

Changes to test/orderby5.test.

    79     79   
    80     80     EXPLAIN QUERY PLAN
    81     81     SELECT * FROM t2 WHERE a=0 ORDER BY a, b, c;
    82     82   } {~/B-TREE/}
    83     83   
    84     84   do_execsql_test 2.1b {
    85     85     EXPLAIN QUERY PLAN
    86         -  SELECT * FROM t1 WHERE likelihood(a=0, 0.05) ORDER BY a, b, c;
           86  +  SELECT * FROM t1 WHERE likelihood(a=0, 0.03) ORDER BY a, b, c;
    87     87   } {/B-TREE/}
    88     88   
    89     89   do_execsql_test 2.2 {
    90     90     EXPLAIN QUERY PLAN
    91     91     SELECT * FROM t1 WHERE +a=0 ORDER BY a, b, c;
    92     92   } {/B-TREE/}
    93     93   do_execsql_test 2.3 {

Changes to test/where.test.

   578    578       SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 10
   579    579     }
   580    580   } {/1 100 4 2 99 9 3 98 16 .* nosort/}
   581    581   do_test where-6.7.2 {
   582    582     cksort {
   583    583       SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 1
   584    584     }
   585         -} {1 100 4 sort}
          585  +} {1 100 4 nosort}
   586    586   ifcapable subquery {
   587    587     do_test where-6.8a {
   588    588       cksort {
   589    589         SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
   590    590       }
   591    591     } {1 100 4 2 99 9 3 98 16 nosort}
   592    592     do_test where-6.8b {