Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Change the code that collects samples for sqlite_stat2 so that the first sample taken is the (nRow/(2*SQLITE_INDEX_SAMPLES))th entry in the index, where nRow is the total number of index entries. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
cbfe6e9df39684607cbc9637e3fb3c5e |
User & Date: | dan 2009-08-20 16:11:06.000 |
Original Comment: | Change the code that collects samples for sqlite_stat2 so that the first sample taken is the (nRow/(2*SQLITE_INDEX_SAMPLES))th entry in the index, where nRow is the total number of index entries. |
Original User & Date: | dan 2009-08-20 09:11:06.000 |
Context
2009-08-20
| ||
18:14 | Continuing refinements of the range-scan optimizations in where.c. The range scores are changed from an integer 1..9 to 0..100. (check-in: f0c24b5fb8 user: drh tags: trunk) | |
16:11 | Change the code that collects samples for sqlite_stat2 so that the first sample taken is the (nRow/(2*SQLITE_INDEX_SAMPLES))th entry in the index, where nRow is the total number of index entries. (check-in: cbfe6e9df3 user: dan tags: trunk) | |
13:45 | Incremental code and comment cleanup in where.c. There is more to be done. (check-in: 4a5d9550bd user: drh tags: trunk) | |
Changes
Changes to src/analyze.c.
︙ | ︙ | |||
120 121 122 123 124 125 126 127 128 | int regTabname = iMem++; /* Register containing table name */ int regIdxname = iMem++; /* Register containing index name */ int regSampleno = iMem++; /* Register containing next sample number */ int regCol = iMem++; /* Content of a column analyzed table */ int regRec = iMem++; /* Register holding completed record */ int regTemp = iMem++; /* Temporary use register */ int regRowid = iMem++; /* Rowid for the inserted record */ #ifdef SQLITE_ENABLE_STAT2 int regTemp2 = iMem++; /* Temporary use register */ | > | | > | | 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 | int regTabname = iMem++; /* Register containing table name */ int regIdxname = iMem++; /* Register containing index name */ int regSampleno = iMem++; /* Register containing next sample number */ int regCol = iMem++; /* Content of a column analyzed table */ int regRec = iMem++; /* Register holding completed record */ int regTemp = iMem++; /* Temporary use register */ int regRowid = iMem++; /* Rowid for the inserted record */ #ifdef SQLITE_ENABLE_STAT2 int regTemp2 = iMem++; /* Temporary use register */ int regSamplerecno = iMem++; /* Index of next sample to record */ int regRecno = iMem++; /* Current sample index */ int regLast = iMem++; /* Index of last sample to record */ int regFirst = iMem++; /* Index of first sample to record */ #endif v = sqlite3GetVdbe(pParse); if( v==0 || NEVER(pTab==0) || pTab->pIndex==0 ){ /* Do no analysis for tables that have no indices */ return; } |
︙ | ︙ | |||
167 168 169 170 171 172 173 174 | /* Populate the registers containing the table and index names. */ if( pTab->pIndex==pIdx ){ sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0); } sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0); #ifdef SQLITE_ENABLE_STAT2 /* If this iteration of the loop is generating code to analyze the | > | > > > > | > > > > > > > < < < < < < < < < | | 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 | /* Populate the registers containing the table and index names. */ if( pTab->pIndex==pIdx ){ sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0); } sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0); #ifdef SQLITE_ENABLE_STAT2 /* If this iteration of the loop is generating code to analyze the ** first index in the pTab->pIndex list, then register regLast has ** not been populated. In this case populate it now. */ if( pTab->pIndex==pIdx ){ sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regSamplerecno); sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES*2-1, regTemp); sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES*2, regTemp2); sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regLast); sqlite3VdbeAddOp2(v, OP_Null, 0, regFirst); addr = sqlite3VdbeAddOp3(v, OP_Lt, regSamplerecno, 0, regLast); sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regLast, regFirst); sqlite3VdbeAddOp3(v, OP_Multiply, regLast, regTemp, regLast); sqlite3VdbeAddOp2(v, OP_AddImm, regLast, SQLITE_INDEX_SAMPLES*2-2); sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regLast, regLast); sqlite3VdbeJumpHere(v, addr); } /* Zero the regSampleno and regRecno registers. */ sqlite3VdbeAddOp2(v, OP_Integer, 0, regSampleno); sqlite3VdbeAddOp2(v, OP_Integer, 0, regRecno); sqlite3VdbeAddOp2(v, OP_Copy, regFirst, regSamplerecno); #endif /* The block of memory cells initialized here is used as follows. ** ** iMem: ** The total number of rows in the table. ** |
︙ | ︙ | |||
231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 | ** value that should be stored in the sqlite_stat2 table. If so, ** store it. */ int ne = sqlite3VdbeAddOp3(v, OP_Ne, regRecno, 0, regSamplerecno); assert( regTabname+1==regIdxname && regTabname+2==regSampleno && regTabname+3==regCol ); sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 4, regRec, "aaab", 0); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regRowid); /* Calculate new values for regSamplerecno and regSampleno. ** ** sampleno = sampleno + 1 ** samplerecno = samplerecno+(remaining records)/(remaining samples) */ sqlite3VdbeAddOp2(v, OP_AddImm, regSampleno, 1); | > | | 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 | ** value that should be stored in the sqlite_stat2 table. If so, ** store it. */ int ne = sqlite3VdbeAddOp3(v, OP_Ne, regRecno, 0, regSamplerecno); assert( regTabname+1==regIdxname && regTabname+2==regSampleno && regTabname+3==regCol ); sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL); sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 4, regRec, "aaab", 0); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regRowid); /* Calculate new values for regSamplerecno and regSampleno. ** ** sampleno = sampleno + 1 ** samplerecno = samplerecno+(remaining records)/(remaining samples) */ sqlite3VdbeAddOp2(v, OP_AddImm, regSampleno, 1); sqlite3VdbeAddOp3(v, OP_Subtract, regRecno, regLast, regTemp); sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1); sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regTemp2); sqlite3VdbeAddOp3(v, OP_Subtract, regSampleno, regTemp2, regTemp2); sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regTemp, regTemp); sqlite3VdbeAddOp3(v, OP_Add, regSamplerecno, regTemp, regSamplerecno); sqlite3VdbeJumpHere(v, ne); |
︙ | ︙ |
Changes to test/analyze2.test.
︙ | ︙ | |||
62 63 64 65 66 67 68 | for {set i 0} {$i < 1000} {incr i} { execsql { INSERT INTO t1 VALUES($i) } } execsql { ANALYZE; SELECT * FROM sqlite_stat2; } | | | | | | | | | | | > | < | 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 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 | for {set i 0} {$i < 1000} {incr i} { execsql { INSERT INTO t1 VALUES($i) } } execsql { ANALYZE; SELECT * FROM sqlite_stat2; } } [list t1 sqlite_autoindex_t1_1 0 50 \ t1 sqlite_autoindex_t1_1 1 149 \ t1 sqlite_autoindex_t1_1 2 249 \ t1 sqlite_autoindex_t1_1 3 349 \ t1 sqlite_autoindex_t1_1 4 449 \ t1 sqlite_autoindex_t1_1 5 549 \ t1 sqlite_autoindex_t1_1 6 649 \ t1 sqlite_autoindex_t1_1 7 749 \ t1 sqlite_autoindex_t1_1 8 849 \ t1 sqlite_autoindex_t1_1 9 949 \ ] do_test analyze2-1.2 { execsql { DELETE FROM t1 WHERe x>9; ANALYZE; SELECT tbl, idx, group_concat(sample, ' ') FROM sqlite_stat2; } } {t1 sqlite_autoindex_t1_1 {0 1 2 3 4 5 6 7 8 9}} do_test analyze2-1.3 { execsql { DELETE FROM t1 WHERE x>8; ANALYZE; SELECT * FROM sqlite_stat2; } } {} do_test analyze2-1.4 { execsql { DELETE FROM t1; ANALYZE; SELECT * FROM sqlite_stat2; } } {} do_test analyze2-2.1 { execsql { BEGIN; DROP TABLE t1; CREATE TABLE t1(x, y); CREATE INDEX t1_x ON t1(x); |
︙ | ︙ | |||
158 159 160 161 162 163 164 | execsql ANALYZE execsql { SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE idx = 't1_x' GROUP BY tbl,idx } | | | | | 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 | execsql ANALYZE execsql { SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE idx = 't1_x' GROUP BY tbl,idx } } {t1 t1_x {100 299 499 699 899 ajj cjj ejj gjj ijj}} do_test analyze2-3.2 { execsql { SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE idx = 't1_y' GROUP BY tbl,idx } } {t1 t1_y {100 299 499 699 899 ajj cjj ejj gjj ijj}} do_test analyze2-3.3 { eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'" } {0 0 {TABLE t1 WITH INDEX t1_y}} do_test analyze2-3.4 { eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 400 AND y BETWEEN 'a' AND 'h'" } {0 0 {TABLE t1 WITH INDEX t1_x}} do_test analyze2-3.5 { eqp "SELECT * FROM t1 WHERE x<'a' AND y>'h'" } {0 0 {TABLE t1 WITH INDEX t1_y}} do_test analyze2-3.6 { eqp "SELECT * FROM t1 WHERE x<444 AND y>'h'" } {0 0 {TABLE t1 WITH INDEX t1_y}} do_test analyze2-3.7 { eqp "SELECT * FROM t1 WHERE x<221 AND y>'g'" } {0 0 {TABLE t1 WITH INDEX t1_x}} do_test analyze2-4.1 { execsql { CREATE TABLE t3(a COLLATE nocase, b) } execsql { CREATE INDEX t3a ON t3(a) } execsql { CREATE INDEX t3b ON t3(b) } set alphabet [list A b C d E f G h I j] |
︙ | ︙ | |||
206 207 208 209 210 211 212 | do_test analyze2-4.2 { execsql { SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE idx = 't3a' GROUP BY tbl,idx } | | | | 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 | do_test analyze2-4.2 { execsql { SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE idx = 't3a' GROUP BY tbl,idx } } {t3 t3a {AfA bEj CEj dEj EEj fEj GEj hEj IEj jEj}} do_test analyze2-4.3 { execsql { SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE idx = 't3b' GROUP BY tbl,idx } } {t3 t3b {AbA CIj EIj GIj IIj bIj dIj fIj hIj jIj}} do_test analyze2-4.4 { eqp "SELECT * FROM t3 WHERE a > 'A' AND a < 'C' AND b > 'A' AND b < 'C'" } {0 0 {TABLE t3 WITH INDEX t3b}} do_test analyze2-4.5 { eqp "SELECT * FROM t3 WHERE a > 'A' AND a < 'c' AND b > 'A' AND b < 'c'" } {0 0 {TABLE t3 WITH INDEX t3a}} |
︙ | ︙ | |||
249 250 251 252 253 254 255 | do_test analyze2-5.2 { execsql { SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE tbl = 't4' GROUP BY tbl,idx } | | | 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 | do_test analyze2-5.2 { execsql { SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE tbl = 't4' GROUP BY tbl,idx } } {t4 t4x {afa bej cej dej eej fej gej hej iej jej}} do_test analyze2-5.3 { eqp "SELECT * FROM t4 WHERE x>'ccc'" } {0 0 {TABLE t4 WITH INDEX t4x}} do_test analyze2-5.4 { eqp "SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ccc' AND t42.x>'ggg'" } {0 1 {TABLE t4 AS t42 WITH INDEX t4x} 1 0 {TABLE t4 AS t41 WITH INDEX t4x}} do_test analyze2-5.5 { |
︙ | ︙ |