Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Change the weighting of binary searches on tables to 1/10th the cost of a search on an index. Change the assumed reduction in search space from a indexed range constraint from 1/3rd to 1/4th. Do not let the estimated number of rows drop below 1. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | stat2-enhancement |
Files: | files | file ages | folders |
SHA1: |
4847c6cb71423248b186ab7842b97c83 |
User & Date: | drh 2011-01-28 01:57:41.767 |
Context
2011-01-28
| ||
03:13 | Reactivate the analyze5.test script. (Closed-Leaf check-in: a2a9f6401c user: drh tags: stat2-enhancement) | |
01:57 | Change the weighting of binary searches on tables to 1/10th the cost of a search on an index. Change the assumed reduction in search space from a indexed range constraint from 1/3rd to 1/4th. Do not let the estimated number of rows drop below 1. (check-in: 4847c6cb71 user: drh tags: stat2-enhancement) | |
2011-01-24
| ||
17:46 | Restructuring and generalizing analyze5.test. The whole script is currently disabled and will need to be reenabled prior to merging with trunk. (check-in: 31fcc7067b user: drh tags: stat2-enhancement) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
2417 2418 2419 2420 2421 2422 2423 | ** value of 1 indicates that the proposed range scan is expected to visit ** approximately 1/100th (1%) of the rows selected by the nEq equality ** constraints (if any). A return value of 100 indicates that it is expected ** that the range scan will visit every row (100%) selected by the equality ** constraints. ** ** In the absence of sqlite_stat2 ANALYZE data, each range inequality | | | | | 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 | ** value of 1 indicates that the proposed range scan is expected to visit ** approximately 1/100th (1%) of the rows selected by the nEq equality ** constraints (if any). A return value of 100 indicates that it is expected ** that the range scan will visit every row (100%) selected by the equality ** constraints. ** ** In the absence of sqlite_stat2 ANALYZE data, each range inequality ** reduces the search space by 3/4ths. Hence a single constraint (x>?) ** results in a return of 25 and a range constraint (x>? AND x<?) results ** in a return of 6. */ static int whereRangeScanEst( Parse *pParse, /* Parsing & code generating context */ Index *p, /* The index containing the range-compared column; "x" */ int nEq, /* index into p->aCol[] of the range-compared column */ WhereTerm *pLower, /* Lower bound on the range. ex: "x>123" Might be NULL */ WhereTerm *pUpper, /* Upper bound on the range. ex: "x<455" Might be NULL */ |
︙ | ︙ | |||
2494 2495 2496 2497 2498 2499 2500 | #else UNUSED_PARAMETER(pParse); UNUSED_PARAMETER(p); UNUSED_PARAMETER(nEq); #endif assert( pLower || pUpper ); *piEst = 100; | | | | 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 | #else UNUSED_PARAMETER(pParse); UNUSED_PARAMETER(p); UNUSED_PARAMETER(nEq); #endif assert( pLower || pUpper ); *piEst = 100; if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ) *piEst /= 4; if( pUpper ) *piEst /= 4; return rc; } #ifdef SQLITE_ENABLE_STAT2 /* ** Estimate the number of rows that will be returned based on ** an equality constraint x=VALUE and where that VALUE occurs in |
︙ | ︙ | |||
2632 2633 2634 2635 2636 2637 2638 | sqlite3ValueFree(pVal); return rc; } #endif /* defined(SQLITE_ENABLE_STAT2) */ /* | | | | | 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 | sqlite3ValueFree(pVal); return rc; } #endif /* defined(SQLITE_ENABLE_STAT2) */ /* ** Find the best query plan for accessing a particular table. Write the ** best query plan and its cost into the WhereCost object supplied as the ** last parameter. ** ** The lowest cost plan wins. The cost is an estimate of the amount of ** CPU and disk I/O needed to process the requested result. ** Factors that influence cost include: ** ** * The estimated number of rows that will be retrieved. (The ** fewer the better.) ** ** * Whether or not sorting must occur. ** ** * Whether or not there must be separate lookups in the ** index and in the main table. ** ** If there was an INDEXED BY clause (pSrc->pIndex) attached to the table in ** the SQL statement, then this function only considers plans using the ** named index. If no such plan is found, then the returned cost is ** SQLITE_BIG_DBL. If a plan is found that uses the named index, ** then the cost is calculated in the usual way. ** ** If a NOT INDEXED clause (pSrc->notIndexed!=0) was attached to the table ** in the SELECT statement, then no indexes are considered. However, the ** selected plan may still take advantage of the built-in rowid primary key ** index. */ static void bestBtreeIndex( Parse *pParse, /* The parsing context */ WhereClause *pWC, /* The WHERE clause */ struct SrcList_item *pSrc, /* The FROM clause term to search */ Bitmask notReady, /* Mask of cursors not available for indexing */ |
︙ | ︙ | |||
2699 2700 2701 2702 2703 2704 2705 | if( pSrc->pIndex ){ /* An INDEXED BY clause specifies a particular index to use */ pIdx = pProbe = pSrc->pIndex; wsFlagMask = ~(WHERE_ROWID_EQ|WHERE_ROWID_RANGE); eqTermMask = idxEqTermMask; }else{ | | | > > | > > > | > > | 2699 2700 2701 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 2714 2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 | if( pSrc->pIndex ){ /* An INDEXED BY clause specifies a particular index to use */ pIdx = pProbe = pSrc->pIndex; wsFlagMask = ~(WHERE_ROWID_EQ|WHERE_ROWID_RANGE); eqTermMask = idxEqTermMask; }else{ /* There is no INDEXED BY clause. Create a fake Index object in local ** variable sPk to represent the rowid primary key index. Make this ** fake index the first in a chain of Index objects with all of the real ** indices to follow */ Index *pFirst; /* First of real indices on the table */ memset(&sPk, 0, sizeof(Index)); sPk.nColumn = 1; sPk.aiColumn = &aiColumnPk; sPk.aiRowEst = aiRowEstPk; sPk.onError = OE_Replace; sPk.pTable = pSrc->pTab; aiRowEstPk[0] = pSrc->pTab->nRowEst; aiRowEstPk[1] = 1; pFirst = pSrc->pTab->pIndex; if( pSrc->notIndexed==0 ){ /* The real indices of the table are only considered if the ** NOT INDEXED qualifier is omitted from the FROM clause */ sPk.pNext = pFirst; } pProbe = &sPk; wsFlagMask = ~( WHERE_COLUMN_IN|WHERE_COLUMN_EQ|WHERE_COLUMN_NULL|WHERE_COLUMN_RANGE ); eqTermMask = WO_EQ|WO_IN; pIdx = 0; } /* Loop over all indices looking for the best one to use */ for(; pProbe; pIdx=pProbe=pProbe->pNext){ const unsigned int * const aiRowEst = pProbe->aiRowEst; double cost; /* Cost of using pProbe */ double nRow; /* Estimated number of rows in result set */ int rev; /* True to scan in reverse order */ double nSearch; /* Estimated number of binary searches */ int wsFlags = 0; Bitmask used = 0; /* The following variables are populated based on the properties of ** index being evaluated. They are then used to determine the expected ** cost and number of rows returned. ** ** nEq: ** Number of equality terms that can be implemented using the index. ** In other words, the number of initial fields in the index that ** are used in == or IN or NOT NULL constraints of the WHERE clause. ** ** nInMul: ** The "in-multiplier". This is an estimate of how many seek operations ** SQLite must perform on the index in question. For example, if the ** WHERE clause is: ** ** WHERE a IN (1, 2, 3) AND b IN (4, 5, 6) |
︙ | ︙ | |||
2761 2762 2763 2764 2765 2766 2767 | ** ** If there exists a WHERE term of the form "x IN (SELECT ...)", then ** the sub-select is assumed to return 25 rows for the purposes of ** determining nInMul. ** ** bInEst: ** Set to true if there was at least one "x IN (SELECT ...)" term used | | > > | | | > | | | | | > > | | | | | | | | | 2768 2769 2770 2771 2772 2773 2774 2775 2776 2777 2778 2779 2780 2781 2782 2783 2784 2785 2786 2787 2788 2789 2790 2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 2805 2806 2807 2808 2809 2810 2811 2812 2813 2814 2815 2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832 2833 2834 2835 2836 2837 2838 2839 2840 2841 2842 | ** ** If there exists a WHERE term of the form "x IN (SELECT ...)", then ** the sub-select is assumed to return 25 rows for the purposes of ** determining nInMul. ** ** bInEst: ** Set to true if there was at least one "x IN (SELECT ...)" term used ** in determining the value of nInMul. Note that the RHS of the ** IN operator must be a SELECT, not a value list, for this variable ** to be true. ** ** estBound: ** An estimate on the amount of the table that must be searched. A ** value of 100 means the entire table is searched. Range constraints ** might reduce this to a value less than 100 to indicate that only ** a fraction of the table needs searching. In the absence of ** sqlite_stat2 ANALYZE data, a single inequality reduces the search ** space to 1/4rd its original size. So an x>? constraint reduces ** estBound to 25. Two constraints (x>? AND x<?) reduce estBound to 6. ** ** bSort: ** Boolean. True if there is an ORDER BY clause that will require an ** external sort (i.e. scanning the index being evaluated will not ** correctly order records). ** ** bLookup: ** Boolean. True if a table lookup is required for each index entry ** visited. In other words, true if this is not a covering index. ** This is always false for the rowid primary key index of a table. ** For other indexes, it is true unless all the columns of the table ** used by the SELECT statement are present in the index (such an ** index is sometimes described as a covering index). ** For example, given the index on (a, b), the second of the following ** two queries requires table b-tree lookups in order to find the value ** of column c, but the first does not because columns a and b are ** both available in the index. ** ** SELECT a, b FROM tbl WHERE a = 1; ** SELECT a, b, c FROM tbl WHERE a = 1; */ int nEq; /* Number of == or IN terms matching index */ int bInEst = 0; /* True if "x IN (SELECT...)" seen */ int nInMul = 1; /* Number of distinct equalities to lookup */ int estBound = 100; /* Estimated reduction in search space */ int nBound = 0; /* Number of range constraints seen */ int bSort = 0; /* True if external sort required */ int bLookup = 0; /* True if not a covering index */ WhereTerm *pTerm; /* A single term of the WHERE clause */ #ifdef SQLITE_ENABLE_STAT2 WhereTerm *pFirstTerm = 0; /* First term matching the index */ #endif /* Determine the values of nEq and nInMul */ for(nEq=0; nEq<pProbe->nColumn; nEq++){ int j = pProbe->aiColumn[nEq]; pTerm = findTerm(pWC, iCur, j, notReady, eqTermMask, pIdx); if( pTerm==0 ) break; wsFlags |= (WHERE_COLUMN_EQ|WHERE_ROWID_EQ); if( pTerm->eOperator & WO_IN ){ Expr *pExpr = pTerm->pExpr; wsFlags |= WHERE_COLUMN_IN; if( ExprHasProperty(pExpr, EP_xIsSelect) ){ /* "x IN (SELECT ...)": Assume the SELECT returns 25 rows */ nInMul *= 25; bInEst = 1; }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){ /* "x IN (value, value, ...)" */ nInMul *= pExpr->x.pList->nExpr; } }else if( pTerm->eOperator & WO_ISNULL ){ wsFlags |= WHERE_COLUMN_NULL; } #ifdef SQLITE_ENABLE_STAT2 if( nEq==0 && pProbe->aSample ) pFirstTerm = pTerm; #endif |
︙ | ︙ | |||
2919 2920 2921 2922 2923 2924 2925 2926 | } #endif /* SQLITE_ENABLE_STAT2 */ /* Adjust the number of rows and the cost downward to reflect rows ** that are excluded by range constraints. */ nRow = (nRow * (double)estBound) / (double)100; | > | | > | > > > > > | > > > > > > > > > > > > | > > > > > | > | 2931 2932 2933 2934 2935 2936 2937 2938 2939 2940 2941 2942 2943 2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961 2962 2963 2964 2965 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977 2978 2979 | } #endif /* SQLITE_ENABLE_STAT2 */ /* Adjust the number of rows and the cost downward to reflect rows ** that are excluded by range constraints. */ nRow = (nRow * (double)estBound) / (double)100; if( nRow<1 ) nRow = 1; /* Assume constant cost to advance from one row to the next and ** logarithmic cost to do a binary search. Hence, the initial cost ** is the number of output rows plus log2(table-size) times the ** number of binary searches. ** ** Because fan-out on tables is so much higher than the fan-out on ** indices (because table btrees contain only integer keys in non-leaf ** nodes) we weight the cost of a table binary search as 1/10th the ** cost of an index binary search. */ if( pIdx ){ if( bLookup ){ /* For an index lookup followed by a table lookup: ** nInMul index searches to find the start of each index range ** + nRow steps through the index ** + nRow table searches to lookup the table entry using the rowid */ nSearch = nInMul + nRow/10; }else{ /* For a covering index: ** nInMul binary searches to find the initial entry ** + nRow steps through the index */ nSearch = nInMul; } }else{ /* For a rowid primary key lookup: ** nInMult binary searches to find the initial entry scaled by 1/10th ** + nRow steps through the table */ nSearch = nInMul/10; } cost = nRow + nSearch*estLog(aiRowEst[0]); /* Add in the estimated cost of sorting the result. This cost is expanded ** by a fudge factor of 3.0 to account for the fact that a sorting step ** involves a write and is thus more expensive than a lookup step. */ if( bSort ){ cost += nRow*estLog(nRow)*(double)3; |
︙ | ︙ | |||
2983 2984 2985 2986 2987 2988 2989 | }else if( pTerm->eOperator & (WO_LT|WO_LE|WO_GT|WO_GE) ){ if( nSkipRange ){ /* Ignore the first nSkipRange range constraints since the index ** has already accounted for these */ nSkipRange--; }else{ /* Assume each additional range constraint reduces the result | | > > > > | 3020 3021 3022 3023 3024 3025 3026 3027 3028 3029 3030 3031 3032 3033 3034 3035 3036 3037 3038 | }else if( pTerm->eOperator & (WO_LT|WO_LE|WO_GT|WO_GE) ){ if( nSkipRange ){ /* Ignore the first nSkipRange range constraints since the index ** has already accounted for these */ nSkipRange--; }else{ /* Assume each additional range constraint reduces the result ** set size by a factor of 3. Indexed range constraints reduce ** the search space by a larger factor: 4. We make indexed range ** more selective intentionally because of the subjective ** observation that indexed range constraints really are more ** selective in practice, on average. */ nRow /= 3; } }else if( pTerm->eOperator!=WO_NOOP ){ /* Any other expression lowers the output row count by half */ nRow /= 2; } } |
︙ | ︙ |
Changes to test/analyze2.test.
︙ | ︙ | |||
238 239 240 241 242 243 244 245 246 247 | execsql { INSERT INTO t3 VALUES($str, $str) } } execsql COMMIT execsql ANALYZE } {} do_test analyze2-4.2 { execsql { SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE idx = 't3a' | > | > | 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 | execsql { INSERT INTO t3 VALUES($str, $str) } } execsql COMMIT execsql ANALYZE } {} do_test analyze2-4.2 { execsql { PRAGMA automatic_index=OFF; SELECT tbl,idx,group_concat(sample,' ') FROM sqlite_stat2 WHERE idx = 't3a' GROUP BY tbl,idx; PRAGMA automatic_index=ON; } } {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' |
︙ | ︙ | |||
404 405 406 407 408 409 410 | DELETE FROM sqlite_stat2; } sqlite3 db test.db eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND t5.a>1 AND t5.a<15 AND t6.a>1 } | | | 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 | DELETE FROM sqlite_stat2; } sqlite3 db test.db eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND t5.a>1 AND t5.a<15 AND t6.a>1 } } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} do_test analyze2-6.2.2 { db cache flush execsql ANALYZE eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND t5.a>1 AND t5.a<15 AND t6.a>1 } |
︙ | ︙ | |||
430 431 432 433 434 435 436 | DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1'; } sqlite3 db test.db eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND t5.a>1 AND t5.a<15 AND t6.a>1 } | | | | 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 | DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1'; } sqlite3 db test.db eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND t5.a>1 AND t5.a<15 AND t6.a>1 } } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} do_test analyze2-6.2.5 { execsql { PRAGMA writable_schema = 1; DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2'; } sqlite3 db test.db eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND t5.a>1 AND t5.a<15 AND t6.a>1 } } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} do_test analyze2-6.2.6 { execsql { PRAGMA writable_schema = 1; INSERT INTO sqlite_master SELECT * FROM master; } sqlite3 db test.db execsql ANALYZE |
︙ | ︙ | |||
538 539 540 541 542 543 544 | do_test analyze2-7.10 { incr_schema_cookie test.db execsql { SELECT * FROM sqlite_master } db1 eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND t5.a>1 AND t5.a<15 AND t6.a>1 } db1 | | | 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 | do_test analyze2-7.10 { incr_schema_cookie test.db execsql { SELECT * FROM sqlite_master } db1 eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND t5.a>1 AND t5.a<15 AND t6.a>1 } db1 } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}} db1 close db2 close sqlite3_enable_shared_cache $::enable_shared_cache } finish_test |
Changes to test/analyze3.test.
︙ | ︙ | |||
244 245 246 247 248 249 250 | append t [lindex {a b c d e f g h i j} [expr ($i%10)]] execsql { INSERT INTO t1 VALUES($i, $t) } } execsql COMMIT } {} do_eqp_test analyze3-2.2 { SELECT count(a) FROM t1 WHERE b LIKE 'a%' | | | 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 | append t [lindex {a b c d e f g h i j} [expr ($i%10)]] execsql { INSERT INTO t1 VALUES($i, $t) } } execsql COMMIT } {} do_eqp_test analyze3-2.2 { SELECT count(a) FROM t1 WHERE b LIKE 'a%' } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?) (~30000 rows)}} do_eqp_test analyze3-2.3 { SELECT count(a) FROM t1 WHERE b LIKE '%a' } {0 0 0 {SCAN TABLE t1 (~500000 rows)}} do_test analyze3-2.4 { sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' } } {101 0 100} |
︙ | ︙ |
Changes to test/e_createtable.test.
︙ | ︙ | |||
1375 1376 1377 1378 1379 1380 1381 | 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}} 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c" {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}} 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10" | | | 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 | 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}} 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c" {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}} 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10" {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~2 rows)}} } # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a # column definition or specified as a table constraint. In practice it # makes no difference. # # All the tests that deal with CHECK constraints below (4.11.* and |
︙ | ︙ |
Changes to test/eqp.test.
︙ | ︙ | |||
388 389 390 391 392 393 394 | # EVIDENCE-OF: R-22253-05302 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, # t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SEARCH TABLE t1 # USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2 # (~1000000 rows) do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)} det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { | | | | 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 | # EVIDENCE-OF: R-22253-05302 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, # t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SEARCH TABLE t1 # USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2 # (~1000000 rows) do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)} det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)} 0 1 1 {SCAN TABLE t2 (~1000000 rows)} } # EVIDENCE-OF: R-21040-07025 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, # t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SEARCH TABLE t1 # USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|SCAN TABLE t2 # (~1000000 rows) det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" { 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)} 0 1 0 {SCAN TABLE t2 (~1000000 rows)} } # EVIDENCE-OF: R-39007-61103 sqlite> CREATE INDEX i3 ON t1(b); # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2; # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows) # 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows) |
︙ | ︙ |
Changes to test/indexedby.test.
︙ | ︙ | |||
150 151 152 153 154 155 156 | # Test embedding an INDEXED BY in a CREATE VIEW statement. This block # also tests that nothing bad happens if an index refered to by # a CREATE VIEW statement is dropped and recreated. # do_execsql_test indexedby-5.1 { CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5; EXPLAIN QUERY PLAN SELECT * FROM v2 | | | | 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 | # Test embedding an INDEXED BY in a CREATE VIEW statement. This block # also tests that nothing bad happens if an index refered to by # a CREATE VIEW statement is dropped and recreated. # do_execsql_test indexedby-5.1 { CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5; EXPLAIN QUERY PLAN SELECT * FROM v2 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~250000 rows)}} do_execsql_test indexedby-5.2 { EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~25000 rows)}} do_test indexedby-5.3 { execsql { DROP INDEX i1 } catchsql { SELECT * FROM v2 } } {1 {no such index: i1}} do_test indexedby-5.4 { # Recreate index i1 in such a way as it cannot be used by the view query. execsql { CREATE INDEX i1 ON t1(b) } |
︙ | ︙ |
Changes to test/like.test.
︙ | ︙ | |||
703 704 705 706 707 708 709 | INSERT INTO t10 VALUES(12,12,12,12,12,12); INSERT INTO t10 VALUES(123,123,123,123,123,123); INSERT INTO t10 VALUES(234,234,234,234,234,234); INSERT INTO t10 VALUES(345,345,345,345,345,345); INSERT INTO t10 VALUES(45,45,45,45,45,45); } count { | | | | | | | | | | | | | | 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 | INSERT INTO t10 VALUES(12,12,12,12,12,12); INSERT INTO t10 VALUES(123,123,123,123,123,123); INSERT INTO t10 VALUES(234,234,234,234,234,234); INSERT INTO t10 VALUES(345,345,345,345,345,345); INSERT INTO t10 VALUES(45,45,45,45,45,45); } count { SELECT a FROM t10 WHERE b LIKE '12%' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.2 { count { SELECT a FROM t10 WHERE c LIKE '12%' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.3 { count { SELECT a FROM t10 WHERE d LIKE '12%' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.4 { count { SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.5 { count { SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a; } } {12 123 scan 3 like 0} do_test like-10.6 { count { SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.10 { execsql { CREATE TABLE t10b( a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c NUMBER UNIQUE, d BLOB UNIQUE, e UNIQUE, f TEXT UNIQUE ); INSERT INTO t10b SELECT * FROM t10; } count { SELECT a FROM t10b WHERE b GLOB '12*' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.11 { count { SELECT a FROM t10b WHERE c GLOB '12*' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.12 { count { SELECT a FROM t10b WHERE d GLOB '12*' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.13 { count { SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.14 { count { SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a; } } {12 123 scan 3 like 0} do_test like-10.15 { count { SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY +a; } } {12 123 scan 5 like 6} } # LIKE and GLOB where the default collating sequence is not appropriate # but an index with the appropriate collating sequence exists. # |
︙ | ︙ | |||
815 816 817 818 819 820 821 | SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; } } {abc abcd nosort t11 *} do_test like-11.3 { queryplan { PRAGMA case_sensitive_like=OFF; CREATE INDEX t11b ON t11(b); | | | | | | | | | 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 | SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; } } {abc abcd nosort t11 *} do_test like-11.3 { queryplan { PRAGMA case_sensitive_like=OFF; CREATE INDEX t11b ON t11(b); SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; } } {abc abcd ABC ABCD sort {} t11b} do_test like-11.4 { queryplan { PRAGMA case_sensitive_like=ON; SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; } } {abc abcd nosort t11 *} do_test like-11.5 { queryplan { PRAGMA case_sensitive_like=OFF; DROP INDEX t11b; CREATE INDEX t11bnc ON t11(b COLLATE nocase); SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; } } {abc abcd ABC ABCD sort {} t11bnc} do_test like-11.6 { queryplan { CREATE INDEX t11bb ON t11(b COLLATE binary); SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; } } {abc abcd ABC ABCD sort {} t11bnc} do_test like-11.7 { queryplan { PRAGMA case_sensitive_like=ON; SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; } } {abc abcd sort {} t11bb} do_test like-11.8 { queryplan { PRAGMA case_sensitive_like=OFF; SELECT b FROM t11 WHERE b GLOB 'abc*' ORDER BY +a; } } {abc abcd sort {} t11bb} do_test like-11.9 { queryplan { CREATE INDEX t11cnc ON t11(c COLLATE nocase); CREATE INDEX t11cb ON t11(c COLLATE binary); SELECT c FROM t11 WHERE c LIKE 'abc%' ORDER BY +a; } } {abc abcd ABC ABCD sort {} t11cnc} do_test like-11.10 { queryplan { SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY +a; } } {abc abcd sort {} t11cb} finish_test |
Changes to test/minmax3.test.
︙ | ︙ | |||
48 49 50 51 52 53 54 55 56 57 58 59 60 61 | INSERT INTO t1 VALUES('1', 'I', 'one'); INSERT INTO t1 VALUES('2', 'IV', 'four'); INSERT INTO t1 VALUES('2', NULL, 'three'); INSERT INTO t1 VALUES('2', 'II', 'two'); INSERT INTO t1 VALUES('2', 'V', 'five'); INSERT INTO t1 VALUES('3', 'VI', 'six'); COMMIT; } } {} do_test minmax3-1.1.1 { # Linear scan. count { SELECT max(y) FROM t1 WHERE x = '2'; } } {V 5} do_test minmax3-1.1.2 { | > | 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | INSERT INTO t1 VALUES('1', 'I', 'one'); INSERT INTO t1 VALUES('2', 'IV', 'four'); INSERT INTO t1 VALUES('2', NULL, 'three'); INSERT INTO t1 VALUES('2', 'II', 'two'); INSERT INTO t1 VALUES('2', 'V', 'five'); INSERT INTO t1 VALUES('3', 'VI', 'six'); COMMIT; PRAGMA automatic_index=OFF; } } {} do_test minmax3-1.1.1 { # Linear scan. count { SELECT max(y) FROM t1 WHERE x = '2'; } } {V 5} do_test minmax3-1.1.2 { |
︙ | ︙ |
Changes to test/where3.test.
︙ | ︙ | |||
221 222 223 224 225 226 227 | CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c); CREATE INDEX t301c ON t301(c); INSERT INTO t301 VALUES(1,2,3); CREATE TABLE t302(x, y); ANALYZE; explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y; } { | | > | | 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 | CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c); CREATE INDEX t301c ON t301(c); INSERT INTO t301 VALUES(1,2,3); CREATE TABLE t302(x, y); ANALYZE; explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y; } { 0 0 0 {SCAN TABLE t302 (~1 rows)} 0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} } exit do_execsql_test where3-3.1 { explain query plan SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y; } { 0 0 1 {SCAN TABLE t302 (~1 rows)} 0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} } # Verify that when there are multiple tables in a join which must be # full table scans that the query planner attempts put the table with # the fewest number of output rows as the outer loop. # |
︙ | ︙ |
Changes to test/where9.test.
︙ | ︙ | |||
468 469 470 471 472 473 474 | # Likewise, inequalities in an AND are preferred over inequalities in # an OR. # do_execsql_test where9-5.3 { EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL) } { | | | 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 | # Likewise, inequalities in an AND are preferred over inequalities in # an OR. # do_execsql_test where9-5.3 { EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL) } { 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?) (~125000 rows)} } } ############################################################################ # Make sure OR-clauses work correctly on UPDATE and DELETE statements. do_test where9-6.2.1 { |
︙ | ︙ |