Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Improved management of the space to hold WhereLoop.aLTerm[]. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | nextgen-query-plan-exp |
Files: | files | file ages | folders |
SHA1: |
d4141ecbea3abbe83525910684fbd89e |
User & Date: | drh 2013-06-06 23:02:03.759 |
Context
2013-06-06
| ||
23:44 | Performance improvements. (check-in: 9f8e84ab98 user: drh tags: nextgen-query-plan-exp) | |
23:02 | Improved management of the space to hold WhereLoop.aLTerm[]. (check-in: d4141ecbea user: drh tags: nextgen-query-plan-exp) | |
19:25 | Remove some commented-out code that was mistakenly left in the previous check-in. (check-in: b4a5dbad36 user: drh tags: nextgen-query-plan-exp) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
41 42 43 44 45 46 47 48 49 50 51 52 53 54 | typedef struct WhereAndInfo WhereAndInfo; typedef struct WhereLevel WhereLevel; typedef struct WhereLoop WhereLoop; typedef struct WherePath WherePath; typedef struct WhereTerm WhereTerm; typedef struct WhereLoopBuilder WhereLoopBuilder; typedef struct WhereScan WhereScan; /* ** For each nested loop in a WHERE clause implementation, the WhereInfo ** structure contains a single instance of this structure. This structure ** is intended to be private to the where.c module and should not be ** access or modified by other modules. ** | > | 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | typedef struct WhereAndInfo WhereAndInfo; typedef struct WhereLevel WhereLevel; typedef struct WhereLoop WhereLoop; typedef struct WherePath WherePath; typedef struct WhereTerm WhereTerm; typedef struct WhereLoopBuilder WhereLoopBuilder; typedef struct WhereScan WhereScan; typedef float WhereCost; /* ** For each nested loop in a WHERE clause implementation, the WhereInfo ** structure contains a single instance of this structure. This structure ** is intended to be private to the where.c module and should not be ** access or modified by other modules. ** |
︙ | ︙ | |||
94 95 96 97 98 99 100 | Bitmask prereq; /* Bitmask of other loops that must run first */ Bitmask maskSelf; /* Bitmask identifying table iTab */ #ifdef SQLITE_DEBUG char cId; /* Symbolic ID of this loop for debugging use */ #endif u8 iTab; /* Position in FROM clause of table for this loop */ u8 iSortIdx; /* Sorting index number. 0==None */ | | > | | | | > > > > | | | 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 | Bitmask prereq; /* Bitmask of other loops that must run first */ Bitmask maskSelf; /* Bitmask identifying table iTab */ #ifdef SQLITE_DEBUG char cId; /* Symbolic ID of this loop for debugging use */ #endif u8 iTab; /* Position in FROM clause of table for this loop */ u8 iSortIdx; /* Sorting index number. 0==None */ u16 nLTerm; /* Number of entries in aLTerm[] */ u16 nLSlot; /* Number of slots allocated for aLTerm[] */ u32 wsFlags; /* WHERE_* flags describing the plan */ WhereCost rSetup; /* One-time setup cost (ex: create transient index) */ WhereCost rRun; /* Cost of running each loop */ WhereCost nOut; /* Estimated number of output rows */ union { struct { /* Information for internal btree tables */ int nEq; /* Number of equality constraints */ Index *pIndex; /* Index used, or NULL */ } btree; struct { /* Information for virtual tables */ int idxNum; /* Index number */ u8 needFree; /* True if sqlite3_free(idxStr) is needed */ u8 isOrdered; /* True if satisfies ORDER BY */ u16 omitMask; /* Terms that may be omitted */ char *idxStr; /* Index identifier string */ } vtab; } u; WhereTerm **aLTerm; /* WhereTerms used */ WhereLoop *pNextLoop; /* Next WhereLoop object in the WhereClause */ WhereTerm *aLTermSpace[4]; /* Initial aLTerm[] space */ }; /* Forward declaration of methods */ static int whereLoopResize(sqlite3*, WhereLoop*, int); /* ** Each instance of this object holds a sequence of WhereLoop objects ** that implement some or all of the entire query plan. */ struct WherePath { Bitmask maskLoop; /* Bitmask of all WhereLoop objects in this path */ Bitmask revLoop; /* aLoop[]s that should be reversed for ORDER BY */ WhereCost nRow; /* Estimated number of rows generated by this path */ WhereCost rCost; /* Total cost of this path */ u8 isOrdered; /* True if this path satisfies ORDER BY */ u8 isOrderedValid; /* True if the isOrdered field is valid */ WhereLoop **aLoop; /* Array of WhereLoop objects implementing this path */ }; /* ** The query generator uses an array of instances of this structure to |
︙ | ︙ | |||
314 315 316 317 318 319 320 | */ struct WhereLoopBuilder { WhereInfo *pWInfo; /* Information about this WHERE */ WhereClause *pWC; /* WHERE clause terms */ ExprList *pOrderBy; /* ORDER BY clause */ WhereLoop *pNew; /* Template WhereLoop */ WhereLoop *pBest; /* If non-NULL, store single best loop here */ | < | 320 321 322 323 324 325 326 327 328 329 330 331 332 333 | */ struct WhereLoopBuilder { WhereInfo *pWInfo; /* Information about this WHERE */ WhereClause *pWC; /* WHERE clause terms */ ExprList *pOrderBy; /* ORDER BY clause */ WhereLoop *pNew; /* Template WhereLoop */ WhereLoop *pBest; /* If non-NULL, store single best loop here */ }; /* ** The WHERE clause processing routine has two halves. The ** first part does the start of the WHERE loop and the second ** half does the tail of the WHERE loop. An instance of ** this structure is returned by the first half and passed |
︙ | ︙ | |||
342 343 344 345 346 347 348 | int iTop; /* The very beginning of the WHERE loop */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int nLevel; /* Number of nested loop */ WhereMaskSet sMaskSet; /* Map cursor numbers to bitmasks */ WhereClause sWC; /* Decomposition of the WHERE clause */ WhereLoop *pLoops; /* List of all WhereLoop objects */ | | | | 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 | int iTop; /* The very beginning of the WHERE loop */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int nLevel; /* Number of nested loop */ WhereMaskSet sMaskSet; /* Map cursor numbers to bitmasks */ WhereClause sWC; /* Decomposition of the WHERE clause */ WhereLoop *pLoops; /* List of all WhereLoop objects */ WhereCost savedNQueryLoop; /* pParse->nQueryLoop outside the WHERE loop */ WhereCost nRowOut; /* Estimated number of output rows */ WhereLevel a[1]; /* Information about each nest loop in WHERE */ }; /* ** Bitmasks for the operators that indices are able to exploit. An ** OR-ed combination of these values can be used when searching for ** terms in the where clause. |
︙ | ︙ | |||
395 396 397 398 399 400 401 | #define WHERE_TEMP_INDEX 0x00004000 /* Uses an ephemeral index */ #define WHERE_COVER_SCAN 0x00008000 /* Full scan of a covering index */ /* ** Return the estimated number of output rows from a WHERE clause */ double sqlite3WhereOutputRowCount(WhereInfo *pWInfo){ | | | 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 | #define WHERE_TEMP_INDEX 0x00004000 /* Uses an ephemeral index */ #define WHERE_COVER_SCAN 0x00008000 /* Full scan of a covering index */ /* ** Return the estimated number of output rows from a WHERE clause */ double sqlite3WhereOutputRowCount(WhereInfo *pWInfo){ return (double)pWInfo->nRowOut; } /* ** Return one of the WHERE_DISTINCT_xxxxx values to indicate how this ** WHERE clause returns outputs for DISTINCT processing. */ int sqlite3WhereIsDistinct(WhereInfo *pWInfo){ |
︙ | ︙ | |||
1819 1820 1821 1822 1823 1824 1825 | /* ** Prepare a crude estimate of the logarithm of the input value. ** The results need not be exact. This is only used for estimating ** the total cost of performing operations with O(logN) or O(NlogN) ** complexity. Because N is just a guess, it is no great tragedy if ** logN is a little off. */ | | | | | 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 | /* ** Prepare a crude estimate of the logarithm of the input value. ** The results need not be exact. This is only used for estimating ** the total cost of performing operations with O(logN) or O(NlogN) ** complexity. Because N is just a guess, it is no great tragedy if ** logN is a little off. */ static WhereCost estLog(WhereCost N){ WhereCost logN = 1; WhereCost x = 10; while( N>x ){ logN += 1; x *= 10; } return logN; } |
︙ | ︙ | |||
1942 1943 1944 1945 1946 1947 1948 | /* Count the number of columns that will be added to the index ** and used to match WHERE clause constraints */ nColumn = 0; pTable = pSrc->pTab; pWCEnd = &pWC->a[pWC->nTerm]; pLoop = pLevel->pWLoop; idxCols = 0; | < < < | > | | | 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 | /* Count the number of columns that will be added to the index ** and used to match WHERE clause constraints */ nColumn = 0; pTable = pSrc->pTab; pWCEnd = &pWC->a[pWC->nTerm]; pLoop = pLevel->pWLoop; idxCols = 0; for(pTerm=pWC->a; pTerm<pWCEnd && pLoop->nLTerm<mxConstraint; pTerm++){ if( termCanDriveIndex(pTerm, pSrc, notReady) ){ int iCol = pTerm->u.leftColumn; Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol); testcase( iCol==BMS ); testcase( iCol==BMS-1 ); if( (idxCols & cMask)==0 ){ if( whereLoopResize(pParse->db, pLoop, nColumn+1) ) return; pLoop->aLTerm[nColumn++] = pTerm; idxCols |= cMask; } } } assert( nColumn>0 ); pLoop->u.btree.nEq = pLoop->nLTerm = nColumn; pLoop->wsFlags = WHERE_COLUMN_EQ | WHERE_IDX_ONLY | WHERE_INDEXED | WHERE_TEMP_INDEX; /* Count the number of additional columns needed to create a ** covering index. A "covering index" is an index that contains all ** columns that are needed by the query. With a covering index, the ** original table never needs to be accessed. Automatic indices must |
︙ | ︙ | |||
2114 2115 2116 2117 2118 2119 2120 | /* Allocate the sqlite3_index_info structure */ pIdxInfo = sqlite3DbMallocZero(pParse->db, sizeof(*pIdxInfo) + (sizeof(*pIdxCons) + sizeof(*pUsage))*nTerm + sizeof(*pIdxOrderBy)*nOrderBy ); if( pIdxInfo==0 ){ sqlite3ErrorMsg(pParse, "out of memory"); | < | 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 | /* Allocate the sqlite3_index_info structure */ pIdxInfo = sqlite3DbMallocZero(pParse->db, sizeof(*pIdxInfo) + (sizeof(*pIdxCons) + sizeof(*pUsage))*nTerm + sizeof(*pIdxOrderBy)*nOrderBy ); if( pIdxInfo==0 ){ sqlite3ErrorMsg(pParse, "out of memory"); return 0; } /* Initialize the structure. The sqlite3_index_info structure contains ** many fields that are declared "const" to prevent xBestIndex from ** changing them. We have to do some funky casting in order to ** initialize those fields. |
︙ | ︙ | |||
2237 2238 2239 2240 2241 2242 2243 | tRowcnt *aStat /* OUT: stats written here */ ){ tRowcnt n; IndexSample *aSample; int i, eType; int isEq = 0; i64 v; | | | 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 | tRowcnt *aStat /* OUT: stats written here */ ){ tRowcnt n; IndexSample *aSample; int i, eType; int isEq = 0; i64 v; WhereCost r, rS; assert( roundUp==0 || roundUp==1 ); assert( pIdx->nSample>0 ); if( pVal==0 ) return SQLITE_ERROR; n = pIdx->aiRowEst[0]; aSample = pIdx->aSample; eType = sqlite3_value_type(pVal); |
︙ | ︙ | |||
2455 2456 2457 2458 2459 2460 2461 | */ 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 */ | | | 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 | */ 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 */ WhereCost *pRangeDiv /* OUT: Reduce search space by this divisor */ ){ int rc = SQLITE_OK; #ifdef SQLITE_ENABLE_STAT3 if( nEq==0 && p->nSample ){ sqlite3_value *pRangeVal; |
︙ | ︙ | |||
2494 2495 2496 2497 2498 2499 2500 | iUpper = a[0]; if( (pUpper->eOperator & WO_LE)!=0 ) iUpper += a[1]; } sqlite3ValueFree(pRangeVal); } if( rc==SQLITE_OK ){ if( iUpper<=iLower ){ | | | | | | | 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 | iUpper = a[0]; if( (pUpper->eOperator & WO_LE)!=0 ) iUpper += a[1]; } sqlite3ValueFree(pRangeVal); } if( rc==SQLITE_OK ){ if( iUpper<=iLower ){ *pRangeDiv = (WhereCost)p->aiRowEst[0]; }else{ *pRangeDiv = (WhereCost)p->aiRowEst[0]/(WhereCost)(iUpper - iLower); } /*WHERETRACE(("range scan regions: %u..%u div=%g\n", (u32)iLower, (u32)iUpper, *pRangeDiv));*/ return SQLITE_OK; } } #else UNUSED_PARAMETER(pParse); UNUSED_PARAMETER(p); UNUSED_PARAMETER(nEq); #endif assert( pLower || pUpper ); *pRangeDiv = (WhereCost)1; if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ) *pRangeDiv *= (WhereCost)4; if( pUpper ) *pRangeDiv *= (WhereCost)4; return rc; } #ifdef SQLITE_ENABLE_STAT3 /* ** Estimate the number of rows that will be returned based on ** an equality constraint x=VALUE and where that VALUE occurs in |
︙ | ︙ | |||
2537 2538 2539 2540 2541 2542 2543 | ** for a UTF conversion required for comparison. The error is stored ** in the pParse structure. */ static int whereEqualScanEst( Parse *pParse, /* Parsing & code generating context */ Index *p, /* The index whose left-most column is pTerm */ Expr *pExpr, /* Expression for VALUE in the x=VALUE constraint */ | | | 2539 2540 2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 | ** for a UTF conversion required for comparison. The error is stored ** in the pParse structure. */ static int whereEqualScanEst( Parse *pParse, /* Parsing & code generating context */ Index *p, /* The index whose left-most column is pTerm */ Expr *pExpr, /* Expression for VALUE in the x=VALUE constraint */ WhereCost *pnRow /* Write the revised row estimate here */ ){ sqlite3_value *pRhs = 0; /* VALUE on right-hand side of pTerm */ u8 aff; /* Column affinity */ int rc; /* Subfunction return code */ tRowcnt a[2]; /* Statistics */ assert( p->aSample!=0 ); |
︙ | ︙ | |||
2586 2587 2588 2589 2590 2591 2592 | ** for a UTF conversion required for comparison. The error is stored ** in the pParse structure. */ static int whereInScanEst( Parse *pParse, /* Parsing & code generating context */ Index *p, /* The index whose left-most column is pTerm */ ExprList *pList, /* The value list on the RHS of "x IN (v1,v2,v3,...)" */ | | | | | 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 | ** for a UTF conversion required for comparison. The error is stored ** in the pParse structure. */ static int whereInScanEst( Parse *pParse, /* Parsing & code generating context */ Index *p, /* The index whose left-most column is pTerm */ ExprList *pList, /* The value list on the RHS of "x IN (v1,v2,v3,...)" */ WhereCost *pnRow /* Write the revised row estimate here */ ){ int rc = SQLITE_OK; /* Subfunction return code */ WhereCost nEst; /* Number of rows for a single term */ WhereCost nRowEst = (WhereCost)0; /* New estimate of the number of rows */ int i; /* Loop counter */ assert( p->aSample!=0 ); for(i=0; rc==SQLITE_OK && i<pList->nExpr; i++){ nEst = p->aiRowEst[0]; rc = whereEqualScanEst(pParse, p, pList->a[i].pExpr, &nEst); nRowEst += nEst; |
︙ | ︙ | |||
2854 2855 2856 2857 2858 2859 2860 | } /* Evaluate the equality constraints */ assert( pIdx->nColumn>=nEq ); for(j=0; j<nEq; j++){ int r1; | | | 2856 2857 2858 2859 2860 2861 2862 2863 2864 2865 2866 2867 2868 2869 2870 | } /* Evaluate the equality constraints */ assert( pIdx->nColumn>=nEq ); for(j=0; j<nEq; j++){ int r1; pTerm = pLoop->aLTerm[j]; assert( pTerm!=0 ); /* The following true for indices with redundant columns. ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */ testcase( (pTerm->wtFlags & TERM_CODED)!=0 ); testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */ r1 = codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, regBase+j); if( r1!=regBase+j ){ |
︙ | ︙ | |||
3128 3129 3130 3131 3132 3133 3134 | #ifndef SQLITE_OMIT_VIRTUALTABLE if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)!=0 ){ /* Case 1: The table is a virtual-table. Use the VFilter and VNext ** to access the data. */ int iReg; /* P3 Value for OP_VFilter */ int addrNotFound; | | | | | | 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 3148 3149 3150 3151 3152 3153 3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 3166 3167 3168 3169 3170 3171 3172 3173 3174 3175 3176 3177 3178 3179 3180 3181 3182 3183 3184 3185 3186 3187 3188 | #ifndef SQLITE_OMIT_VIRTUALTABLE if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)!=0 ){ /* Case 1: The table is a virtual-table. Use the VFilter and VNext ** to access the data. */ int iReg; /* P3 Value for OP_VFilter */ int addrNotFound; int nConstraint = pLoop->nLTerm; sqlite3ExprCachePush(pParse); iReg = sqlite3GetTempRange(pParse, nConstraint+2); addrNotFound = pLevel->addrBrk; for(j=0; j<nConstraint; j++){ int iTarget = iReg+j+2; pTerm = pLoop->aLTerm[j]; if( pTerm->eOperator & WO_IN ){ codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, iTarget); addrNotFound = pLevel->addrNxt; }else{ sqlite3ExprCode(pParse, pTerm->pExpr->pRight, iTarget); } } sqlite3VdbeAddOp2(v, OP_Integer, pLoop->u.vtab.idxNum, iReg); sqlite3VdbeAddOp2(v, OP_Integer, nConstraint, iReg+1); sqlite3VdbeAddOp4(v, OP_VFilter, iCur, addrNotFound, iReg, pLoop->u.vtab.idxStr, pLoop->u.vtab.needFree ? P4_MPRINTF : P4_STATIC); pLoop->u.vtab.needFree = 0; for(j=0; j<nConstraint && j<16; j++){ if( (pLoop->u.vtab.omitMask>>j)&1 ){ disableTerm(pLevel, pLoop->aLTerm[j]); } } pLevel->op = OP_VNext; pLevel->p1 = iCur; pLevel->p2 = sqlite3VdbeCurrentAddr(v); sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2); sqlite3ExprCachePop(pParse, 1); }else #endif /* SQLITE_OMIT_VIRTUALTABLE */ if( (pLoop->wsFlags & WHERE_IPK)!=0 && (pLoop->wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_EQ))!=0 ){ /* Case 2: We can directly reference a single row using an ** equality comparison against the ROWID field. Or ** we reference multiple rows using a "rowid IN (...)" ** construct. */ assert( pLoop->u.btree.nEq==1 ); iReleaseReg = sqlite3GetTempReg(pParse); pTerm = pLoop->aLTerm[0]; assert( pTerm!=0 ); assert( pTerm->pExpr!=0 ); assert( omitTable==0 ); testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */ iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, bRev, iReleaseReg); addrNxt = pLevel->addrNxt; sqlite3VdbeAddOp2(v, OP_MustBeInt, iRowidReg, addrNxt); |
︙ | ︙ | |||
3198 3199 3200 3201 3202 3203 3204 | int start; int memEndValue = 0; WhereTerm *pStart, *pEnd; assert( omitTable==0 ); j = 0; pStart = pEnd = 0; | | | | 3200 3201 3202 3203 3204 3205 3206 3207 3208 3209 3210 3211 3212 3213 3214 3215 | int start; int memEndValue = 0; WhereTerm *pStart, *pEnd; assert( omitTable==0 ); j = 0; pStart = pEnd = 0; if( pLoop->wsFlags & WHERE_BTM_LIMIT ) pStart = pLoop->aLTerm[j++]; if( pLoop->wsFlags & WHERE_TOP_LIMIT ) pEnd = pLoop->aLTerm[j++]; if( bRev ){ pTerm = pStart; pStart = pEnd; pEnd = pTerm; } if( pStart ){ Expr *pX; /* The expression that defines the start bound */ |
︙ | ︙ | |||
3357 3358 3359 3360 3361 3362 3363 | } /* Find any inequality constraint terms for the start and end ** of the range. */ j = nEq; if( pLoop->wsFlags & WHERE_BTM_LIMIT ){ | | | | 3359 3360 3361 3362 3363 3364 3365 3366 3367 3368 3369 3370 3371 3372 3373 3374 3375 3376 3377 | } /* Find any inequality constraint terms for the start and end ** of the range. */ j = nEq; if( pLoop->wsFlags & WHERE_BTM_LIMIT ){ pRangeStart = pLoop->aLTerm[j++]; nExtraReg = 1; } if( pLoop->wsFlags & WHERE_TOP_LIMIT ){ pRangeEnd = pLoop->aLTerm[j++]; nExtraReg = 1; } /* Generate code to evaluate all constraint terms using == or IN ** and store the values of those terms in an array of registers ** starting at regBase. */ |
︙ | ︙ | |||
3570 3571 3572 3573 3574 3575 3576 | int regRowid = 0; /* Register holding rowid */ int iLoopBody = sqlite3VdbeMakeLabel(v); /* Start of loop body */ int iRetInit; /* Address of regReturn init */ int untestedTerms = 0; /* Some terms not completely tested */ int ii; /* Loop counter */ Expr *pAndExpr = 0; /* An ".. AND (...)" expression */ | | | 3572 3573 3574 3575 3576 3577 3578 3579 3580 3581 3582 3583 3584 3585 3586 | int regRowid = 0; /* Register holding rowid */ int iLoopBody = sqlite3VdbeMakeLabel(v); /* Start of loop body */ int iRetInit; /* Address of regReturn init */ int untestedTerms = 0; /* Some terms not completely tested */ int ii; /* Loop counter */ Expr *pAndExpr = 0; /* An ".. AND (...)" expression */ pTerm = pLoop->aLTerm[0]; assert( pTerm!=0 ); assert( pTerm->eOperator & WO_OR ); assert( (pTerm->wtFlags & TERM_ORINFO)!=0 ); pOrWc = &pTerm->u.pOrInfo->wc; pLevel->op = OP_Return; pLevel->p1 = regReturn; |
︙ | ︙ | |||
3856 3857 3858 3859 3860 3861 3862 | p->u.vtab.idxNum, p->u.vtab.idxStr, p->u.vtab.omitMask); }else{ z = sqlite3_mprintf("(%d,%x)", p->u.vtab.idxNum, p->u.vtab.omitMask); } sqlite3DebugPrintf(" %-15s", z); sqlite3_free(z); } | | > | | | | > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 3858 3859 3860 3861 3862 3863 3864 3865 3866 3867 3868 3869 3870 3871 3872 3873 3874 3875 3876 3877 3878 3879 3880 3881 3882 3883 3884 3885 3886 3887 3888 3889 3890 3891 3892 3893 3894 3895 3896 3897 3898 3899 3900 3901 3902 3903 3904 3905 3906 3907 3908 3909 3910 3911 3912 3913 3914 3915 3916 3917 3918 3919 3920 3921 3922 3923 3924 3925 3926 3927 3928 3929 3930 3931 3932 3933 3934 3935 3936 3937 3938 3939 3940 3941 3942 3943 3944 3945 3946 3947 3948 3949 3950 3951 3952 3953 3954 3955 3956 3957 3958 | p->u.vtab.idxNum, p->u.vtab.idxStr, p->u.vtab.omitMask); }else{ z = sqlite3_mprintf("(%d,%x)", p->u.vtab.idxNum, p->u.vtab.omitMask); } sqlite3DebugPrintf(" %-15s", z); sqlite3_free(z); } sqlite3DebugPrintf(" fg %05x N %d", p->wsFlags, p->nLTerm); sqlite3DebugPrintf(" cost %.2g,%.2g,%.2g\n", p->prereq, p->rSetup, p->rRun, p->nOut); } #endif /* ** Convert bulk memory into a valid WhereLoop that can be passed ** to whereLoopClear harmlessly. */ static void whereLoopInit(WhereLoop *p){ p->aLTerm = p->aLTermSpace; p->nLTerm = 0; p->nLSlot = ArraySize(p->aLTermSpace); p->wsFlags = 0; } /* ** Clear the WhereLoop.u union. Leave WhereLoop.pLTerm intact. */ static void whereLoopClearUnion(sqlite3 *db, WhereLoop *p){ if( (p->wsFlags & WHERE_VIRTUALTABLE)!=0 ){ if( p->u.vtab.needFree ) sqlite3_free(p->u.vtab.idxStr); p->u.vtab.needFree = 0; p->u.vtab.idxStr = 0; }else if( (p->wsFlags & WHERE_TEMP_INDEX)!=0 && p->u.btree.pIndex!=0 ){ sqlite3DbFree(db, p->u.btree.pIndex->zColAff); sqlite3DbFree(db, p->u.btree.pIndex); p->u.btree.pIndex = 0; } } /* ** Deallocate internal memory used by a WhereLoop object */ static void whereLoopClear(sqlite3 *db, WhereLoop *p){ if( p->aLTerm!=p->aLTermSpace ) sqlite3DbFree(db, p->aLTerm); whereLoopClearUnion(db, p); whereLoopInit(p); } /* ** Increase the memory allocation for pLoop->aLTerm[] to be at least n. */ static int whereLoopResize(sqlite3 *db, WhereLoop *p, int n){ WhereTerm **paNew; if( p->nLSlot>=n ) return SQLITE_OK; n = (n+7)&~7; paNew = sqlite3DbMallocRaw(db, sizeof(p->aLTerm[0])*n); if( paNew==0 ) return SQLITE_NOMEM; memcpy(paNew, p->aLTerm, sizeof(p->aLTerm[0])*p->nLSlot); if( p->aLTerm!=p->aLTermSpace ) sqlite3DbFree(db, p->aLTerm); p->aLTerm = paNew; p->nLSlot = n; return SQLITE_OK; } /* ** Transfer content from the second pLoop into the first. */ static int whereLoopXfer(sqlite3 *db, WhereLoop *pTo, WhereLoop *pFrom){ if( whereLoopResize(db, pTo, pFrom->nLTerm) ) return SQLITE_NOMEM; whereLoopClearUnion(db, pTo); pTo->prereq = pFrom->prereq; pTo->maskSelf = pFrom->maskSelf; pTo->iTab = pFrom->iTab; pTo->iSortIdx = pFrom->iSortIdx; pTo->nLTerm = pFrom->nLTerm; pTo->rSetup = pFrom->rSetup; pTo->rRun = pFrom->rRun; pTo->nOut = pFrom->nOut; if( pTo->nLTerm ){ memcpy(pTo->aLTerm, pFrom->aLTerm, pTo->nLTerm*sizeof(pTo->aLTerm[0])); } pTo->wsFlags = pFrom->wsFlags; pTo->u = pFrom->u; if( pFrom->wsFlags & WHERE_VIRTUALTABLE ){ pFrom->u.vtab.needFree = 0; }else if( (pFrom->wsFlags & WHERE_TEMP_INDEX)!=0 && pFrom->u.btree.pIndex!=0 ){ sqlite3DbFree(db, pFrom->u.btree.pIndex->zColAff); sqlite3DbFree(db, pFrom->u.btree.pIndex); pFrom->u.btree.pIndex = 0; } return SQLITE_OK; } /* ** Delete a WhereLoop object */ static void whereLoopDelete(sqlite3 *db, WhereLoop *p){ whereLoopClear(db, p); sqlite3DbFree(db, p); } |
︙ | ︙ | |||
3930 3931 3932 3933 3934 3935 3936 | ** (2) They have the same iSortIdx. ** (3) The template has same or fewer dependencies than the current loop ** (4) The template has the same or lower cost than the current loop ** (5) The template uses more terms of the same index but has no additional ** dependencies */ static int whereLoopInsert(WhereLoopBuilder *pBuilder, WhereLoop *pTemplate){ | | < > | > < | | < < | | 3995 3996 3997 3998 3999 4000 4001 4002 4003 4004 4005 4006 4007 4008 4009 4010 4011 4012 4013 4014 4015 4016 4017 4018 4019 4020 4021 4022 4023 4024 4025 4026 4027 4028 4029 4030 4031 4032 4033 4034 4035 4036 4037 4038 4039 4040 4041 4042 4043 4044 4045 4046 4047 4048 4049 | ** (2) They have the same iSortIdx. ** (3) The template has same or fewer dependencies than the current loop ** (4) The template has the same or lower cost than the current loop ** (5) The template uses more terms of the same index but has no additional ** dependencies */ static int whereLoopInsert(WhereLoopBuilder *pBuilder, WhereLoop *pTemplate){ WhereLoop **ppPrev, *p, *pNext = 0; WhereInfo *pWInfo = pBuilder->pWInfo; sqlite3 *db = pWInfo->pParse->db; /* If pBuilder->pBest is defined, then only keep track of the single ** best WhereLoop. pBuilder->pBest->maskSelf==0 indicates that no ** prior WhereLoops have been evaluated and that the current pTemplate ** is therefore the first and hence the best and should be retained. */ if( (p = pBuilder->pBest)!=0 ){ if( p->maskSelf!=0 ){ WhereCost rCost = p->rRun + p->rSetup; WhereCost rTemplate = pTemplate->rRun + pTemplate->rSetup; if( rCost < rTemplate ){ goto whereLoopInsert_noop; } if( rCost == rTemplate && p->prereq <= pTemplate->prereq ){ goto whereLoopInsert_noop; } } whereLoopXfer(db, p, pTemplate); #if WHERETRACE_ENABLED if( sqlite3WhereTrace & 0x8 ){ sqlite3DebugPrintf("ins-best: "); whereLoopPrint(pTemplate, pWInfo->pTabList); } #endif return SQLITE_OK; } /* Search for an existing WhereLoop to overwrite, or which takes ** priority over pTemplate. */ for(ppPrev=&pWInfo->pLoops, p=*ppPrev; p; ppPrev=&p->pNextLoop, p=*ppPrev){ if( p->iTab!=pTemplate->iTab || p->iSortIdx!=pTemplate->iSortIdx ) continue; if( (p->prereq & pTemplate->prereq)==p->prereq && p->rSetup<=pTemplate->rSetup && p->rRun<=pTemplate->rRun ){ /* p is equal or better than pTemplate */ if( p->nLTerm<pTemplate->nLTerm && (p->wsFlags & WHERE_INDEXED)!=0 && (pTemplate->wsFlags & WHERE_INDEXED)!=0 && p->u.btree.pIndex==pTemplate->u.btree.pIndex && p->prereq==pTemplate->prereq ){ /* Overwrite an existing WhereLoop with an similar one that uses ** more terms of the index */ |
︙ | ︙ | |||
4015 4016 4017 4018 4019 4020 4021 | whereLoopPrint(p, pWInfo->pTabList); } sqlite3DebugPrintf("ins-new: "); whereLoopPrint(pTemplate, pWInfo->pTabList); } #endif if( p==0 ){ | | < < < | < < | < | < < < < < < | 4078 4079 4080 4081 4082 4083 4084 4085 4086 4087 4088 4089 4090 4091 4092 4093 4094 4095 4096 4097 4098 4099 4100 4101 4102 4103 | whereLoopPrint(p, pWInfo->pTabList); } sqlite3DebugPrintf("ins-new: "); whereLoopPrint(pTemplate, pWInfo->pTabList); } #endif if( p==0 ){ p = sqlite3DbMallocRaw(db, sizeof(WhereLoop)); if( p==0 ) return SQLITE_NOMEM; whereLoopInit(p); } whereLoopXfer(db, p, pTemplate); p->pNextLoop = pNext; *ppPrev = p; if( (p->wsFlags & WHERE_VIRTUALTABLE)==0 ){ Index *pIndex = p->u.btree.pIndex; if( pIndex && pIndex->tnum==0 ){ p->u.btree.pIndex = 0; } } return SQLITE_OK; /* Jump here if the insert is a no-op */ whereLoopInsert_noop: #if WHERETRACE_ENABLED if( sqlite3WhereTrace & 0x8 ){ |
︙ | ︙ | |||
4073 4074 4075 4076 4077 4078 4079 | WhereInfo *pWInfo = pBuilder->pWInfo; /* WHERE analyse context */ Parse *pParse = pWInfo->pParse; /* Parsing context */ sqlite3 *db = pParse->db; /* Database connection malloc context */ WhereLoop *pNew; /* Template WhereLoop under construction */ WhereTerm *pTerm; /* A WhereTerm under consideration */ int opMask; /* Valid operators for constraints */ WhereScan scan; /* Iterator for WHERE terms */ | > | > > > | | 4124 4125 4126 4127 4128 4129 4130 4131 4132 4133 4134 4135 4136 4137 4138 4139 4140 4141 4142 4143 4144 4145 4146 | WhereInfo *pWInfo = pBuilder->pWInfo; /* WHERE analyse context */ Parse *pParse = pWInfo->pParse; /* Parsing context */ sqlite3 *db = pParse->db; /* Database connection malloc context */ WhereLoop *pNew; /* Template WhereLoop under construction */ WhereTerm *pTerm; /* A WhereTerm under consideration */ int opMask; /* Valid operators for constraints */ WhereScan scan; /* Iterator for WHERE terms */ Bitmask saved_prereq; /* Original value of pNew->prereq */ u16 saved_nLTerm; /* Original value of pNew->nLTerm */ int saved_nEq; /* Original value of pNew->u.btree.nEq */ u32 saved_wsFlags; /* Original value of pNew->wsFlags */ WhereCost saved_nOut; /* Original value of pNew->nOut */ int iCol; /* Index of the column in the table */ int rc = SQLITE_OK; /* Return code */ tRowcnt iRowEst; /* Estimated index selectivity */ WhereCost rLogSize; /* Logarithm of table size */ WhereTerm *pTop, *pBtm; /* Top and bottom range constraints */ pNew = pBuilder->pNew; if( db->mallocFailed ) return SQLITE_NOMEM; assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 ); assert( pNew->u.btree.nEq<=pProbe->nColumn ); |
︙ | ︙ | |||
4104 4105 4106 4107 4108 4109 4110 | iRowEst = pProbe->aiRowEst[pNew->u.btree.nEq+1]; }else{ iCol = -1; iRowEst = 1; } pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol, opMask, pProbe); | > > > > | | | | | | | | | | | | | | | 4159 4160 4161 4162 4163 4164 4165 4166 4167 4168 4169 4170 4171 4172 4173 4174 4175 4176 4177 4178 4179 4180 4181 4182 4183 4184 4185 4186 4187 4188 4189 4190 4191 4192 4193 4194 4195 4196 4197 4198 4199 4200 4201 4202 4203 4204 4205 4206 4207 4208 4209 4210 4211 4212 4213 4214 4215 4216 4217 4218 4219 4220 4221 4222 4223 4224 4225 4226 4227 4228 4229 4230 4231 4232 4233 4234 4235 4236 | iRowEst = pProbe->aiRowEst[pNew->u.btree.nEq+1]; }else{ iCol = -1; iRowEst = 1; } pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol, opMask, pProbe); saved_nEq = pNew->u.btree.nEq; saved_nLTerm = pNew->nLTerm; saved_wsFlags = pNew->wsFlags; saved_prereq = pNew->prereq; saved_nOut = pNew->nOut; pNew->rSetup = (WhereCost)0; rLogSize = estLog(pProbe->aiRowEst[0]); for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){ int nIn = 1; if( pTerm->prereqRight & pNew->maskSelf ) continue; pNew->wsFlags = saved_wsFlags; pNew->u.btree.nEq = saved_nEq; pNew->nLTerm = saved_nLTerm; if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */ pNew->aLTerm[pNew->nLTerm++] = pTerm; pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf; pNew->rRun = rLogSize; if( pTerm->eOperator & WO_IN ){ Expr *pExpr = pTerm->pExpr; pNew->wsFlags |= WHERE_COLUMN_IN; if( ExprHasProperty(pExpr, EP_xIsSelect) ){ /* "x IN (SELECT ...)": Assume the SELECT returns 25 rows */ nIn = 25; }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){ /* "x IN (value, value, ...)" */ nIn = pExpr->x.pList->nExpr; } pNew->rRun *= nIn; pNew->u.btree.nEq++; pNew->nOut = (WhereCost)iRowEst * nInMul * nIn; }else if( pTerm->eOperator & (WO_EQ) ){ assert( (pNew->wsFlags & (WHERE_COLUMN_NULL|WHERE_COLUMN_IN))!=0 || nInMul==1 ); pNew->wsFlags |= WHERE_COLUMN_EQ; if( iCol<0 || (pProbe->onError!=OE_None && nInMul==1 && pNew->u.btree.nEq==pProbe->nColumn-1) ){ testcase( pNew->wsFlags & WHERE_COLUMN_IN ); pNew->wsFlags |= WHERE_ONEROW; } pNew->u.btree.nEq++; pNew->nOut = (WhereCost)iRowEst * nInMul; }else if( pTerm->eOperator & (WO_ISNULL) ){ pNew->wsFlags |= WHERE_COLUMN_NULL; pNew->u.btree.nEq++; nIn = 2; /* Assume IS NULL matches two rows */ pNew->nOut = (WhereCost)iRowEst * nInMul * nIn; }else if( pTerm->eOperator & (WO_GT|WO_GE) ){ pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT; pBtm = pTerm; pTop = 0; }else if( pTerm->eOperator & (WO_LT|WO_LE) ){ pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT; pTop = pTerm; pBtm = (pNew->wsFlags & WHERE_BTM_LIMIT)!=0 ? pNew->aLTerm[pNew->nLTerm-2] : 0; } if( pNew->wsFlags & WHERE_COLUMN_RANGE ){ /* Adjust nOut and rRun for STAT3 range values */ WhereCost rDiv; whereRangeScanEst(pParse, pProbe, pNew->u.btree.nEq, pBtm, pTop, &rDiv); pNew->nOut = saved_nOut/rDiv; } #ifdef SQLITE_ENABLE_STAT3 if( pNew->u.btree.nEq==1 && pProbe->nSample ){ if( (pTerm->eOperator & (WO_EQ|WO_ISNULL))!=0 ){ rc = whereEqualScanEst(pParse, pProbe, pTerm->pExpr->pRight, &pNew->nOut); }else if( (pTerm->eOperator & WO_IN) |
︙ | ︙ | |||
4194 4195 4196 4197 4198 4199 4200 | if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 && pNew->u.btree.nEq<=pProbe->nColumn && pProbe->zName!=0 ){ whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul*nIn); } } | > > > | > | 4253 4254 4255 4256 4257 4258 4259 4260 4261 4262 4263 4264 4265 4266 4267 4268 4269 4270 4271 | if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 && pNew->u.btree.nEq<=pProbe->nColumn && pProbe->zName!=0 ){ whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul*nIn); } } pNew->prereq = saved_prereq; pNew->u.btree.nEq = saved_nEq; pNew->wsFlags = saved_wsFlags; pNew->nOut = saved_nOut; pNew->nLTerm = saved_nLTerm; return rc; } /* ** Return True if it is possible that pIndex might be useful in ** implementing the ORDER BY clause in pBuilder. ** |
︙ | ︙ | |||
4248 4249 4250 4251 4252 4253 4254 | int aiColumnPk = -1; /* The aColumn[] value for the sPk index */ SrcList *pTabList; /* The FROM clause */ struct SrcList_item *pSrc; /* The FROM clause btree term to add */ WhereLoop *pNew; /* Template WhereLoop object */ int rc = SQLITE_OK; /* Return code */ int iSortIdx = 1; /* Index number */ int b; /* A boolean value */ | | | | 4311 4312 4313 4314 4315 4316 4317 4318 4319 4320 4321 4322 4323 4324 4325 4326 | int aiColumnPk = -1; /* The aColumn[] value for the sPk index */ SrcList *pTabList; /* The FROM clause */ struct SrcList_item *pSrc; /* The FROM clause btree term to add */ WhereLoop *pNew; /* Template WhereLoop object */ int rc = SQLITE_OK; /* Return code */ int iSortIdx = 1; /* Index number */ int b; /* A boolean value */ WhereCost rSize; /* number of rows in the table */ WhereCost rLogSize; /* Logarithm of the number of rows in the table */ pNew = pBuilder->pNew; pWInfo = pBuilder->pWInfo; pTabList = pWInfo->pTabList; pSrc = pTabList->a + pNew->iTab; assert( !IsVirtual(pSrc->pTab) ); |
︙ | ︙ | |||
4282 4283 4284 4285 4286 4287 4288 | 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; } | | | | | | | | 4345 4346 4347 4348 4349 4350 4351 4352 4353 4354 4355 4356 4357 4358 4359 4360 4361 4362 4363 4364 4365 4366 4367 4368 4369 4370 4371 4372 4373 4374 4375 4376 4377 4378 4379 4380 4381 4382 4383 4384 4385 4386 4387 4388 4389 4390 4391 4392 4393 4394 4395 4396 4397 | 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; } rSize = (WhereCost)pSrc->pTab->nRowEst; rLogSize = estLog(rSize); /* Automatic indexes */ if( !pBuilder->pBest && pTabList->nSrc>1 && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0 && !pSrc->viaCoroutine && !pSrc->notIndexed && !pSrc->isCorrelated ){ /* Generate auto-index WhereLoops */ WhereClause *pWC = pBuilder->pWC; WhereTerm *pTerm; WhereTerm *pWCEnd = pWC->a + pWC->nTerm; for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){ if( pTerm->prereqRight & pNew->maskSelf ) continue; if( termCanDriveIndex(pTerm, pSrc, 0) ){ pNew->u.btree.nEq = 1; pNew->u.btree.pIndex = 0; pNew->nLTerm = 1; pNew->aLTerm[0] = pTerm; pNew->rSetup = 20*rLogSize*pSrc->pTab->nRowEst; pNew->nOut = (WhereCost)10; pNew->rRun = rLogSize + pNew->nOut; pNew->wsFlags = WHERE_TEMP_INDEX; pNew->prereq = mExtra | pTerm->prereqRight; rc = whereLoopInsert(pBuilder, pNew); } } } /* Loop over all indices */ for(; rc==SQLITE_OK && pProbe; pProbe=pProbe->pNext, iSortIdx++){ pNew->u.btree.nEq = 0; pNew->nLTerm = 0; pNew->iSortIdx = 0; pNew->rSetup = (WhereCost)0; pNew->prereq = mExtra; pNew->u.btree.pIndex = pProbe; b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor); if( pProbe->tnum<=0 ){ /* Integer primary key index */ pNew->wsFlags = WHERE_IPK; |
︙ | ︙ | |||
4388 4389 4390 4391 4392 4393 4394 4395 4396 4397 4398 4399 4400 4401 4402 4403 4404 4405 4406 4407 4408 4409 4410 4411 4412 4413 | sqlite3 *db; sqlite3_index_info *pIdxInfo; struct sqlite3_index_constraint *pIdxCons; struct sqlite3_index_constraint_usage *pUsage; WhereTerm *pTerm; int i, j; int iTerm, mxTerm; int seenIn = 0; /* True if an IN operator is seen */ int seenVar = 0; /* True if a non-constant constraint is seen */ int iPhase; /* 0: const w/o IN, 1: const, 2: no IN, 2: IN */ WhereLoop *pNew; int rc = SQLITE_OK; pWInfo = pBuilder->pWInfo; pParse = pWInfo->pParse; db = pParse->db; pWC = pBuilder->pWC; pNew = pBuilder->pNew; pSrc = &pWInfo->pTabList->a[pNew->iTab]; pTab = pSrc->pTab; assert( IsVirtual(pTab) ); pIdxInfo = allocateIndexInfo(pParse, pWC, pSrc, pBuilder->pOrderBy); if( pIdxInfo==0 ) return SQLITE_NOMEM; pNew->prereq = 0; pNew->rSetup = 0; pNew->wsFlags = WHERE_VIRTUALTABLE; | > | > > | 4451 4452 4453 4454 4455 4456 4457 4458 4459 4460 4461 4462 4463 4464 4465 4466 4467 4468 4469 4470 4471 4472 4473 4474 4475 4476 4477 4478 4479 4480 4481 4482 4483 4484 4485 4486 4487 4488 4489 | sqlite3 *db; sqlite3_index_info *pIdxInfo; struct sqlite3_index_constraint *pIdxCons; struct sqlite3_index_constraint_usage *pUsage; WhereTerm *pTerm; int i, j; int iTerm, mxTerm; int nConstraint; int seenIn = 0; /* True if an IN operator is seen */ int seenVar = 0; /* True if a non-constant constraint is seen */ int iPhase; /* 0: const w/o IN, 1: const, 2: no IN, 2: IN */ WhereLoop *pNew; int rc = SQLITE_OK; pWInfo = pBuilder->pWInfo; pParse = pWInfo->pParse; db = pParse->db; pWC = pBuilder->pWC; pNew = pBuilder->pNew; pSrc = &pWInfo->pTabList->a[pNew->iTab]; pTab = pSrc->pTab; assert( IsVirtual(pTab) ); pIdxInfo = allocateIndexInfo(pParse, pWC, pSrc, pBuilder->pOrderBy); if( pIdxInfo==0 ) return SQLITE_NOMEM; pNew->prereq = 0; pNew->rSetup = 0; pNew->wsFlags = WHERE_VIRTUALTABLE; pNew->nLTerm = 0; pNew->u.vtab.needFree = 0; pUsage = pIdxInfo->aConstraintUsage; nConstraint = pIdxInfo->nConstraint; if( whereLoopResize(db, pNew, nConstraint) ) return SQLITE_NOMEM; for(iPhase=0; iPhase<=3; iPhase++){ if( !seenIn && (iPhase&1)!=0 ){ iPhase++; if( iPhase>3 ) break; } if( !seenVar && iPhase>1 ) break; |
︙ | ︙ | |||
4452 4453 4454 4455 4456 4457 4458 | } memset(pUsage, 0, sizeof(pUsage[0])*pIdxInfo->nConstraint); if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr); pIdxInfo->idxStr = 0; pIdxInfo->idxNum = 0; pIdxInfo->needToFreeIdxStr = 0; pIdxInfo->orderByConsumed = 0; | | | > | | < | | > | | | > | | | 4518 4519 4520 4521 4522 4523 4524 4525 4526 4527 4528 4529 4530 4531 4532 4533 4534 4535 4536 4537 4538 4539 4540 4541 4542 4543 4544 4545 4546 4547 4548 4549 4550 4551 4552 4553 4554 4555 4556 4557 4558 4559 4560 4561 4562 4563 4564 4565 4566 4567 4568 4569 4570 4571 4572 4573 4574 4575 4576 4577 4578 4579 4580 4581 4582 4583 4584 4585 4586 4587 4588 | } memset(pUsage, 0, sizeof(pUsage[0])*pIdxInfo->nConstraint); if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr); pIdxInfo->idxStr = 0; pIdxInfo->idxNum = 0; pIdxInfo->needToFreeIdxStr = 0; pIdxInfo->orderByConsumed = 0; /* ((WhereCost)2) In case of SQLITE_OMIT_FLOATING_POINT... */ pIdxInfo->estimatedCost = SQLITE_BIG_DBL / ((WhereCost)2); rc = vtabBestIndex(pParse, pTab, pIdxInfo); if( rc ) goto whereLoopAddVtab_exit; pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; pNew->prereq = 0; mxTerm = -1; assert( pNew->nLSlot>=nConstraint ); for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0; pNew->u.vtab.omitMask = 0; for(i=0; i<nConstraint; i++, pIdxCons++){ if( (iTerm = pUsage[i].argvIndex - 1)>=0 ){ j = pIdxCons->iTermOffset; if( iTerm>=nConstraint || j<0 || j>=pWC->nTerm || pNew->aLTerm[iTerm]!=0 ){ rc = SQLITE_ERROR; sqlite3ErrorMsg(pParse, "%s.xBestIndex() malfunction", pTab->zName); goto whereLoopAddVtab_exit; } pTerm = &pWC->a[j]; pNew->prereq |= pTerm->prereqRight; assert( iTerm<pNew->nLSlot ); pNew->aLTerm[iTerm] = pTerm; if( iTerm>mxTerm ) mxTerm = iTerm; if( iTerm<16 && pUsage[i].omit ) pNew->u.vtab.omitMask |= 1<<iTerm; if( (pTerm->eOperator & WO_IN)!=0 ){ if( pUsage[i].omit==0 ){ /* Do not attempt to use an IN constraint if the virtual table ** says that the equivalent EQ constraint cannot be safely omitted. ** If we do attempt to use such a constraint, some rows might be ** repeated in the output. */ break; } /* A virtual table that is constrained by an IN clause may not ** consume the ORDER BY clause because (1) the order of IN terms ** is not necessarily related to the order of output terms and ** (2) Multiple outputs from a single IN value will not merge ** together. */ pIdxInfo->orderByConsumed = 0; } } } if( i>=nConstraint ){ pNew->nLTerm = mxTerm+1; assert( pNew->nLTerm<=pNew->nLSlot ); pNew->u.vtab.idxNum = pIdxInfo->idxNum; pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr; pIdxInfo->needToFreeIdxStr = 0; pNew->u.vtab.idxStr = pIdxInfo->idxStr; pNew->u.vtab.isOrdered = (u8)((pIdxInfo->nOrderBy!=0) && pIdxInfo->orderByConsumed); pNew->rSetup = (WhereCost)0; pNew->rRun = pIdxInfo->estimatedCost; pNew->nOut = (WhereCost)25; whereLoopInsert(pBuilder, pNew); if( pNew->u.vtab.needFree ){ sqlite3_free(pNew->u.vtab.idxStr); pNew->u.vtab.needFree = 0; } } } |
︙ | ︙ | |||
4541 4542 4543 4544 4545 4546 4547 4548 4549 4550 4551 4552 4553 4554 4555 | WhereLoop sBest; struct SrcList_item *pItem; pWC = pBuilder->pWC; if( pWInfo->wctrlFlags & WHERE_AND_ONLY ) return SQLITE_OK; pWCEnd = pWC->a + pWC->nTerm; pNew = pBuilder->pNew; for(pTerm=pWC->a; pTerm<pWCEnd && rc==SQLITE_OK; pTerm++){ if( (pTerm->eOperator & WO_OR)!=0 && (pTerm->u.pOrInfo->indexable & pNew->maskSelf)!=0 ){ WhereClause * const pOrWC = &pTerm->u.pOrInfo->wc; WhereTerm * const pOrWCEnd = &pOrWC->a[pOrWC->nTerm]; WhereTerm *pOrTerm; | > | | | 4609 4610 4611 4612 4613 4614 4615 4616 4617 4618 4619 4620 4621 4622 4623 4624 4625 4626 4627 4628 4629 4630 4631 4632 4633 | WhereLoop sBest; struct SrcList_item *pItem; pWC = pBuilder->pWC; if( pWInfo->wctrlFlags & WHERE_AND_ONLY ) return SQLITE_OK; pWCEnd = pWC->a + pWC->nTerm; pNew = pBuilder->pNew; whereLoopInit(&sBest); for(pTerm=pWC->a; pTerm<pWCEnd && rc==SQLITE_OK; pTerm++){ if( (pTerm->eOperator & WO_OR)!=0 && (pTerm->u.pOrInfo->indexable & pNew->maskSelf)!=0 ){ WhereClause * const pOrWC = &pTerm->u.pOrInfo->wc; WhereTerm * const pOrWCEnd = &pOrWC->a[pOrWC->nTerm]; WhereTerm *pOrTerm; WhereCost rTotal = 0; WhereCost nRow = 0; Bitmask prereq = mExtra; pItem = pWInfo->pTabList->a + pNew->iTab; iCur = pItem->iCursor; sSubBuild = *pBuilder; sSubBuild.pOrderBy = 0; sSubBuild.pBest = &sBest; |
︙ | ︙ | |||
4573 4574 4575 4576 4577 4578 4579 4580 4581 4582 4583 4584 4585 | tempWC.nTerm = 1; tempWC.a = pOrTerm; sSubBuild.pWC = &tempWC; }else{ continue; } sBest.maskSelf = 0; if( IsVirtual(pItem->pTab) ){ rc = whereLoopAddVirtual(&sSubBuild, mExtra); }else{ rc = whereLoopAddBtree(&sSubBuild, mExtra); } if( sBest.maskSelf==0 ) break; | > > | > | | | > < | < < < < < < | < < < < | 4642 4643 4644 4645 4646 4647 4648 4649 4650 4651 4652 4653 4654 4655 4656 4657 4658 4659 4660 4661 4662 4663 4664 4665 4666 4667 4668 4669 4670 4671 4672 4673 4674 4675 4676 4677 4678 4679 4680 4681 4682 4683 4684 4685 4686 4687 4688 4689 4690 4691 4692 4693 4694 4695 4696 4697 4698 4699 4700 4701 4702 4703 4704 4705 4706 4707 4708 4709 4710 4711 4712 4713 4714 4715 4716 4717 4718 4719 4720 4721 | tempWC.nTerm = 1; tempWC.a = pOrTerm; sSubBuild.pWC = &tempWC; }else{ continue; } sBest.maskSelf = 0; sBest.rSetup = 0; sBest.rRun = 0; if( IsVirtual(pItem->pTab) ){ rc = whereLoopAddVirtual(&sSubBuild, mExtra); }else{ rc = whereLoopAddBtree(&sSubBuild, mExtra); } if( sBest.maskSelf==0 ) break; assert( sBest.rSetup==(WhereCost)0 ); rTotal += sBest.rRun; nRow += sBest.nOut; prereq |= sBest.prereq; } assert( pNew->nLSlot>=1 ); pNew->nLTerm = 1; pNew->aLTerm[0] = pTerm; pNew->wsFlags = WHERE_MULTI_OR; pNew->rSetup = (WhereCost)0; pNew->rRun = rTotal; pNew->nOut = nRow; pNew->prereq = prereq; memset(&pNew->u, 0, sizeof(pNew->u)); rc = whereLoopInsert(pBuilder, pNew); } } whereLoopClear(pWInfo->pParse->db, &sBest); return rc; } /* ** Add all WhereLoop objects for all tables */ static int whereLoopAddAll(WhereLoopBuilder *pBuilder){ WhereInfo *pWInfo = pBuilder->pWInfo; Bitmask mExtra = 0; Bitmask mPrior = 0; int iTab; SrcList *pTabList = pWInfo->pTabList; struct SrcList_item *pItem; sqlite3 *db = pWInfo->pParse->db; int nTabList = pWInfo->nLevel; int rc = SQLITE_OK; WhereLoop *pNew; /* Loop over the tables in the join, from left to right */ pBuilder->pNew = pNew = sqlite3DbMallocZero(db, sizeof(WhereLoop)); if( pNew==0 ) return SQLITE_NOMEM; pNew->aLTerm = pNew->aLTermSpace; pNew->nLSlot = ArraySize(pNew->aLTermSpace); for(iTab=0, pItem=pTabList->a; iTab<nTabList; iTab++, pItem++){ pNew->iTab = iTab; pNew->maskSelf = getMask(&pWInfo->sMaskSet, pItem->iCursor); if( (pItem->jointype & (JT_LEFT|JT_CROSS))!=0 ){ mExtra = mPrior; } if( IsVirtual(pItem->pTab) ){ rc = whereLoopAddVirtual(pBuilder, mExtra); }else{ rc = whereLoopAddBtree(pBuilder, mExtra); } if( rc==SQLITE_OK ){ rc = whereLoopAddOr(pBuilder, mExtra); } mPrior |= pNew->maskSelf; if( rc || db->mallocFailed ) break; } whereLoopDelete(db, pBuilder->pNew); pBuilder->pNew = 0; return rc; } /* ** Examine a WherePath (with the addition of the extra WhereLoop of the 5th |
︙ | ︙ | |||
4751 4752 4753 4754 4755 4756 4757 | } /* For every term of the index that is constrained by == or IS NULL, ** mark off corresponding ORDER BY terms wherever they occur ** in the ORDER BY clause. */ for(i=0; i<pLoop->u.btree.nEq; i++){ | | | 4813 4814 4815 4816 4817 4818 4819 4820 4821 4822 4823 4824 4825 4826 4827 | } /* For every term of the index that is constrained by == or IS NULL, ** mark off corresponding ORDER BY terms wherever they occur ** in the ORDER BY clause. */ for(i=0; i<pLoop->u.btree.nEq; i++){ pTerm = pLoop->aLTerm[i]; if( (pTerm->eOperator & (WO_EQ|WO_ISNULL))==0 ) continue; iColumn = pTerm->u.leftColumn; for(j=0; j<nOrderBy; j++){ if( MASKBIT(j) & obSat ) continue; pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[j].pExpr); if( pOBExpr->op!=TK_COLUMN ) continue; if( pOBExpr->iTable!=iCur ) continue; |
︙ | ︙ | |||
4780 4781 4782 4783 4784 4785 4786 | rev = revSet = 0; distinctColumns = 0; for(j=0; j<=nColumn; j++){ u8 bOnce; /* True to run the ORDER BY search loop */ /* Skip over == and IS NULL terms */ if( j<pLoop->u.btree.nEq | | | 4842 4843 4844 4845 4846 4847 4848 4849 4850 4851 4852 4853 4854 4855 4856 | rev = revSet = 0; distinctColumns = 0; for(j=0; j<=nColumn; j++){ u8 bOnce; /* True to run the ORDER BY search loop */ /* Skip over == and IS NULL terms */ if( j<pLoop->u.btree.nEq && ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL))!=0 ){ if( i & WO_ISNULL ) isOrderDistinct = 0; continue; } /* Get the column number in the table (iColumn) and sort order ** (revIdx) for the j-th column of the index. |
︙ | ︙ | |||
4894 4895 4896 4897 4898 4899 4900 | ** Given the list of WhereLoop objects on pWInfo->pLoops, this routine ** attempts to find the lowest cost path that visits each WhereLoop ** once. This path is then loaded into the pWInfo->a[].pWLoop fields. ** ** Return SQLITE_OK on success or SQLITE_NOMEM of a memory allocation ** error occurs. */ | | | | | | 4956 4957 4958 4959 4960 4961 4962 4963 4964 4965 4966 4967 4968 4969 4970 4971 4972 4973 4974 4975 4976 4977 4978 | ** Given the list of WhereLoop objects on pWInfo->pLoops, this routine ** attempts to find the lowest cost path that visits each WhereLoop ** once. This path is then loaded into the pWInfo->a[].pWLoop fields. ** ** Return SQLITE_OK on success or SQLITE_NOMEM of a memory allocation ** error occurs. */ static int wherePathSolver(WhereInfo *pWInfo, WhereCost nRowEst){ int mxChoice; /* Maximum number of simultaneous paths tracked */ int nLoop; /* Number of terms in the join */ sqlite3 *db; /* The database connection */ int iLoop; /* Loop counter over the terms of the join */ int ii, jj; /* Loop counters */ WhereCost rCost; /* Cost of a path */ WhereCost mxCost; /* Maximum cost of a set of paths */ WhereCost rSortCost; /* Cost to do a sort */ int nTo, nFrom; /* Number of valid entries in aTo[] and aFrom[] */ WherePath *aFrom; /* All nFrom paths at the previous level */ WherePath *aTo; /* The nTo best paths at the current level */ WherePath *pFrom; /* An element of aFrom[] that we are working on */ WherePath *pTo; /* An element of aTo[] that we are working on */ WhereLoop *pWLoop; /* One of the WhereLoop objects */ WhereLoop **pX; /* Used to divy up the pSpace memory */ |
︙ | ︙ | |||
4933 4934 4935 4936 4937 4938 4939 | memset(aFrom, 0, sizeof(aFrom[0])); pX = (WhereLoop**)(aFrom+mxChoice); for(ii=mxChoice*2, pFrom=aTo; ii>0; ii--, pFrom++, pX += nLoop){ pFrom->aLoop = pX; } /* Seed the search with a single WherePath containing zero WhereLoops */ | | | | 4995 4996 4997 4998 4999 5000 5001 5002 5003 5004 5005 5006 5007 5008 5009 5010 5011 5012 5013 5014 | memset(aFrom, 0, sizeof(aFrom[0])); pX = (WhereLoop**)(aFrom+mxChoice); for(ii=mxChoice*2, pFrom=aTo; ii>0; ii--, pFrom++, pX += nLoop){ pFrom->aLoop = pX; } /* Seed the search with a single WherePath containing zero WhereLoops */ aFrom[0].nRow = (WhereCost)1; nFrom = 1; /* Precompute the cost of sorting the final result set, if the caller ** to sqlite3WhereBegin() was concerned about sorting */ rSortCost = (WhereCost)0; if( pWInfo->pOrderBy==0 || nRowEst<=0.0 ){ aFrom[0].isOrderedValid = 1; }else{ /* Compute an estimate on the cost to sort the entire result set */ rSortCost = nRowEst*estLog(nRowEst); #ifdef WHERETRACE_ENABLED if( sqlite3WhereTrace>=2 ){ |
︙ | ︙ | |||
5408 5409 5410 5411 5412 5413 5414 | #endif /* Open all tables in the pTabList and any indices selected for ** searching those tables. */ sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */ notReady = ~(Bitmask)0; | | | 5470 5471 5472 5473 5474 5475 5476 5477 5478 5479 5480 5481 5482 5483 5484 | #endif /* Open all tables in the pTabList and any indices selected for ** searching those tables. */ sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */ notReady = ~(Bitmask)0; pWInfo->nRowOut = (WhereCost)1; for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){ Table *pTab; /* Table to open */ int iDb; /* Index of database containing table/index */ struct SrcList_item *pTabItem; WhereLoop *pLoop; pTabItem = &pTabList->a[pLevel->iFrom]; |
︙ | ︙ |