Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Test that the default values used when sqlite_stat1 data is not available are calculated correctly. Fixes for the same. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | experimental-costs |
| Files: | files | file ages | folders |
| SHA1: |
e2d42f909de85a0586389f2dc0e654f7 |
| User & Date: | dan 2014-04-29 19:01:57.481 |
Context
|
2014-04-30
| ||
| 13:19 | Fix long-standing typos in comments. (check-in: b9f91317c3 user: drh tags: experimental-costs) | |
|
2014-04-29
| ||
| 19:01 | Test that the default values used when sqlite_stat1 data is not available are calculated correctly. Fixes for the same. (check-in: e2d42f909d user: dan tags: experimental-costs) | |
| 16:46 | Merge trunk changes into this branch. (check-in: d74299f037 user: dan tags: experimental-costs) | |
Changes
Changes to src/build.c.
| ︙ | ︙ | |||
3256 3257 3258 3259 3260 3261 3262 |
** aiRowEst[N]>=1
**
** Apart from that, we have little to go on besides intuition as to
** how aiRowEst[] should be initialized. The numbers generated here
** are based on typical values found in actual indices.
*/
void sqlite3DefaultRowEst(Index *pIdx){
| < < < < < < < < < < < < < < < < | | > > > > > > > > < | > > > > | 3256 3257 3258 3259 3260 3261 3262 3263 3264 3265 3266 3267 3268 3269 3270 3271 3272 3273 3274 3275 3276 3277 3278 3279 3280 3281 3282 3283 3284 3285 3286 3287 3288 3289 3290 |
** aiRowEst[N]>=1
**
** Apart from that, we have little to go on besides intuition as to
** how aiRowEst[] should be initialized. The numbers generated here
** are based on typical values found in actual indices.
*/
void sqlite3DefaultRowEst(Index *pIdx){
/* 10, 9, 8, 7, 6 */
LogEst aVal[] = { 33, 32, 30, 28, 26 };
LogEst *a = pIdx->aiRowLogEst;
int nCopy = MIN(ArraySize(aVal), pIdx->nKeyCol);
int i;
/* Set the first entry (number of rows in the index) to the estimated
** number of rows in the table. Or 10, if the estimated number of rows
** in the table is less than that. */
a[0] = pIdx->pTable->nRowLogEst;
if( a[0]<33 ) a[0] = 33; assert( 33==sqlite3LogEst(10) );
/* Estimate that a[1] is 10, a[2] is 9, a[3] is 8, a[4] is 7, a[5] is
** 6 and each subsequent value (if any) is 5. */
memcpy(&a[1], aVal, nCopy*sizeof(LogEst));
for(i=nCopy+1; i<=pIdx->nKeyCol; i++){
a[i] = 23; assert( 23==sqlite3LogEst(5) );
}
assert( 0==sqlite3LogEst(1) );
if( pIdx->onError!=OE_None ) a[pIdx->nKeyCol] = 0;
}
/*
** This routine will drop an existing named index. This routine
** implements the DROP INDEX statement.
*/
void sqlite3DropIndex(Parse *pParse, SrcList *pName, int ifExists){
|
| ︙ | ︙ |
Changes to test/cost.test.
| ︙ | ︙ | |||
187 188 189 190 191 192 193 194 195 196 197 198 |
AND unlikely(album.aid=track.aid);
} {
0 0 2 {SCAN TABLE track}
0 1 0 {SEARCH TABLE album USING INTEGER PRIMARY KEY (rowid=?)}
0 2 1 {SEARCH TABLE composer USING INTEGER PRIMARY KEY (rowid=?)}
0 0 0 {USE TEMP B-TREE FOR DISTINCT}
}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 |
AND unlikely(album.aid=track.aid);
} {
0 0 2 {SCAN TABLE track}
0 1 0 {SEARCH TABLE album USING INTEGER PRIMARY KEY (rowid=?)}
0 2 1 {SEARCH TABLE composer USING INTEGER PRIMARY KEY (rowid=?)}
0 0 0 {USE TEMP B-TREE FOR DISTINCT}
}
#-------------------------------------------------------------------------
#
do_execsql_test 9.1 {
CREATE TABLE t1(
a,b,c,d,e, f,g,h,i,j,
k,l,m,n,o, p,q,r,s,t
);
CREATE INDEX i1 ON t1(k,l,m,n,o,p,q,r,s,t);
}
do_test 9.2 {
for {set i 0} {$i < 100} {incr i} {
execsql { INSERT INTO t1 DEFAULT VALUES }
}
execsql {
ANALYZE;
CREATE INDEX i2 ON t1(a,b,c,d,e,f,g,h,i,j);
}
} {}
set L [list a=? b=? c=? d=? e=? f=? g=? h=? i=? j=?]
foreach {tn nTerm nRow} {
1 1 10
2 2 9
3 3 8
4 4 7
5 5 6
6 6 5
7 7 5
8 8 5
9 9 5
10 10 5
} {
set w [join [lrange $L 0 [expr $nTerm-1]] " AND "]
set p1 [expr ($nRow-1) / 100.0]
set p2 [expr ($nRow+1) / 100.0]
set sql1 "SELECT * FROM t1 WHERE likelihood(k=?, $p1) AND $w"
set sql2 "SELECT * FROM t1 WHERE likelihood(k=?, $p2) AND $w"
do_eqp_test 9.3.$tn.1 $sql1 {/INDEX i1/}
do_eqp_test 9.3.$tn.2 $sql2 {/INDEX i2/}
}
finish_test
|