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 | SQL 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 |
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: 7e914aa9 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: e93c248c 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: eb434228 user: drh tags: query-planner-tweaks) | |
Changes
Changes to src/analyze.c.
263 263 topOfLoop = sqlite3VdbeCurrentAddr(v); 264 264 sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1); /* Increment row counter */ 265 265 266 266 for(i=0; i<nCol; i++){ 267 267 CollSeq *pColl; 268 268 sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol); 269 269 if( i==0 ){ 270 -#ifdef SQLITE_ENABLE_STAT2 271 - /* Check if the record that cursor iIdxCur points to contains a 272 - ** value that should be stored in the sqlite_stat2 table. If so, 273 - ** store it. */ 274 - int ne = sqlite3VdbeAddOp3(v, OP_Ne, iMem, 0, regNext); 275 - VdbeComment((v, "jump if not a sample")); 276 - sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrStoreStat2); 277 - sqlite3VdbeAddOp2(v, OP_Copy, regCol, regSample); 278 - sqlite3VdbeAddOp2(v, OP_AddImm, regReady, 1); 279 - 280 - /* Calculate new values for regNextSample. Where N is the number 281 - ** of rows in the table and S is the number of samples to take: 282 - ** 283 - ** nextSample = (sampleNumber*N*2 + N)/(2*S) 284 - */ 285 - sqlite3VdbeAddOp2(v, OP_AddImm, regSampleIdx, 1); 286 - sqlite3VdbeAddOp3(v, OP_Multiply, regSampleIdx, regCount2, regNext); 287 - sqlite3VdbeAddOp3(v, OP_Add, regNext, regCount, regNext); 288 - sqlite3VdbeAddOp3(v, OP_Divide, regSample2, regNext, regNext); 289 - sqlite3VdbeJumpHere(v, ne); 290 -#endif 291 - 292 270 /* Always record the very first row */ 293 271 addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1); 294 272 } 295 273 assert( pIdx->azColl!=0 ); 296 274 assert( pIdx->azColl[i]!=0 ); 297 275 pColl = sqlite3LocateCollSeq(pParse, pIdx->azColl[i]); 298 276 aChngAddr[i] = sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1, ................................................................................ 317 295 #endif 318 296 } 319 297 sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1); 320 298 sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1); 321 299 } 322 300 sqlite3DbFree(db, aChngAddr); 323 301 324 - /* End of the analysis loop. */ 302 + /* Always jump here after updating the iMem+1...iMem+1+nCol counters */ 325 303 sqlite3VdbeResolveLabel(v, endOfLoop); 304 + 305 +#ifdef SQLITE_ENABLE_STAT2 306 + /* Check if the record that cursor iIdxCur points to contains a 307 + ** value that should be stored in the sqlite_stat2 table. If so, 308 + ** store it. 309 + */ 310 + int ne = sqlite3VdbeAddOp3(v, OP_Le, regNext, 0, iMem); 311 + VdbeComment((v, "jump if not a sample")); 312 + shortJump = sqlite3VdbeAddOp1(v, OP_If, regReady); 313 + sqlite3VdbeAddOp2(v, OP_Copy, iMem+nCol+1, regSample); 314 + sqlite3VdbeJumpHere(v, shortJump); 315 + sqlite3VdbeAddOp2(v, OP_AddImm, regReady, 1); 316 + 317 + /* Calculate new values for regNextSample. Where N is the number 318 + ** of rows in the table and S is the number of samples to take: 319 + ** 320 + ** nextSample = (sampleNumber*N*2 + N)/(2*S) 321 + */ 322 + sqlite3VdbeAddOp2(v, OP_AddImm, regSampleIdx, 1); 323 + sqlite3VdbeAddOp3(v, OP_Multiply, regSampleIdx, regCount2, regNext); 324 + sqlite3VdbeAddOp3(v, OP_Add, regNext, regCount, regNext); 325 + sqlite3VdbeAddOp3(v, OP_Divide, regSample2, regNext, regNext); 326 + sqlite3VdbeJumpHere(v, ne); 327 +#endif 328 + 326 329 sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop); 327 330 sqlite3VdbeAddOp1(v, OP_Close, iIdxCur); 328 331 #ifdef SQLITE_ENABLE_STAT2 329 332 sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrStoreStat2); 330 333 #endif 331 334 332 335 /* Store the results in sqlite_stat1.
Changes to src/vdbe.c.
2464 2464 memAboutToChange(p, pOut); 2465 2465 2466 2466 /* Loop through the elements that will make up the record to figure 2467 2467 ** out how much space is required for the new record. 2468 2468 */ 2469 2469 for(pRec=pData0; pRec<=pLast; pRec++){ 2470 2470 assert( memIsValid(pRec) ); 2471 + REGISTER_TRACE((pRec-pData0)+pOp->p1, pRec); 2471 2472 if( zAffinity ){ 2472 2473 applyAffinity(pRec, zAffinity[pRec-pData0], encoding); 2473 2474 } 2474 2475 if( pRec->flags&MEM_Zero && pRec->n>0 ){ 2475 2476 sqlite3VdbeMemExpandBlob(pRec); 2476 2477 } 2477 2478 serial_type = sqlite3VdbeSerialType(pRec, file_format);
Added test/analyze8.test.
1 +# 2011 August 5 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# This file implements regression tests for SQLite library. 12 +# This file implements tests for the ANALYZE command under STAT2. 13 +# Testing the logic that computes the number of copies of each sample. 14 +# 15 + 16 +set testdir [file dirname $argv0] 17 +source $testdir/tester.tcl 18 + 19 +# There is nothing to test if ANALYZE is disable for this build. 20 +# 21 +ifcapable {!analyze||!vtab||!stat2} { 22 + finish_test 23 + return 24 +} 25 + 26 +# Generate some test data 27 +# 28 +do_test analyze8-1.0 { 29 + set x 100 30 + set y 1 31 + set ycnt 0 32 + set yinc 10 33 + execsql { 34 + CREATE TABLE t1(x,y); 35 + ANALYZE; 36 + BEGIN; 37 + CREATE INDEX t1x ON t1(x); 38 + CREATE INDEX t1y ON t1(y); 39 + } 40 + for {set i 0} {$i<20} {incr i} { 41 + for {set j 0} {$j<300} {incr j} { 42 + execsql {INSERT INTO t1 VALUES($x,$y)} 43 + incr ycnt 44 + if {$ycnt>=$yinc} {set ycnt 0; incr y} 45 + } 46 + for {set j 0} {$j<100} {incr j} { 47 + incr x 48 + execsql {INSERT INTO t1 VALUES($x,$y)} 49 + incr ycnt 50 + if {$ycnt>=$yinc} {set ycnt 0; incr y} 51 + } 52 + } 53 + execsql { 54 + COMMIT; 55 + ANALYZE; 56 + SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno; 57 + } 58 +} {200 301 400 301 600 301 800 301 1000 301 1200 301 1400 301 1600 301 1800 301 2000 301} 59 +do_test analyze8-1.1 { 60 + execsql { 61 + SELECT count(*) FROM t1 WHERE x=200; 62 + } 63 +} {301} 64 + 65 +do_test analyze8-2.0 { 66 + execsql { 67 + BEGIN; 68 + DELETE FROM t1; 69 + } 70 + for {set x 1} {$x<200} {incr x} { 71 + execsql {INSERT INTO t1 VALUES($x,$x)} 72 + } 73 + for {set i 0} {$i<200} {incr i} { 74 + execsql {INSERT INTO t1 VALUES(999,999)} 75 + } 76 + execsql { 77 + COMMIT; 78 + ANALYZE; 79 + SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno; 80 + } 81 +} {20 1 60 1 100 1 140 1 180 1 999 200 999 200 999 200 999 200 999 200} 82 +do_test analyze8-2.1 { 83 + for {set i 0} {$i<200} {incr i} { 84 + execsql {INSERT INTO t1 VALUES(0,999)} 85 + } 86 + execsql { 87 + ANALYZE; 88 + SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno; 89 + } 90 +} {0 200 0 200 0 200 10 1 70 1 130 1 190 1 999 200 999 200 999 200}