Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Experimental changes to EXPLAIN QUERY PLAN. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | experimental |
Files: | files | file ages | folders |
SHA1: |
f4747eb83dacce6430ad6e5eb20155ff |
User & Date: | dan 2010-11-08 19:01:16.000 |
Context
2010-11-09
| ||
14:49 | Further enhancements and fixes for explain query plan. (check-in: 73c93f5a2a user: dan tags: experimental) | |
2010-11-08
| ||
19:01 | Experimental changes to EXPLAIN QUERY PLAN. (check-in: f4747eb83d user: dan tags: experimental) | |
2010-11-05
| ||
20:50 | Fix to xTruncate and more journal mode tests for the multiplex VFS. (check-in: 65fa1164f0 user: shaneh tags: trunk) | |
Changes
Changes to src/prepare.c.
︙ | ︙ | |||
624 625 626 627 628 629 630 | } rc = pParse->rc; #ifndef SQLITE_OMIT_EXPLAIN if( rc==SQLITE_OK && pParse->pVdbe && pParse->explain ){ static const char * const azColName[] = { "addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment", | | | | | 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 | } rc = pParse->rc; #ifndef SQLITE_OMIT_EXPLAIN if( rc==SQLITE_OK && pParse->pVdbe && pParse->explain ){ static const char * const azColName[] = { "addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment", "selectid", "order", "from", "detail" }; int iFirst, mx; if( pParse->explain==2 ){ sqlite3VdbeSetNumCols(pParse->pVdbe, 4); iFirst = 8; mx = 12; }else{ sqlite3VdbeSetNumCols(pParse->pVdbe, 8); iFirst = 0; mx = 8; } for(i=iFirst; i<mx; i++){ sqlite3VdbeSetColName(pParse->pVdbe, i-iFirst, COLNAME_NAME, |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
767 768 769 770 771 772 773 774 775 776 777 778 779 780 | pInfo->aColl[i] = pColl; pInfo->aSortOrder[i] = pItem->sortOrder; } } return pInfo; } /* ** If the inner loop was generated using a non-null pOrderBy argument, ** then the results were placed in a sorter. After the loop is terminated ** we need to run the sorter and output the results. The following ** routine generates the code needed to do that. */ | > > > > > > > > > > > > > | 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 | pInfo->aColl[i] = pColl; pInfo->aSortOrder[i] = pItem->sortOrder; } } return pInfo; } #ifndef SQLITE_OMIT_EXPLAIN static void explainTempTable(Parse *pParse, const char *zUsage){ if( pParse->explain==2 ){ Vdbe *v = pParse->pVdbe; char *zMsg = sqlite3MPrintf(pParse->db, "USE TEMP B-TREE FOR %s", zUsage); sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC); } } # define explainRestoreSelectId() pParse->iSelectId = iRestoreSelectId #else # define explainRestoreSelectId() # define explainTempTable(y,z) #endif /* ** If the inner loop was generated using a non-null pOrderBy argument, ** then the results were placed in a sorter. After the loop is terminated ** we need to run the sorter and output the results. The following ** routine generates the code needed to do that. */ |
︙ | ︙ | |||
3585 3586 3587 3588 3589 3590 3591 3592 3593 3594 3595 3596 3597 3598 | int isDistinct; /* True if the DISTINCT keyword is present */ int distinct; /* Table to use for the distinct set */ int rc = 1; /* Value to return from this function */ int addrSortIndex; /* Address of an OP_OpenEphemeral instruction */ AggInfo sAggInfo; /* Information used by aggregate queries */ int iEnd; /* Address of the end of the query */ sqlite3 *db; /* The database connection */ db = pParse->db; if( p==0 || db->mallocFailed || pParse->nErr ){ return 1; } if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1; memset(&sAggInfo, 0, sizeof(sAggInfo)); | > > > > > | 3598 3599 3600 3601 3602 3603 3604 3605 3606 3607 3608 3609 3610 3611 3612 3613 3614 3615 3616 | int isDistinct; /* True if the DISTINCT keyword is present */ int distinct; /* Table to use for the distinct set */ int rc = 1; /* Value to return from this function */ int addrSortIndex; /* Address of an OP_OpenEphemeral instruction */ AggInfo sAggInfo; /* Information used by aggregate queries */ int iEnd; /* Address of the end of the query */ sqlite3 *db; /* The database connection */ #ifndef SQLITE_OMIT_EXPLAIN int iRestoreSelectId = pParse->iSelectId; pParse->iSelectId = pParse->iNextSelectId++; #endif db = pParse->db; if( p==0 || db->mallocFailed || pParse->nErr ){ return 1; } if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1; memset(&sAggInfo, 0, sizeof(sAggInfo)); |
︙ | ︙ | |||
3692 3693 3694 3695 3696 3697 3698 | pLoop->pRightmost = p; pLoop->pNext = pRight; pRight = pLoop; } mxSelect = db->aLimit[SQLITE_LIMIT_COMPOUND_SELECT]; if( mxSelect && cnt>mxSelect ){ sqlite3ErrorMsg(pParse, "too many terms in compound SELECT"); | | > < | 3710 3711 3712 3713 3714 3715 3716 3717 3718 3719 3720 3721 3722 3723 3724 3725 3726 3727 3728 3729 3730 3731 3732 3733 3734 3735 3736 3737 3738 3739 | pLoop->pRightmost = p; pLoop->pNext = pRight; pRight = pLoop; } mxSelect = db->aLimit[SQLITE_LIMIT_COMPOUND_SELECT]; if( mxSelect && cnt>mxSelect ){ sqlite3ErrorMsg(pParse, "too many terms in compound SELECT"); goto select_end; } } explainRestoreSelectId(); return multiSelect(pParse, p, pDest); } #endif /* If possible, rewrite the query to use GROUP BY instead of DISTINCT. ** GROUP BY might use an index, DISTINCT never does. */ assert( p->pGroupBy==0 || (p->selFlags & SF_Aggregate)!=0 ); if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct ){ p->pGroupBy = sqlite3ExprListDup(db, p->pEList, 0); pGroupBy = p->pGroupBy; p->selFlags &= ~SF_Distinct; } /* If there is both a GROUP BY and an ORDER BY clause and they are ** identical, then disable the ORDER BY clause since the GROUP BY ** will cause elements to come out in the correct order. This is ** an optimization - the correct answer should result regardless. ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER |
︙ | ︙ | |||
3754 3755 3756 3757 3758 3759 3760 | /* Set the limiter. */ iEnd = sqlite3VdbeMakeLabel(v); computeLimitRegisters(pParse, p, iEnd); /* Open a virtual index to use for the distinct set. */ | | | 3772 3773 3774 3775 3776 3777 3778 3779 3780 3781 3782 3783 3784 3785 3786 | /* Set the limiter. */ iEnd = sqlite3VdbeMakeLabel(v); computeLimitRegisters(pParse, p, iEnd); /* Open a virtual index to use for the distinct set. */ if( p->selFlags & SF_Distinct ){ KeyInfo *pKeyInfo; assert( isAgg || pGroupBy ); distinct = pParse->nTab++; pKeyInfo = keyInfoFromExprList(pParse, p->pEList); sqlite3VdbeAddOp4(v, OP_OpenEphemeral, distinct, 0, 0, (char*)pKeyInfo, P4_KEYINFO_HANDOFF); sqlite3VdbeChangeP5(v, BTREE_UNORDERED); |
︙ | ︙ | |||
3913 3914 3915 3916 3917 3918 3919 3920 3921 3922 3923 3924 3925 3926 | ** in sorted order */ int regBase; int regRecord; int nCol; int nGroupBy; groupBySort = 1; nGroupBy = pGroupBy->nExpr; nCol = nGroupBy + 1; j = nGroupBy+1; for(i=0; i<sAggInfo.nColumn; i++){ if( sAggInfo.aCol[i].iSorterColumn>=j ){ nCol++; | > > > | 3931 3932 3933 3934 3935 3936 3937 3938 3939 3940 3941 3942 3943 3944 3945 3946 3947 | ** in sorted order */ int regBase; int regRecord; int nCol; int nGroupBy; explainTempTable(pParse, isDistinct && !(p->selFlags&SF_Distinct)?"DISTINCT":"GROUP BY"); groupBySort = 1; nGroupBy = pGroupBy->nExpr; nCol = nGroupBy + 1; j = nGroupBy+1; for(i=0; i<sAggInfo.nColumn; i++){ if( sAggInfo.aCol[i].iSorterColumn>=j ){ nCol++; |
︙ | ︙ | |||
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 | selectInnerLoop(pParse, p, p->pEList, 0, 0, 0, -1, pDest, addrEnd, addrEnd); sqlite3ExprListDelete(db, pDel); } sqlite3VdbeResolveLabel(v, addrEnd); } /* endif aggregate query */ /* If there is an ORDER BY clause, then we need to sort the results ** and send them to the callback one by one. */ if( pOrderBy ){ generateSortTail(pParse, p, v, pEList->nExpr, pDest); } /* Jump here to skip this query */ sqlite3VdbeResolveLabel(v, iEnd); /* The SELECT was successfully coded. Set the return code to 0 ** to indicate no errors. */ rc = 0; /* Control jumps to here if an error is encountered above, or upon ** successful coding of the SELECT. */ select_end: /* Identify column names if results of the SELECT are to be output. */ if( rc==SQLITE_OK && pDest->eDest==SRT_Output ){ generateColumnNames(pParse, pTabList, pEList); } | > > > > > > | 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 | selectInnerLoop(pParse, p, p->pEList, 0, 0, 0, -1, pDest, addrEnd, addrEnd); sqlite3ExprListDelete(db, pDel); } sqlite3VdbeResolveLabel(v, addrEnd); } /* endif aggregate query */ if( distinct>=0 ){ explainTempTable(pParse, "DISTINCT"); } /* If there is an ORDER BY clause, then we need to sort the results ** and send them to the callback one by one. */ if( pOrderBy ){ explainTempTable(pParse, "ORDER BY"); generateSortTail(pParse, p, v, pEList->nExpr, pDest); } /* Jump here to skip this query */ sqlite3VdbeResolveLabel(v, iEnd); /* The SELECT was successfully coded. Set the return code to 0 ** to indicate no errors. */ rc = 0; /* Control jumps to here if an error is encountered above, or upon ** successful coding of the SELECT. */ select_end: explainRestoreSelectId(); /* Identify column names if results of the SELECT are to be output. */ if( rc==SQLITE_OK && pDest->eDest==SRT_Output ){ generateColumnNames(pParse, pTabList, pEList); } |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 | ** pTerm is only used when wsFlags&WHERE_MULTI_OR is true. And pVtabIdx ** is only used when wsFlags&WHERE_VIRTUALTABLE is true. It is never the ** case that more than one of these conditions is true. */ struct WherePlan { u32 wsFlags; /* WHERE_* flags that describe the strategy */ u32 nEq; /* Number of == constraints */ union { Index *pIdx; /* Index when WHERE_INDEXED is true */ struct WhereTerm *pTerm; /* WHERE clause term for OR-search */ sqlite3_index_info *pVtabIdx; /* Virtual table index to use */ } u; }; | > | 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 | ** pTerm is only used when wsFlags&WHERE_MULTI_OR is true. And pVtabIdx ** is only used when wsFlags&WHERE_VIRTUALTABLE is true. It is never the ** case that more than one of these conditions is true. */ struct WherePlan { u32 wsFlags; /* WHERE_* flags that describe the strategy */ u32 nEq; /* Number of == constraints */ double nRow; /* Estimated number of rows (for EQP) */ union { Index *pIdx; /* Index when WHERE_INDEXED is true */ struct WhereTerm *pTerm; /* WHERE clause term for OR-search */ sqlite3_index_info *pVtabIdx; /* Virtual table index to use */ } u; }; |
︙ | ︙ | |||
2209 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 2222 | u8 declareVtab; /* True if inside sqlite3_declare_vtab() */ int nVtabLock; /* Number of virtual tables to lock */ Table **apVtabLock; /* Pointer to virtual tables needing locking */ #endif int nHeight; /* Expression tree height of current sub-select */ Table *pZombieTab; /* List of Table objects to delete after code gen */ TriggerPrg *pTriggerPrg; /* Linked list of coded triggers */ }; #ifdef SQLITE_OMIT_VIRTUALTABLE #define IN_DECLARE_VTAB 0 #else #define IN_DECLARE_VTAB (pParse->declareVtab) #endif | > > > > > | 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 2222 2223 2224 2225 2226 2227 2228 | u8 declareVtab; /* True if inside sqlite3_declare_vtab() */ int nVtabLock; /* Number of virtual tables to lock */ Table **apVtabLock; /* Pointer to virtual tables needing locking */ #endif int nHeight; /* Expression tree height of current sub-select */ Table *pZombieTab; /* List of Table objects to delete after code gen */ TriggerPrg *pTriggerPrg; /* Linked list of coded triggers */ #ifndef SQLITE_OMIT_EXPLAIN int iSelectId; int iNextSelectId; #endif }; #ifdef SQLITE_OMIT_VIRTUALTABLE #define IN_DECLARE_VTAB 0 #else #define IN_DECLARE_VTAB (pParse->declareVtab) #endif |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
1178 1179 1180 1181 1182 1183 1184 | pMem++; pMem->flags = MEM_Int; pMem->u.i = pOp->p2; /* P2 */ pMem->type = SQLITE_INTEGER; pMem++; | < | | | | < | 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 | pMem++; pMem->flags = MEM_Int; pMem->u.i = pOp->p2; /* P2 */ pMem->type = SQLITE_INTEGER; pMem++; pMem->flags = MEM_Int; pMem->u.i = pOp->p3; /* P3 */ pMem->type = SQLITE_INTEGER; pMem++; if( sqlite3VdbeMemGrow(pMem, 32, 0) ){ /* P4 */ assert( p->db->mallocFailed ); return SQLITE_ERROR; } pMem->flags = MEM_Dyn|MEM_Str|MEM_Term; z = displayP4(pOp, pMem->z, 32); |
︙ | ︙ | |||
1228 1229 1230 1231 1232 1233 1234 | #endif { pMem->flags = MEM_Null; /* Comment */ pMem->type = SQLITE_NULL; } } | | | 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 | #endif { pMem->flags = MEM_Null; /* Comment */ pMem->type = SQLITE_NULL; } } p->nResColumn = 8 - 4*(p->explain-1); p->rc = SQLITE_OK; rc = SQLITE_ROW; } return rc; } #endif /* SQLITE_OMIT_EXPLAIN */ |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
188 189 190 191 192 193 194 | /* ** A WhereCost object records a lookup strategy and the estimated ** cost of pursuing that strategy. */ struct WhereCost { WherePlan plan; /* The lookup strategy */ double rCost; /* Overall cost of pursuing this search strategy */ | < | 188 189 190 191 192 193 194 195 196 197 198 199 200 201 | /* ** A WhereCost object records a lookup strategy and the estimated ** cost of pursuing that strategy. */ struct WhereCost { WherePlan plan; /* The lookup strategy */ double rCost; /* Overall cost of pursuing this search strategy */ Bitmask used; /* Bitmask of cursors used by this plan */ }; /* ** 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. |
︙ | ︙ | |||
1617 1618 1619 1620 1621 1622 1623 | tempWC.a = pOrTerm; tempWC.nTerm = 1; bestIndex(pParse, &tempWC, pSrc, notReady, notValid, 0, &sTermCost); }else{ continue; } rTotal += sTermCost.rCost; | | < > | 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 | tempWC.a = pOrTerm; tempWC.nTerm = 1; bestIndex(pParse, &tempWC, pSrc, notReady, notValid, 0, &sTermCost); }else{ continue; } rTotal += sTermCost.rCost; nRow += sTermCost.plan.nRow; used |= sTermCost.used; if( rTotal>=pCost->rCost ) break; } /* If there is an ORDER BY clause, increase the scan cost to account ** for the cost of the sort. */ if( pOrderBy!=0 ){ WHERETRACE(("... sorting increases OR cost %.9g to %.9g\n", rTotal, rTotal+nRow*estLog(nRow))); rTotal += nRow*estLog(nRow); } /* If the cost of scanning using this OR term for optimization is ** less than the current cost stored in pCost, replace the contents ** of pCost. */ WHERETRACE(("... multi-index OR cost=%.9g nrow=%.9g\n", rTotal, nRow)); if( rTotal<pCost->rCost ){ pCost->rCost = rTotal; pCost->used = used; pCost->plan.nRow = nRow; pCost->plan.wsFlags = flags; pCost->plan.u.pTerm = pTerm; } } } #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ } |
︙ | ︙ | |||
1721 1722 1723 1724 1725 1726 1727 | /* Search for any equality comparison term */ pWCEnd = &pWC->a[pWC->nTerm]; for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ if( termCanDriveIndex(pTerm, pSrc, notReady) ){ WHERETRACE(("auto-index reduces cost from %.2f to %.2f\n", pCost->rCost, costTempIdx)); pCost->rCost = costTempIdx; | | | 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 | /* Search for any equality comparison term */ pWCEnd = &pWC->a[pWC->nTerm]; for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ if( termCanDriveIndex(pTerm, pSrc, notReady) ){ WHERETRACE(("auto-index reduces cost from %.2f to %.2f\n", pCost->rCost, costTempIdx)); pCost->rCost = costTempIdx; pCost->plan.nRow = logN + 1; pCost->plan.wsFlags = WHERE_TEMP_INDEX; pCost->used = pTerm->prereqRight; break; } } } #else |
︙ | ︙ | |||
2794 2795 2796 2797 2798 2799 2800 | notReady, nRow, cost, used )); /* If this index is the best we have seen so far, then record this ** index and its cost in the pCost structure. */ if( (!pIdx || wsFlags) | | < > | 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 2805 2806 2807 2808 2809 2810 2811 | notReady, nRow, cost, used )); /* If this index is the best we have seen so far, then record this ** index and its cost in the pCost structure. */ if( (!pIdx || wsFlags) && (cost<pCost->rCost || (cost<=pCost->rCost && nRow<pCost->plan.nRow)) ){ pCost->rCost = cost; pCost->used = used; pCost->plan.nRow = nRow; pCost->plan.wsFlags = (wsFlags&wsFlagMask); pCost->plan.nEq = nEq; pCost->plan.u.pIdx = pIdx; } /* If there was an INDEXED BY clause, then only that one index is ** considered. */ |
︙ | ︙ | |||
3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 | } } } } *pzAff = zAff; return regBase; } /* ** Generate code for the start of the iLevel-th loop in the WHERE clause ** implementation described by pWInfo. */ static Bitmask codeOneLoopStart( WhereInfo *pWInfo, /* Complete information about the WHERE clause */ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 3125 3126 3127 3128 3129 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 3189 3190 3191 3192 3193 3194 3195 3196 3197 3198 3199 3200 3201 3202 3203 3204 3205 3206 3207 3208 3209 3210 3211 3212 3213 3214 3215 3216 3217 3218 3219 3220 3221 3222 3223 3224 3225 3226 3227 3228 3229 3230 | } } } } *pzAff = zAff; return regBase; } #ifndef SQLITE_OMIT_EXPLAIN static char *indexRangeText(sqlite3 *db, WhereLevel *pLevel, Table *pTab){ WherePlan *pPlan = &pLevel->plan; Index *pIndex = pPlan->u.pIdx; int nEq = pPlan->nEq; char *zRet = 0; int i; for(i=0; i<nEq; i++){ char *zCol = pTab->aCol[pIndex->aiColumn[i]].zName; zRet = sqlite3MAppendf(db, zRet, "%s%s%s=?", (zRet?zRet:""), (zRet?" AND ":""), zCol); } if( pPlan->wsFlags&WHERE_BTM_LIMIT ){ zRet = sqlite3MAppendf(db, zRet, "%s%s%s>?", (zRet?zRet:""), (zRet?" AND ":""), pTab->aCol[nEq].zName); } if( pPlan->wsFlags&WHERE_TOP_LIMIT ){ zRet = sqlite3MAppendf(db, zRet, "%s%s%s<?", (zRet?zRet:""), (zRet?" AND ":""), pTab->aCol[nEq].zName); } if( zRet ){ zRet = sqlite3MAppendf(db, zRet, " (%s)", zRet); } return zRet; } static void codeOneLoopExplain( Parse *pParse, /* Parse context */ SrcList *pTabList, /* Table list this loop refers to */ WhereLevel *pLevel, /* Scan to write OP_Explain opcode for */ int iLevel, /* Value for "level" column of output */ int iFrom /* Value for "from" column of output */ ){ if( pParse->explain==2 ){ u32 flags = pLevel->plan.wsFlags; struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom]; Vdbe *v = pParse->pVdbe; sqlite3 *db = pParse->db; char *zMsg; if( flags & WHERE_MULTI_OR ) return; zMsg = sqlite3MPrintf(db, "TABLE %s", pItem->zName); if( pItem->zAlias ){ zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias); } if( (flags & WHERE_INDEXED)!=0 ){ char *zWhere = indexRangeText(db, pLevel, pItem->pTab); zMsg = sqlite3MAppendf(db, zMsg, "%s WITH %s%sINDEX%s%s%s", zMsg, ((flags & WHERE_TEMP_INDEX)?"AUTOMATIC ":""), ((flags & WHERE_IDX_ONLY)?"COVERING ":""), ((flags & WHERE_TEMP_INDEX)?"":" "), ((flags & WHERE_TEMP_INDEX)?"": pLevel->plan.u.pIdx->zName), zWhere ); sqlite3DbFree(db, zWhere); }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){ zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg); if( flags&WHERE_ROWID_EQ ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg); }else if( flags&WHERE_BTM_LIMIT && flags&WHERE_TOP_LIMIT ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg); }else if( flags&WHERE_BTM_LIMIT ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>?)", zMsg); }else if( flags&WHERE_TOP_LIMIT ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid<?)", zMsg); } } #ifndef SQLITE_OMIT_VIRTUALTABLE else if( (flags & WHERE_VIRTUALTABLE)!=0 ){ sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx; zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg, pVtabIdx->idxNum, pVtabIdx->idxStr); } #endif zMsg = sqlite3MAppendf(db, zMsg, "%s (~%lld rows)", zMsg, (sqlite3_int64)(pLevel->plan.nRow) ); sqlite3VdbeAddOp4( v, OP_Explain, pParse->iSelectId, iLevel, iFrom, zMsg, P4_DYNAMIC); } } #else # define codeOneLoopExplain(w,x,y.z) #endif /* SQLITE_OMIT_EXPLAIN */ /* ** Generate code for the start of the iLevel-th loop in the WHERE clause ** implementation described by pWInfo. */ static Bitmask codeOneLoopStart( WhereInfo *pWInfo, /* Complete information about the WHERE clause */ |
︙ | ︙ | |||
3668 3669 3670 3671 3672 3673 3674 3675 3676 3677 3678 3679 3680 3681 | if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){ WhereInfo *pSubWInfo; /* Info for single OR-term scan */ /* Loop through table entries that match term pOrTerm. */ pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrTerm->pExpr, 0, WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE | WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY); if( pSubWInfo ){ if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){ int iSet = ((ii==pOrWc->nTerm-1)?-1:ii); int r; r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, regRowid); sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset, sqlite3VdbeCurrentAddr(v)+2, r, iSet); | > > > | 3759 3760 3761 3762 3763 3764 3765 3766 3767 3768 3769 3770 3771 3772 3773 3774 3775 | if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){ WhereInfo *pSubWInfo; /* Info for single OR-term scan */ /* Loop through table entries that match term pOrTerm. */ pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrTerm->pExpr, 0, WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE | WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY); if( pSubWInfo ){ codeOneLoopExplain( pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom ); if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){ int iSet = ((ii==pOrWc->nTerm-1)?-1:ii); int r; r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, regRowid); sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset, sqlite3VdbeCurrentAddr(v)+2, r, iSet); |
︙ | ︙ | |||
4180 4181 4182 4183 4184 4185 4186 | */ if( (sCost.used¬Ready)==0 /* (1) */ && (bestJ<0 || (notIndexed&m)!=0 /* (2) */ || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0) && (nUnconstrained==0 || pTabItem->pIndex==0 /* (3) */ || NEVER((sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)) && (bestJ<0 || sCost.rCost<bestPlan.rCost /* (4) */ | | > | | > | > | 4274 4275 4276 4277 4278 4279 4280 4281 4282 4283 4284 4285 4286 4287 4288 4289 4290 4291 4292 4293 4294 4295 4296 4297 4298 4299 4300 4301 4302 4303 4304 4305 4306 4307 4308 4309 4310 4311 4312 4313 4314 4315 4316 4317 4318 4319 4320 4321 | */ if( (sCost.used¬Ready)==0 /* (1) */ && (bestJ<0 || (notIndexed&m)!=0 /* (2) */ || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0) && (nUnconstrained==0 || pTabItem->pIndex==0 /* (3) */ || NEVER((sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)) && (bestJ<0 || sCost.rCost<bestPlan.rCost /* (4) */ || (sCost.rCost<=bestPlan.rCost && sCost.plan.nRow<bestPlan.plan.nRow)) ){ WHERETRACE(("=== table %d is best so far" " with cost=%g and nRow=%g\n", j, sCost.rCost, sCost.plan.nRow)); bestPlan = sCost; bestJ = j; } if( doNotReorder ) break; } } assert( bestJ>=0 ); assert( notReady & getMask(pMaskSet, pTabList->a[bestJ].iCursor) ); WHERETRACE(("*** Optimizer selects table %d for loop %d" " with cost=%g and nRow=%g\n", bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow)); if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){ *ppOrderBy = 0; } andFlags &= bestPlan.plan.wsFlags; pLevel->plan = bestPlan.plan; testcase( bestPlan.plan.wsFlags & WHERE_INDEXED ); testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX ); if( bestPlan.plan.wsFlags & (WHERE_INDEXED|WHERE_TEMP_INDEX) ){ pLevel->iIdxCur = pParse->nTab++; }else{ pLevel->iIdxCur = -1; } notReady &= ~getMask(pMaskSet, pTabList->a[bestJ].iCursor); pLevel->iFrom = (u8)bestJ; if( bestPlan.plan.nRow>=(double)1 ){ pParse->nQueryLoop *= bestPlan.plan.nRow; } /* Check that if the table scanned by this loop iteration had an ** INDEXED BY clause attached to it, that the named index is being ** used for the scan. If not, then query compilation has failed. ** Return an error. */ pIdx = pTabList->a[bestJ].pIndex; |
︙ | ︙ | |||
4262 4263 4264 4265 4266 4267 4268 | */ sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */ notReady = ~(Bitmask)0; for(i=0, pLevel=pWInfo->a; i<nTabList; i++, pLevel++){ Table *pTab; /* Table to open */ int iDb; /* Index of database containing table/index */ | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 4359 4360 4361 4362 4363 4364 4365 4366 4367 4368 4369 4370 4371 4372 | */ sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */ notReady = ~(Bitmask)0; for(i=0, pLevel=pWInfo->a; i<nTabList; i++, pLevel++){ Table *pTab; /* Table to open */ int iDb; /* Index of database containing table/index */ pTabItem = &pTabList->a[pLevel->iFrom]; pTab = pTabItem->pTab; pLevel->iTabCur = pTabItem->iCursor; iDb = sqlite3SchemaToIndex(db, pTab->pSchema); if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){ /* Do nothing */ }else |
︙ | ︙ | |||
4351 4352 4353 4354 4355 4356 4357 4358 4359 4360 4361 4362 4363 4364 | /* Generate the code to do the search. Each iteration of the for ** loop below generates code for a single nested loop of the VM ** program. */ notReady = ~(Bitmask)0; for(i=0; i<nTabList; i++){ notReady = codeOneLoopStart(pWInfo, i, wctrlFlags, notReady); pWInfo->iContinue = pWInfo->a[i].addrCont; } #ifdef SQLITE_TEST /* For testing and debugging use only */ /* Record in the query plan information about the current table ** and the index used to access it (if any). If the table itself | > > > | 4417 4418 4419 4420 4421 4422 4423 4424 4425 4426 4427 4428 4429 4430 4431 4432 4433 | /* Generate the code to do the search. Each iteration of the for ** loop below generates code for a single nested loop of the VM ** program. */ notReady = ~(Bitmask)0; for(i=0; i<nTabList; i++){ if( (wctrlFlags&WHERE_ONETABLE_ONLY)==0 ){ codeOneLoopExplain(pParse, pTabList, &pWInfo->a[i],i,pWInfo->a[i].iFrom); } notReady = codeOneLoopStart(pWInfo, i, wctrlFlags, notReady); pWInfo->iContinue = pWInfo->a[i].addrCont; } #ifdef SQLITE_TEST /* For testing and debugging use only */ /* Record in the query plan information about the current table ** and the index used to access it (if any). If the table itself |
︙ | ︙ |
Added test/eqp.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 94 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 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 | # 2010 November 6 # # 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. # #*********************************************************************** # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix eqp #------------------------------------------------------------------------- # # eqp-1.*: Assorted tests. # eqp-2.*: Tests for single select statements. # eqp-3.*: Select statements that execute sub-selects. # eqp-4.*: Compound select statements. # proc do_eqp_test {name sql res} { set res [list {*}$res] uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] [list $res] } do_execsql_test 1.1 { CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a); CREATE INDEX i2 ON t1(b); CREATE TABLE t2(a, b); CREATE TABLE t3(a, b); } do_eqp_test 1.2 { SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2; } { 0 0 1 {TABLE t1 WITH INDEX i1 (a=?) (~10 rows)} 0 0 1 {TABLE t1 WITH INDEX i2 (b=?) (~10 rows)} 0 1 0 {TABLE t2 (~1000000 rows)} } do_eqp_test 1.3 { SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2; } { 0 0 0 {TABLE t2 (~1000000 rows)} 0 1 1 {TABLE t1 WITH INDEX i1 (a=?) (~10 rows)} 0 1 1 {TABLE t1 WITH INDEX i2 (b=?) (~10 rows)} } do_eqp_test 1.3 { SELECT a FROM t1 ORDER BY a } { 0 0 0 {TABLE t1 WITH COVERING INDEX i1 (~1000000 rows)} } do_eqp_test 1.4 { SELECT a FROM t1 ORDER BY +a } { 0 0 0 {TABLE t1 (~1000000 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 1.5 { SELECT a FROM t1 WHERE a=4 } { 0 0 0 {TABLE t1 WITH COVERING INDEX i1 (a=?) (~10 rows)} } do_eqp_test 1.6 { SELECT DISTINCT count(*) FROM t3 GROUP BY a; } { 0 0 0 {TABLE t3 (~1000000 rows)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} } #------------------------------------------------------------------------- # Test cases eqp-2.* - tests for single select statements. # drop_all_tables do_execsql_test 2.1 { CREATE TABLE t1(x, y); CREATE TABLE t2(x, y); CREATE INDEX t2i1 ON t2(x); } do_eqp_test 2.2.1 { SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1 } { 0 0 0 {TABLE t1 (~1000000 rows)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 2.2.2 { SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1 } { 0 0 0 {TABLE t2 WITH COVERING INDEX t2i1 (~1000000 rows)} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } #------------------------------------------------------------------------- # Test cases eqp-3.* - tests for select statements that use sub-selects. # do_eqp_test 3.1.1 { SELECT (SELECT x FROM t1 AS sub) FROM t1; } { 0 0 0 {TABLE t1 (~1000000 rows)} 1 0 0 {TABLE t1 AS sub (~1000000 rows)} } #------------------------------------------------------------------------- # Test cases eqp-4.* - tests for select statements that use sub-selects. # do_eqp_test 4.1.1 { SELECT * FROM t1 UNION ALL SELECT * FROM t2 } { 1 0 0 {TABLE t1 (~1000000 rows)} 2 0 0 {TABLE t2 (~1000000 rows)} } do_eqp_test 4.1.2 { SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2 } { 1 0 0 {TABLE t1 (~1000000 rows)} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {TABLE t2 (~1000000 rows)} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 4.1.3 { SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2 } { 1 0 0 {TABLE t1 (~1000000 rows)} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {TABLE t2 (~1000000 rows)} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 4.1.4 { SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2 } { 1 0 0 {TABLE t1 (~1000000 rows)} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {TABLE t2 (~1000000 rows)} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 4.1.5 { SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2 } { 1 0 0 {TABLE t1 (~1000000 rows)} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {TABLE t2 (~1000000 rows)} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 4.2.2 { SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1 } { 1 0 0 {TABLE t1 (~1000000 rows)} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {TABLE t2 WITH INDEX t2i1 (~1000000 rows)} } # Todo: Why are the following not the same as the UNION ALL case above? do_eqp_test 4.2.3 { SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1 } { 1 0 0 {TABLE t1 (~1000000 rows)} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {TABLE t2 (~1000000 rows)} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 4.2.4 { SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1 } { 1 0 0 {TABLE t1 (~1000000 rows)} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {TABLE t2 (~1000000 rows)} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 4.2.5 { SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1 } { 1 0 0 {TABLE t1 (~1000000 rows)} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {TABLE t2 (~1000000 rows)} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} } finish_test |