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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
fe1874321ba31cec9ae65387920c33d8 |
User & Date: | drh 2016-06-08 18:07:21.900 |
References
2019-05-04
| ||
16:59 | • New ticket [5c6955204c] Incorrect result on a table scan of a partial index. (artifact: d567b0d26b user: drh) | |
Context
2016-06-10
| ||
22:49 | Enhance "PRAGMA table_info" to that it provides information about eponymous virtual tables. (check-in: 53a1e5d513 user: drh tags: trunk) | |
2016-06-08
| ||
18:07 | Prefer to use partial indexes for full table scans when that is possible. (check-in: fe1874321b 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: f48a4ad33e user: drh tags: trunk) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
3430 3431 3432 3433 3434 3435 3436 | /* 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 | | | > | | 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 |