/ Check-in [dee20409]
Login

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

Overview
Comment:Fix an sqlite3_stmt_status() problem caused by recent changs on this branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental-costs
Files: files | file ages | folders
SHA1: dee204092421a239f9f60ab83c3a5b3e24d1baea
User & Date: dan 2014-04-26 20:21:14
Context
2014-04-28
08:49
Fix an error in estimating of the number of rows visited by a range scan. check-in: d491de62 user: dan tags: experimental-costs
2014-04-26
20:21
Fix an sqlite3_stmt_status() problem caused by recent changs on this branch. check-in: dee20409 user: dan tags: experimental-costs
2014-04-25
20:22
If the user provides likelihood() data for a WHERE clause term used as part of an index key, have the planner use it when calculating the expected number of rows visited by the loop. check-in: c51efaa5 user: dan tags: experimental-costs
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

4143
4144
4145
4146
4147
4148
4149
4150

4151
4152
4153
4154
4155
4156
4157
4158
4159
4160
4161
4162
4163
4164
....
5221
5222
5223
5224
5225
5226
5227
5228
5229
5230

5231
5232
5233
5234
5235
5236
5237
      if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){
        if( iCol>=0 && pProbe->onError==OE_None ){
          pNew->wsFlags |= WHERE_UNQ_WANTED;
        }else{
          pNew->wsFlags |= WHERE_ONEROW;
        }
      }


    }else if( eOp & (WO_GT|WO_GE) ){
      testcase( eOp & WO_GT );
      testcase( eOp & WO_GE );
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT;
      pBtm = pTerm;
      pTop = 0;
    }else if( (eOp & WO_ISNULL)==0 ){
      assert( eOp & (WO_LT|WO_LE) );
      testcase( eOp & WO_LT );
      testcase( eOp & WO_LE );
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT;
      pTop = pTerm;
      pBtm = (pNew->wsFlags & WHERE_BTM_LIMIT)!=0 ?
                     pNew->aLTerm[pNew->nLTerm-2] : 0;
................................................................................
            ** cost to N*log(N)*log(Y/X).  The log(Y/X) term is computed
            ** by rScale.
            ** TODO: Should the sorting cost get a small multiplier to help
            ** discourage the use of sorting and encourage the use of index
            ** scans instead?
            */
            LogEst rScale, rSortCost;
            assert( nOrderBy>0 );
            rScale = sqlite3LogEst((nOrderBy-isOrdered)*100/nOrderBy) - 66;
            rSortCost = nRowEst + estLog(nRowEst) + rScale;

            /* TUNING: The cost of implementing DISTINCT using a B-TREE is
            ** also N*log(N) but it has a larger constant of proportionality.
            ** Multiply by 3.0. */
            if( pWInfo->wctrlFlags & WHERE_WANT_DISTINCT ){
              rSortCost += 16;
            }
            WHERETRACE(0x002,







|
>






|







 







|

|
>







4143
4144
4145
4146
4147
4148
4149
4150
4151
4152
4153
4154
4155
4156
4157
4158
4159
4160
4161
4162
4163
4164
4165
....
5222
5223
5224
5225
5226
5227
5228
5229
5230
5231
5232
5233
5234
5235
5236
5237
5238
5239
      if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){
        if( iCol>=0 && pProbe->onError==OE_None ){
          pNew->wsFlags |= WHERE_UNQ_WANTED;
        }else{
          pNew->wsFlags |= WHERE_ONEROW;
        }
      }
    }else if( eOp & WO_ISNULL ){
      pNew->wsFlags |= WHERE_COLUMN_NULL;
    }else if( eOp & (WO_GT|WO_GE) ){
      testcase( eOp & WO_GT );
      testcase( eOp & WO_GE );
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT;
      pBtm = pTerm;
      pTop = 0;
    }else{
      assert( eOp & (WO_LT|WO_LE) );
      testcase( eOp & WO_LT );
      testcase( eOp & WO_LE );
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT;
      pTop = pTerm;
      pBtm = (pNew->wsFlags & WHERE_BTM_LIMIT)!=0 ?
                     pNew->aLTerm[pNew->nLTerm-2] : 0;
................................................................................
            ** cost to N*log(N)*log(Y/X).  The log(Y/X) term is computed
            ** by rScale.
            ** TODO: Should the sorting cost get a small multiplier to help
            ** discourage the use of sorting and encourage the use of index
            ** scans instead?
            */
            LogEst rScale, rSortCost;
            assert( nOrderBy>0 && 66==sqlite3LogEst(100) );
            rScale = sqlite3LogEst((nOrderBy-isOrdered)*100/nOrderBy) - 66;
            rSortCost = nRowEst + estLog(nRowEst) + rScale + 16;

            /* TUNING: The cost of implementing DISTINCT using a B-TREE is
            ** also N*log(N) but it has a larger constant of proportionality.
            ** Multiply by 3.0. */
            if( pWInfo->wctrlFlags & WHERE_WANT_DISTINCT ){
              rSortCost += 16;
            }
            WHERETRACE(0x002,

Added test/cost.test.









































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
# 2014-04-26
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# 

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix cost


do_execsql_test 1.1 {
  CREATE TABLE t3(id INTEGER PRIMARY KEY, b NOT NULL);
  CREATE TABLE t4(c, d, e);
  CREATE UNIQUE INDEX i3 ON t3(b);
  CREATE UNIQUE INDEX i4 ON t4(c, d);
}
do_eqp_test 1.2 {
  SELECT e FROM t3, t4 WHERE b=c ORDER BY b, d;
} {
  0 0 0 {SCAN TABLE t3 USING COVERING INDEX i3} 
  0 1 1 {SEARCH TABLE t4 USING INDEX i4 (c=?)}
}


do_execsql_test 2.1 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a);
}

# It is better to use an index for ORDER BY than sort externally, even 
# if the index is a non-covering index.
do_eqp_test 2.2 {
  SELECT * FROM t1 ORDER BY a;
} {
  0 0 0 {SCAN TABLE t1 USING INDEX i1}
}

do_execsql_test 3.1 {
  CREATE TABLE t5(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
  CREATE INDEX t5b ON t5(b);
  CREATE INDEX t5c ON t5(c);
  CREATE INDEX t5d ON t5(d);
  CREATE INDEX t5e ON t5(e);
  CREATE INDEX t5f ON t5(f);
  CREATE INDEX t5g ON t5(g);
}

do_eqp_test 3.2 {
  SELECT a FROM t5 
  WHERE b IS NULL OR c IS NULL OR d IS NULL 
  ORDER BY a;
} {
  0 0 0 {SEARCH TABLE t5 USING INDEX t5b (b=?)} 
  0 0 0 {SEARCH TABLE t5 USING INDEX t5c (c=?)} 
  0 0 0 {SEARCH TABLE t5 USING INDEX t5d (d=?)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}



finish_test

Changes to test/orderby5.test.

76
77
78
79
80
81
82

83
84
85
86
87
88
89
90
91
92
93
94
  INSERT INTO sqlite_stat1 VALUES('t1','t1bc','1000000 10 9');
  INSERT INTO sqlite_stat1 VALUES('t2','t2bc','100 10 5');
  ANALYZE sqlite_master;

  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 a=0 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 {
  EXPLAIN QUERY PLAN







>


|

<







76
77
78
79
80
81
82
83
84
85
86
87

88
89
90
91
92
93
94
  INSERT INTO sqlite_stat1 VALUES('t1','t1bc','1000000 10 9');
  INSERT INTO sqlite_stat1 VALUES('t2','t2bc','100 10 5');
  ANALYZE sqlite_master;

  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 {
  EXPLAIN QUERY PLAN