Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the ability to disable the covering-index-scan optimization at compile-time, start-time, or at run-time. Add test cases to check this configurability. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | fullscan-covering-index |
Files: | files | file ages | folders |
SHA1: |
ccb8ecc30c8e6c7760131250297c2e45 |
User & Date: | drh 2012-09-17 20:44:46.604 |
Context
2012-09-17
| ||
21:24 | Make sure the WHERE_IDX_ONLY flag is not set on query plans that will not be using an index. (Closed-Leaf check-in: 698b2a2800 user: drh tags: fullscan-covering-index) | |
20:44 | Add the ability to disable the covering-index-scan optimization at compile-time, start-time, or at run-time. Add test cases to check this configurability. (check-in: ccb8ecc30c user: drh tags: fullscan-covering-index) | |
19:26 | Merge recent trunk changes into the fullscan-covering-index branch. (check-in: 1c0bf0305c user: drh tags: fullscan-covering-index) | |
Changes
Changes to src/global.c.
︙ | ︙ | |||
129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 | }; #endif #ifndef SQLITE_USE_URI # define SQLITE_USE_URI 0 #endif /* ** The following singleton contains the global configuration for ** the SQLite library. */ SQLITE_WSD struct Sqlite3Config sqlite3Config = { SQLITE_DEFAULT_MEMSTATUS, /* bMemstat */ 1, /* bCoreMutex */ SQLITE_THREADSAFE==1, /* bFullMutex */ SQLITE_USE_URI, /* bOpenUri */ 0x7ffffffe, /* mxStrlen */ 128, /* szLookaside */ 500, /* nLookaside */ {0,0,0,0,0,0,0,0}, /* m */ {0,0,0,0,0,0,0,0,0}, /* mutex */ {0,0,0,0,0,0,0,0,0,0,0,0,0},/* pcache2 */ (void*)0, /* pHeap */ | > > > > > | 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 | }; #endif #ifndef SQLITE_USE_URI # define SQLITE_USE_URI 0 #endif #ifndef SQLITE_ALLOW_COVERING_INDEX_SCAN # define SQLITE_ALLOW_COVERING_INDEX_SCAN 1 #endif /* ** The following singleton contains the global configuration for ** the SQLite library. */ SQLITE_WSD struct Sqlite3Config sqlite3Config = { SQLITE_DEFAULT_MEMSTATUS, /* bMemstat */ 1, /* bCoreMutex */ SQLITE_THREADSAFE==1, /* bFullMutex */ SQLITE_USE_URI, /* bOpenUri */ SQLITE_ALLOW_COVERING_INDEX_SCAN, /* bUseCis */ 0x7ffffffe, /* mxStrlen */ 128, /* szLookaside */ 500, /* nLookaside */ {0,0,0,0,0,0,0,0}, /* m */ {0,0,0,0,0,0,0,0,0}, /* mutex */ {0,0,0,0,0,0,0,0,0,0,0,0,0},/* pcache2 */ (void*)0, /* pHeap */ |
︙ | ︙ |
Changes to src/main.c.
︙ | ︙ | |||
470 471 472 473 474 475 476 477 478 479 480 481 482 483 | break; } case SQLITE_CONFIG_URI: { sqlite3GlobalConfig.bOpenUri = va_arg(ap, int); break; } default: { rc = SQLITE_ERROR; break; } } va_end(ap); | > > > > > | 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 | break; } case SQLITE_CONFIG_URI: { sqlite3GlobalConfig.bOpenUri = va_arg(ap, int); break; } case SQLITE_CONFIG_COVERING_INDEX_SCAN: { sqlite3GlobalConfig.bUseCis = va_arg(ap, int); break; } default: { rc = SQLITE_ERROR; break; } } va_end(ap); |
︙ | ︙ |
Changes to src/sqlite.h.in.
︙ | ︙ | |||
1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 | ** specified as part of [ATTACH] commands are interpreted as URIs, regardless ** of whether or not the [SQLITE_OPEN_URI] flag is set when the database ** connection is opened. If it is globally disabled, filenames are ** only interpreted as URIs if the SQLITE_OPEN_URI flag is set when the ** database connection is opened. By default, URI handling is globally ** disabled. The default value may be changed by compiling with the ** [SQLITE_USE_URI] symbol defined. ** ** [[SQLITE_CONFIG_PCACHE]] [[SQLITE_CONFIG_GETPCACHE]] ** <dt>SQLITE_CONFIG_PCACHE and SQLITE_CONFIG_GETPCACHE ** <dd> These options are obsolete and should not be used by new code. ** They are retained for backwards compatibility but are now no-ops. ** </dl> */ | > > > > > > > > > > > > | 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 | ** specified as part of [ATTACH] commands are interpreted as URIs, regardless ** of whether or not the [SQLITE_OPEN_URI] flag is set when the database ** connection is opened. If it is globally disabled, filenames are ** only interpreted as URIs if the SQLITE_OPEN_URI flag is set when the ** database connection is opened. By default, URI handling is globally ** disabled. The default value may be changed by compiling with the ** [SQLITE_USE_URI] symbol defined. ** ** [[SQLITE_CONFIG_COVERING_INDEX_SCAN]] <dt>SQLITE_CONFIG_COVERING_INDEX_SCAN ** <dd> This option taks a single integer argument which is interpreted as ** a boolean in order to enable or disable the use of covering indices for ** full table scans in the query optimizer. The default setting is determined ** by the [SQLITE_ALLOW_COVERING_INDEX_SCAN] compile-time option, or is "on" ** if that compile-time option is omitted. ** The ability to disable the use of covering indices for full table scans ** is because some incorrectly coded legacy applications might malfunction ** malfunction when the optimization is enabled. Providing the ability to ** disable the optimization allows the older, buggy application code to work ** without change even with newer versions of SQLite. ** ** [[SQLITE_CONFIG_PCACHE]] [[SQLITE_CONFIG_GETPCACHE]] ** <dt>SQLITE_CONFIG_PCACHE and SQLITE_CONFIG_GETPCACHE ** <dd> These options are obsolete and should not be used by new code. ** They are retained for backwards compatibility but are now no-ops. ** </dl> */ |
︙ | ︙ | |||
1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 | #define SQLITE_CONFIG_LOOKASIDE 13 /* int int */ #define SQLITE_CONFIG_PCACHE 14 /* no-op */ #define SQLITE_CONFIG_GETPCACHE 15 /* no-op */ #define SQLITE_CONFIG_LOG 16 /* xFunc, void* */ #define SQLITE_CONFIG_URI 17 /* int */ #define SQLITE_CONFIG_PCACHE2 18 /* sqlite3_pcache_methods2* */ #define SQLITE_CONFIG_GETPCACHE2 19 /* sqlite3_pcache_methods2* */ /* ** CAPI3REF: Database Connection Configuration Options ** ** These constants are the available integer configuration options that ** can be passed as the second argument to the [sqlite3_db_config()] interface. ** | > | 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 | #define SQLITE_CONFIG_LOOKASIDE 13 /* int int */ #define SQLITE_CONFIG_PCACHE 14 /* no-op */ #define SQLITE_CONFIG_GETPCACHE 15 /* no-op */ #define SQLITE_CONFIG_LOG 16 /* xFunc, void* */ #define SQLITE_CONFIG_URI 17 /* int */ #define SQLITE_CONFIG_PCACHE2 18 /* sqlite3_pcache_methods2* */ #define SQLITE_CONFIG_GETPCACHE2 19 /* sqlite3_pcache_methods2* */ #define SQLITE_CONFIG_COVERING_INDEX_SCAN 20 /* int */ /* ** CAPI3REF: Database Connection Configuration Options ** ** These constants are the available integer configuration options that ** can be passed as the second argument to the [sqlite3_db_config()] interface. ** |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
964 965 966 967 968 969 970 971 972 973 974 975 976 977 | */ #define SQLITE_QueryFlattener 0x01 /* Disable query flattening */ #define SQLITE_ColumnCache 0x02 /* Disable the column cache */ #define SQLITE_GroupByOrder 0x04 /* Disable GROUPBY cover of ORDERBY */ #define SQLITE_FactorOutConst 0x08 /* Disable factoring out constants */ #define SQLITE_IdxRealAsInt 0x10 /* Store REAL as INT in indices */ #define SQLITE_DistinctOpt 0x20 /* DISTINCT using indexes */ #define SQLITE_OptMask 0xff /* Mask of all disablable opts */ /* ** Possible values for the sqlite.magic field. ** The numbers are obtained at random and have no special meaning, other ** than being distinct from one another. */ | > | 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 | */ #define SQLITE_QueryFlattener 0x01 /* Disable query flattening */ #define SQLITE_ColumnCache 0x02 /* Disable the column cache */ #define SQLITE_GroupByOrder 0x04 /* Disable GROUPBY cover of ORDERBY */ #define SQLITE_FactorOutConst 0x08 /* Disable factoring out constants */ #define SQLITE_IdxRealAsInt 0x10 /* Store REAL as INT in indices */ #define SQLITE_DistinctOpt 0x20 /* DISTINCT using indexes */ #define SQLITE_CoverIdxScan 0x40 /* Disable covering index scans */ #define SQLITE_OptMask 0xff /* Mask of all disablable opts */ /* ** Possible values for the sqlite.magic field. ** The numbers are obtained at random and have no special meaning, other ** than being distinct from one another. */ |
︙ | ︙ | |||
2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 | ** This structure also contains some state information. */ struct Sqlite3Config { int bMemstat; /* True to enable memory status */ int bCoreMutex; /* True to enable core mutexing */ int bFullMutex; /* True to enable full mutexing */ int bOpenUri; /* True to interpret filenames as URIs */ int mxStrlen; /* Maximum string length */ int szLookaside; /* Default lookaside buffer size */ int nLookaside; /* Default lookaside buffer count */ sqlite3_mem_methods m; /* Low-level memory allocation interface */ sqlite3_mutex_methods mutex; /* Low-level mutex interface */ sqlite3_pcache_methods2 pcache2; /* Low-level page-cache interface */ void *pHeap; /* Heap storage space */ | > | 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 | ** This structure also contains some state information. */ struct Sqlite3Config { int bMemstat; /* True to enable memory status */ int bCoreMutex; /* True to enable core mutexing */ int bFullMutex; /* True to enable full mutexing */ int bOpenUri; /* True to interpret filenames as URIs */ int bUseCis; /* Use covering indices for full-scans */ int mxStrlen; /* Maximum string length */ int szLookaside; /* Default lookaside buffer size */ int nLookaside; /* Default lookaside buffer count */ sqlite3_mem_methods m; /* Low-level memory allocation interface */ sqlite3_mutex_methods mutex; /* Low-level mutex interface */ sqlite3_pcache_methods2 pcache2; /* Low-level page-cache interface */ void *pHeap; /* Heap storage space */ |
︙ | ︙ |
Changes to src/test1.c.
︙ | ︙ | |||
5936 5937 5938 5939 5940 5941 5942 5943 5944 5945 5946 5947 5948 5949 | { "all", SQLITE_OptMask }, { "query-flattener", SQLITE_QueryFlattener }, { "column-cache", SQLITE_ColumnCache }, { "groupby-order", SQLITE_GroupByOrder }, { "factor-constants", SQLITE_FactorOutConst }, { "real-as-int", SQLITE_IdxRealAsInt }, { "distinct-opt", SQLITE_DistinctOpt }, }; if( objc!=4 ){ Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN"); return TCL_ERROR; } if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR; | > | 5936 5937 5938 5939 5940 5941 5942 5943 5944 5945 5946 5947 5948 5949 5950 | { "all", SQLITE_OptMask }, { "query-flattener", SQLITE_QueryFlattener }, { "column-cache", SQLITE_ColumnCache }, { "groupby-order", SQLITE_GroupByOrder }, { "factor-constants", SQLITE_FactorOutConst }, { "real-as-int", SQLITE_IdxRealAsInt }, { "distinct-opt", SQLITE_DistinctOpt }, { "cover-idx-scan", SQLITE_CoverIdxScan }, }; if( objc!=4 ){ Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN"); return TCL_ERROR; } if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR; |
︙ | ︙ |
Changes to src/test_malloc.c.
︙ | ︙ | |||
1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 | } rc = sqlite3_config(SQLITE_CONFIG_URI, bOpenUri); Tcl_SetResult(interp, (char *)sqlite3TestErrorName(rc), TCL_VOLATILE); return TCL_OK; } /* ** Usage: sqlite3_dump_memsys3 FILENAME ** sqlite3_dump_memsys5 FILENAME ** ** Write a summary of unfreed memsys3 allocations to FILENAME. */ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 | } rc = sqlite3_config(SQLITE_CONFIG_URI, bOpenUri); Tcl_SetResult(interp, (char *)sqlite3TestErrorName(rc), TCL_VOLATILE); return TCL_OK; } /* ** Usage: sqlite3_config_cis BOOLEAN ** ** Enables or disables the use of the covering-index scan optimization. ** SQLITE_CONFIG_COVERING_INDEX_SCAN. */ static int test_config_cis( void * clientData, Tcl_Interp *interp, int objc, Tcl_Obj *CONST objv[] ){ int rc; int bUseCis; if( objc!=2 ){ Tcl_WrongNumArgs(interp, 1, objv, "BOOL"); return TCL_ERROR; } if( Tcl_GetBooleanFromObj(interp, objv[1], &bUseCis) ){ return TCL_ERROR; } rc = sqlite3_config(SQLITE_CONFIG_COVERING_INDEX_SCAN, bUseCis); Tcl_SetResult(interp, (char *)sqlite3TestErrorName(rc), TCL_VOLATILE); return TCL_OK; } /* ** Usage: sqlite3_dump_memsys3 FILENAME ** sqlite3_dump_memsys5 FILENAME ** ** Write a summary of unfreed memsys3 allocations to FILENAME. */ |
︙ | ︙ | |||
1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 | { "sqlite3_db_status", test_db_status ,0 }, { "install_malloc_faultsim", test_install_malloc_faultsim ,0 }, { "sqlite3_config_heap", test_config_heap ,0 }, { "sqlite3_config_memstatus", test_config_memstatus ,0 }, { "sqlite3_config_lookaside", test_config_lookaside ,0 }, { "sqlite3_config_error", test_config_error ,0 }, { "sqlite3_config_uri", test_config_uri ,0 }, { "sqlite3_db_config_lookaside",test_db_config_lookaside ,0 }, { "sqlite3_dump_memsys3", test_dump_memsys3 ,3 }, { "sqlite3_dump_memsys5", test_dump_memsys3 ,5 }, { "sqlite3_install_memsys3", test_install_memsys3 ,0 }, { "sqlite3_memdebug_vfs_oom_test", test_vfs_oom_test ,0 }, }; int i; | > | 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 | { "sqlite3_db_status", test_db_status ,0 }, { "install_malloc_faultsim", test_install_malloc_faultsim ,0 }, { "sqlite3_config_heap", test_config_heap ,0 }, { "sqlite3_config_memstatus", test_config_memstatus ,0 }, { "sqlite3_config_lookaside", test_config_lookaside ,0 }, { "sqlite3_config_error", test_config_error ,0 }, { "sqlite3_config_uri", test_config_uri ,0 }, { "sqlite3_config_cis", test_config_cis ,0 }, { "sqlite3_db_config_lookaside",test_db_config_lookaside ,0 }, { "sqlite3_dump_memsys3", test_dump_memsys3 ,3 }, { "sqlite3_dump_memsys5", test_dump_memsys3 ,5 }, { "sqlite3_install_memsys3", test_install_memsys3 ,0 }, { "sqlite3_memdebug_vfs_oom_test", test_vfs_oom_test ,0 }, }; int i; |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
3197 3198 3199 3200 3201 3202 3203 3204 3205 3206 3207 3208 | ** The ANALYZE command and the sqlite_stat1 and sqlite_stat3 tables do ** not give us data on the relative sizes of table and index records. ** So this computation assumes table records are about twice as big ** as index records */ if( wsFlags==WHERE_IDX_ONLY && (pWC->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 ){ /* This index is not useful for indexing, but it is a covering index. ** A full-scan of the index might be a little faster than a full-scan ** of the table, so give this case a cost slightly less than a table ** scan. */ | > > > > | | 3197 3198 3199 3200 3201 3202 3203 3204 3205 3206 3207 3208 3209 3210 3211 3212 3213 3214 3215 3216 3217 3218 3219 3220 | ** The ANALYZE command and the sqlite_stat1 and sqlite_stat3 tables do ** not give us data on the relative sizes of table and index records. ** So this computation assumes table records are about twice as big ** as index records */ if( wsFlags==WHERE_IDX_ONLY && (pWC->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 && sqlite3GlobalConfig.bUseCis #ifndef SQLITE_OMIT_BUILTIN_TEST && (pParse->db->flags & SQLITE_CoverIdxScan)==0 #endif ){ /* This index is not useful for indexing, but it is a covering index. ** A full-scan of the index might be a little faster than a full-scan ** of the table, so give this case a cost slightly less than a table ** scan. */ cost = aiRowEst[0]*3 + pProbe->nColumn; wsFlags |= WHERE_COVER_SCAN|WHERE_COLUMN_RANGE; }else if( (wsFlags & WHERE_NOT_FULLSCAN)==0 ){ /* The cost of a full table scan is a number of move operations equal ** to the number of rows in the table. ** ** We add an additional 4x penalty to full table scans. This causes ** the cost function to err on the side of choosing an index over |
︙ | ︙ |
Added test/coveridxscan.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 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 | # 2012 September 17 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # Tests for the optimization which attempts to use a covering index # for a full-table scan (under the theory that the index will be smaller # and require less I/O and hence will run faster.) # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix coveridxscan do_test 1.1 { db eval { CREATE TABLE t1(a,b,c); INSERT INTO t1 VALUES(5,4,3), (4,8,2), (3,2,1); CREATE INDEX t1ab ON t1(a,b); CREATE INDEX t1b ON t1(b); SELECT a FROM t1; } # covering index used for the scan, hence values are increasing } {3 4 5} do_test 1.2 { db eval { SELECT a, c FROM t1; } # There is no covering index, hence the values are in rowid order } {5 3 4 2 3 1} do_test 1.3 { db eval { SELECT b FROM t1; } # Choice of two indices: use the one with fewest columns } {2 4 8} do_test 2.1 { optimization_control db cover-idx-scan 0 db eval {SELECT a FROM t1} # With the optimization turned off, output in rowid order } {5 4 3} do_test 2.2 { db eval {SELECT a, c FROM t1} } {5 3 4 2 3 1} do_test 2.3 { db eval {SELECT b FROM t1} } {4 8 2} db close sqlite3_shutdown sqlite3_config_cis 0 sqlite3 db test.db do_test 3.1 { db eval {SELECT a FROM t1} # With the optimization configured off, output in rowid order } {5 4 3} do_test 3.2 { db eval {SELECT a, c FROM t1} } {5 3 4 2 3 1} do_test 3.3 { db eval {SELECT b FROM t1} } {4 8 2} db close sqlite3_shutdown sqlite3_config_cis 1 sqlite3 db test.db # The CIS optimization is enabled again. Covering indices are once again # used for all table scans. do_test 4.1 { db eval {SELECT a FROM t1} } {3 4 5} do_test 4.2 { db eval {SELECT a, c FROM t1} } {5 3 4 2 3 1} do_test 4.3 { db eval {SELECT b FROM t1} } {2 4 8} finish_test |
Changes to test/eqp.test.
︙ | ︙ | |||
475 476 477 478 479 480 481 | } # EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM # t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows) # 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 # USING TEMP B-TREE (UNION) det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" { | | | 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 | } # EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM # t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows) # 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 # USING TEMP B-TREE (UNION) det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" { 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4 (~1000000 rows)} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} } # EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM # t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING # COVERING INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows) |
︙ | ︙ |