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 |
Timelines: | family | ancestors | descendants | both | experimental-costs |
Files: | files | file ages | folders |
SHA1: |
dee204092421a239f9f60ab83c3a5b3e |
User & Date: | dan 2014-04-26 20:21:14.140 |
Context
2014-04-28
| ||
08:49 | Fix an error in estimating of the number of rows visited by a range scan. (check-in: d491de62fc 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: dee2040924 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: c51efaa5d2 user: dan tags: experimental-costs) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
4143 4144 4145 4146 4147 4148 4149 | 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; } } | | > | | 4143 4144 4145 4146 4147 4148 4149 4150 4151 4152 4153 4154 4155 4156 4157 4158 4159 4160 4161 4162 4163 4164 4165 | 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; |
︙ | ︙ | |||
5221 5222 5223 5224 5225 5226 5227 | ** 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; | | | > | 5222 5223 5224 5225 5226 5227 5228 5229 5230 5231 5232 5233 5234 5235 5236 5237 5238 5239 | ** 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 | 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 | > | < | 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 |
︙ | ︙ |