Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Prefer to use partial indexes for full table scans when that is possible. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
fe1874321ba31cec9ae65387920c33d8 |
| User & Date: | drh 2016-06-08 18:07:21 |
References
|
2019-05-04
| ||
| 16:59 | • New ticket [5c695520] Incorrect result on a table scan of a partial index. (artifact: d567b0d2 user: drh) | |
Context
|
2016-06-10
| ||
| 22:49 | Enhance "PRAGMA table_info" to that it provides information about eponymous virtual tables. (check-in: 53a1e5d5 user: drh tags: trunk) | |
|
2016-06-08
| ||
| 18:07 | Prefer to use partial indexes for full table scans when that is possible. (check-in: fe187432 user: drh tags: trunk) | |
| 14:04 | Add the "dbhash.exe" utility program that computes a SHA1 hash over the invariant content of an SQLite database file. Free space in the file, the page size, auto_vacuum status, text encoding, and so forth do not change the hash. Only the content matters. (check-in: f48a4ad3 user: drh tags: trunk) | |
Changes
Changes to src/build.c.
3430 3431 3432 3433 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443 3444 3445 3446 3447 |
/* 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) );
}
|
| | > | |
3430 3431 3432 3433 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443 3444 3445 3446 3447 3448 |
/* 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 half the number of rows in the table
** for a partial index. But do not let the estimate drop below 10. */
a[0] = pIdx->pTable->nRowLogEst;
if( pIdx->pPartIdxWhere!=0 ) a[0] -= 10; assert( 10==sqlite3LogEst(2) );
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) );
}
|
Changes to src/where.c.
2757 2758 2759 2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 2770 |
m = pSrc->colUsed & ~columnsInIndex(pProbe);
pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;
}
/* Full scan via index */
if( b
|| !HasRowid(pTab)
|| ( m==0
&& pProbe->bUnordered==0
&& (pProbe->szIdxRow<pTab->szTabRow)
&& (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
&& sqlite3GlobalConfig.bUseCis
&& OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
)
|
> |
2757 2758 2759 2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 2770 2771 |
m = pSrc->colUsed & ~columnsInIndex(pProbe);
pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;
}
/* Full scan via index */
if( b
|| !HasRowid(pTab)
|| pProbe->pPartIdxWhere!=0
|| ( m==0
&& pProbe->bUnordered==0
&& (pProbe->szIdxRow<pTab->szTabRow)
&& (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
&& sqlite3GlobalConfig.bUseCis
&& OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
)
|
Changes to test/index6.test.
371 372 373 374 375 376 377 378 379 |
do_execsql_test index6-10.3 {
SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC;
} {9 5}
do_execsql_test index6-10.3eqp {
EXPLAIN QUERY PLAN
SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC;
} {~/USING INDEX t10x/}
finish_test
|
> > > > > > > > > > > |
371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 |
do_execsql_test index6-10.3 {
SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC;
} {9 5}
do_execsql_test index6-10.3eqp {
EXPLAIN QUERY PLAN
SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC;
} {~/USING INDEX t10x/}
# A partial index will be used for a full table scan, where possible
do_execsql_test index6-11.1 {
CREATE TABLE t11(a,b,c);
CREATE INDEX t11x ON t11(a) WHERE b<>99;
EXPLAIN QUERY PLAN SELECT a FROM t11 WHERE b<>99;
} {/USING INDEX t11x/}
do_execsql_test index6-11.2 {
EXPLAIN QUERY PLAN SELECT a FROM t11 WHERE b<>99 AND c<>98;
} {/USING INDEX t11x/}
finish_test
|