/ Check-in [cbfe6e9d]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: cbfe6e9df39684607cbc9637e3fb3c5ee6af2515
User & Date: dan 2009-08-20 16:11:06
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
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: f0c24b5f 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: cbfe6e9d user: dan tags: trunk
13:45
Incremental code and comment cleanup in where.c. There is more to be done. check-in: 4a5d9550 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

120
121
122
123
124
125
126

127
128
129
130
131


132
133
134
135
136
137
138
...
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
196
197
198
199
200
...
231
232
233
234
235
236
237

238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
  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++; /* Next sample index record number */
  int regRecno = iMem++;       /* Register next index record number */
  int regCount = iMem++;       /* Total number of records in table */


#endif

  v = sqlite3GetVdbe(pParse);
  if( v==0 || NEVER(pTab==0) || pTab->pIndex==0 ){
    /* Do no analysis for tables that have no indices */
    return;
  }
................................................................................
    /* 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 regCount has
    ** not been populated. In this case populate it now.  */
    if( pTab->pIndex==pIdx ){




      sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regCount);







    }

    /* Zero the regSampleno and regRecno registers. */
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regSampleno);
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regRecno);

    /* If there are less than INDEX_SAMPLES records in the index, then
    ** set the contents of regSampleRecno to integer value INDEX_SAMPLES.
    ** Otherwise, set it to zero. This is to ensure that if there are 
    ** less than the said number of entries in the index, no samples at
    ** all are collected.  */
    sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regSamplerecno);
    sqlite3VdbeAddOp3(v, OP_Lt, regSamplerecno, sqlite3VdbeCurrentAddr(v)+2,
        regCount);
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regSamplerecno);
#endif

    /* The block of memory cells initialized here is used as follows.
    **
    **    iMem:                
    **        The total number of rows in the table.
    **
................................................................................
        ** 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);
        sqlite3VdbeAddOp3(v, OP_Subtract, regRecno, regCount, 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);







>


|
|
<
>
>







 







>

|


>
>
>
>
|
>
>
>
>
>
>
>





<
<
<
<
<
<
<
<
<
|







 







>










|







120
121
122
123
124
125
126
127
128
129
130
131

132
133
134
135
136
137
138
139
140
...
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
...
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
  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;
  }
................................................................................
    /* 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.
    **
................................................................................
        ** 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
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
...
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
...
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
...
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
  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 0   \
        t1 sqlite_autoindex_t1_1 1 111 \
        t1 sqlite_autoindex_t1_1 2 222 \
        t1 sqlite_autoindex_t1_1 3 333 \
        t1 sqlite_autoindex_t1_1 4 444 \
        t1 sqlite_autoindex_t1_1 5 555 \
        t1 sqlite_autoindex_t1_1 6 666 \
        t1 sqlite_autoindex_t1_1 7 777 \
        t1 sqlite_autoindex_t1_1 8 888 \
        t1 sqlite_autoindex_t1_1 9 999 \
]

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>5;
    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);
................................................................................
  execsql ANALYZE
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't1_x' 
    GROUP BY tbl,idx
  }
} {t1 t1_x {0 222 444 666 888 bba ddc ffe hhg jjj}}
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 {0 222 444 666 888 bba ddc ffe hhg jjj}}

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>'h'"
} {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]
................................................................................
do_test analyze2-4.2 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't3a' 
    GROUP BY tbl,idx
  }
} {t3 t3a {AAA bbb CCC ddd EEE fff GGG hhh III jjj}}
do_test analyze2-4.3 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't3b' 
    GROUP BY tbl,idx
  }
} {t3 t3b {AAA CCC EEE GGG III bbb ddd fff hhh jjj}}

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}}
................................................................................
do_test analyze2-5.2 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE tbl = 't4' 
    GROUP BY tbl,idx
  }
} {t4 t4x {aaa bbb ccc ddd eee fff ggg hhh iii jjj}}
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 {







|
|
|
|
|
|
|
|
|
|

>









|











<







 







|







|







 







|







 







|







|







 







|







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
...
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
...
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
...
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
  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);
................................................................................
  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]
................................................................................
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}}
................................................................................
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 {