Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Bug fixes to the sample-count logic for STAT2. A few test cases added. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | query-planner-tweaks |
Files: | files | file ages | folders |
SHA1: |
e93c248c8470622944cf7f9e5af4f10c |
User & Date: | drh 2011-08-05 22:31:19.378 |
Context
2011-08-06
| ||
01:22 | Fix a compiler warning when STAT2 is off. More test cases. Fix legacy tests to deal with the new STAT2 logic. (check-in: 7e914aa999 user: drh tags: query-planner-tweaks) | |
2011-08-05
| ||
22:31 | Bug fixes to the sample-count logic for STAT2. A few test cases added. (check-in: e93c248c84 user: drh tags: query-planner-tweaks) | |
21:13 | Add a column to the sqlite_stat2 table that contains the number of entries with exactly the same key as the sample. We do not yet do anything with this extra value. Some tests in analyze2.test are failing. (check-in: eb43422827 user: drh tags: query-planner-tweaks) | |
Changes
Changes to src/analyze.c.
︙ | ︙ | |||
263 264 265 266 267 268 269 | topOfLoop = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1); /* Increment row counter */ for(i=0; i<nCol; i++){ CollSeq *pColl; sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol); if( i==0 ){ | < < < < < < < < < < < < < < < < < < < < < < | 263 264 265 266 267 268 269 270 271 272 273 274 275 276 | topOfLoop = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1); /* Increment row counter */ for(i=0; i<nCol; i++){ CollSeq *pColl; sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol); if( i==0 ){ /* Always record the very first row */ addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1); } assert( pIdx->azColl!=0 ); assert( pIdx->azColl[i]!=0 ); pColl = sqlite3LocateCollSeq(pParse, pIdx->azColl[i]); aChngAddr[i] = sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1, |
︙ | ︙ | |||
317 318 319 320 321 322 323 | #endif } sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1); sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1); } sqlite3DbFree(db, aChngAddr); | | > > > > > > > > > > > > > > > > > > > > > > > > > | 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 | #endif } sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1); sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1); } sqlite3DbFree(db, aChngAddr); /* Always jump here after updating the iMem+1...iMem+1+nCol counters */ sqlite3VdbeResolveLabel(v, endOfLoop); #ifdef SQLITE_ENABLE_STAT2 /* Check if the record that cursor iIdxCur points to contains a ** value that should be stored in the sqlite_stat2 table. If so, ** store it. */ int ne = sqlite3VdbeAddOp3(v, OP_Le, regNext, 0, iMem); VdbeComment((v, "jump if not a sample")); shortJump = sqlite3VdbeAddOp1(v, OP_If, regReady); sqlite3VdbeAddOp2(v, OP_Copy, iMem+nCol+1, regSample); sqlite3VdbeJumpHere(v, shortJump); sqlite3VdbeAddOp2(v, OP_AddImm, regReady, 1); /* Calculate new values for regNextSample. Where N is the number ** of rows in the table and S is the number of samples to take: ** ** nextSample = (sampleNumber*N*2 + N)/(2*S) */ sqlite3VdbeAddOp2(v, OP_AddImm, regSampleIdx, 1); sqlite3VdbeAddOp3(v, OP_Multiply, regSampleIdx, regCount2, regNext); sqlite3VdbeAddOp3(v, OP_Add, regNext, regCount, regNext); sqlite3VdbeAddOp3(v, OP_Divide, regSample2, regNext, regNext); sqlite3VdbeJumpHere(v, ne); #endif sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop); sqlite3VdbeAddOp1(v, OP_Close, iIdxCur); #ifdef SQLITE_ENABLE_STAT2 sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrStoreStat2); #endif /* Store the results in sqlite_stat1. |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 | memAboutToChange(p, pOut); /* Loop through the elements that will make up the record to figure ** out how much space is required for the new record. */ for(pRec=pData0; pRec<=pLast; pRec++){ assert( memIsValid(pRec) ); if( zAffinity ){ applyAffinity(pRec, zAffinity[pRec-pData0], encoding); } if( pRec->flags&MEM_Zero && pRec->n>0 ){ sqlite3VdbeMemExpandBlob(pRec); } serial_type = sqlite3VdbeSerialType(pRec, file_format); | > | 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 | memAboutToChange(p, pOut); /* Loop through the elements that will make up the record to figure ** out how much space is required for the new record. */ for(pRec=pData0; pRec<=pLast; pRec++){ assert( memIsValid(pRec) ); REGISTER_TRACE((pRec-pData0)+pOp->p1, pRec); if( zAffinity ){ applyAffinity(pRec, zAffinity[pRec-pData0], encoding); } if( pRec->flags&MEM_Zero && pRec->n>0 ){ sqlite3VdbeMemExpandBlob(pRec); } serial_type = sqlite3VdbeSerialType(pRec, file_format); |
︙ | ︙ |
Added test/analyze8.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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | # 2011 August 5 # # 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. # #*********************************************************************** # This file implements regression tests for SQLite library. # This file implements tests for the ANALYZE command under STAT2. # Testing the logic that computes the number of copies of each sample. # set testdir [file dirname $argv0] source $testdir/tester.tcl # There is nothing to test if ANALYZE is disable for this build. # ifcapable {!analyze||!vtab||!stat2} { finish_test return } # Generate some test data # do_test analyze8-1.0 { set x 100 set y 1 set ycnt 0 set yinc 10 execsql { CREATE TABLE t1(x,y); ANALYZE; BEGIN; CREATE INDEX t1x ON t1(x); CREATE INDEX t1y ON t1(y); } for {set i 0} {$i<20} {incr i} { for {set j 0} {$j<300} {incr j} { execsql {INSERT INTO t1 VALUES($x,$y)} incr ycnt if {$ycnt>=$yinc} {set ycnt 0; incr y} } for {set j 0} {$j<100} {incr j} { incr x execsql {INSERT INTO t1 VALUES($x,$y)} incr ycnt if {$ycnt>=$yinc} {set ycnt 0; incr y} } } execsql { COMMIT; ANALYZE; SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno; } } {200 301 400 301 600 301 800 301 1000 301 1200 301 1400 301 1600 301 1800 301 2000 301} do_test analyze8-1.1 { execsql { SELECT count(*) FROM t1 WHERE x=200; } } {301} do_test analyze8-2.0 { execsql { BEGIN; DELETE FROM t1; } for {set x 1} {$x<200} {incr x} { execsql {INSERT INTO t1 VALUES($x,$x)} } for {set i 0} {$i<200} {incr i} { execsql {INSERT INTO t1 VALUES(999,999)} } execsql { COMMIT; ANALYZE; SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno; } } {20 1 60 1 100 1 140 1 180 1 999 200 999 200 999 200 999 200 999 200} do_test analyze8-2.1 { for {set i 0} {$i<200} {incr i} { execsql {INSERT INTO t1 VALUES(0,999)} } execsql { ANALYZE; SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno; } } {0 200 0 200 0 200 10 1 70 1 130 1 190 1 999 200 999 200 999 200} |