Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Further tweaks to the query planner logic in preparation for adding ORDER BY opt-out for joins. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | qp-enhancements |
Files: | files | file ages | folders |
SHA1: |
53efc10af990d3f293551f3cd8ef2f8b |
User & Date: | drh 2012-09-27 12:05:09.522 |
Context
2012-09-27
| ||
14:11 | Enable ORDER BY clauses that span joins to be optimized out. (check-in: c29538f9b1 user: drh tags: qp-enhancements) | |
12:05 | Further tweaks to the query planner logic in preparation for adding ORDER BY opt-out for joins. (check-in: 53efc10af9 user: drh tags: qp-enhancements) | |
2012-09-26
| ||
23:17 | Further refactoring of the ORDER BY related query-planning logic in order to make it easier to extend to support optimizing out ORDER BY on joins. No actual behavior changes, yet. (check-in: 96496ddae1 user: drh tags: qp-enhancements) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
3063 3064 3065 3066 3067 3068 3069 | int nOrderBy; /* Number of ORDER BY terms */ WhereTerm *pTerm; /* A single term of the WHERE clause */ #ifdef SQLITE_ENABLE_STAT3 WhereTerm *pFirstTerm = 0; /* First term matching the index */ #endif nOrderBy = p->pOrderBy ? p->pOrderBy->nExpr : 0; | < | | < < < < | 3063 3064 3065 3066 3067 3068 3069 3070 3071 3072 3073 3074 3075 3076 3077 3078 | int nOrderBy; /* Number of ORDER BY terms */ WhereTerm *pTerm; /* A single term of the WHERE clause */ #ifdef SQLITE_ENABLE_STAT3 WhereTerm *pFirstTerm = 0; /* First term matching the index */ #endif nOrderBy = p->pOrderBy ? p->pOrderBy->nExpr : 0; bSort = nOrderBy>0 && (p->i==0 || p->aLevel[p->i-1].plan.nOBSat<nOrderBy); bDist = p->i==0 && p->pDistinct!=0; /* Determine the values of nEq and nInMul */ for(nEq=nOrdered=0; nEq<pProbe->nColumn; nEq++){ int j = pProbe->aiColumn[nEq]; pTerm = findTerm(pWC, iCur, j, p->notReady, eqTermMask, pIdx); if( pTerm==0 ) break; wsFlags |= (WHERE_COLUMN_EQ|WHERE_ROWID_EQ); |
︙ | ︙ | |||
3365 3366 3367 3368 3369 3370 3371 | } if( nRow<2 ) nRow = 2; } WHERETRACE(( "%s(%s): nEq=%d nInMul=%d rangeDiv=%d bSort=%d bLookup=%d wsFlags=0x%x\n" | | | | 3360 3361 3362 3363 3364 3365 3366 3367 3368 3369 3370 3371 3372 3373 3374 3375 | } if( nRow<2 ) nRow = 2; } WHERETRACE(( "%s(%s): nEq=%d nInMul=%d rangeDiv=%d bSort=%d bLookup=%d wsFlags=0x%x\n" " notReady=0x%llx log10N=%.1f nRow=%.1f cost=%.1f\n" " used=0x%llx nOrdered=%d nOBSat=%d\n", pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk"), nEq, nInMul, (int)rangeDiv, bSort, bLookup, wsFlags, p->notReady, log10N, nRow, cost, used, nOrdered, nOBSat )); /* If this index is the best we have seen so far, then record this ** index and its cost in the pCost structure. |
︙ | ︙ | |||
5020 5021 5022 5023 5024 5025 5026 | && (nUnconstrained==0 || sWBI.pSrc->pIndex==0 /* (3) */ || NEVER((sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)) && (bestJ<0 || sWBI.cost.rCost<bestPlan.rCost /* (4) */ || (sWBI.cost.rCost<=bestPlan.rCost && sWBI.cost.plan.nRow<bestPlan.plan.nRow)) ){ WHERETRACE(("=== table %d is best so far" | | | > | | > | 5015 5016 5017 5018 5019 5020 5021 5022 5023 5024 5025 5026 5027 5028 5029 5030 5031 5032 5033 5034 5035 5036 5037 5038 5039 5040 5041 5042 5043 | && (nUnconstrained==0 || sWBI.pSrc->pIndex==0 /* (3) */ || NEVER((sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)) && (bestJ<0 || sWBI.cost.rCost<bestPlan.rCost /* (4) */ || (sWBI.cost.rCost<=bestPlan.rCost && sWBI.cost.plan.nRow<bestPlan.plan.nRow)) ){ WHERETRACE(("=== table %d is best so far" " with cost=%.1f, nRow=%.1f, nOBSat=%d\n", j, sWBI.cost.rCost, sWBI.cost.plan.nRow, sWBI.cost.plan.nOBSat)); bestPlan = sWBI.cost; bestJ = j; } if( doNotReorder ) break; } } assert( bestJ>=0 ); assert( sWBI.notValid & getMask(pMaskSet, pTabList->a[bestJ].iCursor) ); WHERETRACE(("*** Optimizer selects table %d for loop %d" " with cost=%.1f, nRow=%.1f, nOBSat=%d\n", bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow, bestPlan.plan.nOBSat)); if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){ pWInfo->nOBSat = pOrderBy->nExpr; } if( (bestPlan.plan.wsFlags & WHERE_DISTINCT)!=0 ){ assert( pWInfo->eDistinct==0 ); pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; } |
︙ | ︙ |