/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

4052
4053
4054
4055
4056
4057
4058




4059
4060
4061
4062
4063
4064
4065
4066
        }
        if( isOrdered>=0 && isOrdered<nOrderBy ){
          if( aSortCost[isOrdered]==0 ){
            aSortCost[isOrdered] = whereSortingCost(
                pWInfo, nRowEst, nOrderBy, isOrdered
            );
          }




          rCost = sqlite3LogEstAdd(rUnsorted, aSortCost[isOrdered]);

          WHERETRACE(0x002,
              ("---- sort cost=%-3d (%d/%d) increases cost %3d to %-3d\n",
               aSortCost[isOrdered], (nOrderBy-isOrdered), nOrderBy, 
               rUnsorted, rCost));
        }else{
          rCost = rUnsorted;







>
>
>
>
|







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

          WHERETRACE(0x002,
              ("---- sort cost=%-3d (%d/%d) increases cost %3d to %-3d\n",
               aSortCost[isOrdered], (nOrderBy-isOrdered), nOrderBy, 
               rUnsorted, rCost));
        }else{
          rCost = rUnsorted;

Changes to test/orderby5.test.

79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

  EXPLAIN QUERY PLAN
  SELECT * FROM t2 WHERE a=0 ORDER BY a, b, c;
} {~/B-TREE/}

do_execsql_test 2.1b {
  EXPLAIN QUERY PLAN
  SELECT * FROM t1 WHERE likelihood(a=0, 0.05) ORDER BY a, b, c;
} {/B-TREE/}

do_execsql_test 2.2 {
  EXPLAIN QUERY PLAN
  SELECT * FROM t1 WHERE +a=0 ORDER BY a, b, c;
} {/B-TREE/}
do_execsql_test 2.3 {







|







79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

  EXPLAIN QUERY PLAN
  SELECT * FROM t2 WHERE a=0 ORDER BY a, b, c;
} {~/B-TREE/}

do_execsql_test 2.1b {
  EXPLAIN QUERY PLAN
  SELECT * FROM t1 WHERE likelihood(a=0, 0.03) ORDER BY a, b, c;
} {/B-TREE/}

do_execsql_test 2.2 {
  EXPLAIN QUERY PLAN
  SELECT * FROM t1 WHERE +a=0 ORDER BY a, b, c;
} {/B-TREE/}
do_execsql_test 2.3 {

Changes to test/where.test.

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







|







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