/ Check-in [53efc10a]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | qp-enhancements
Files: files | file ages | folders
SHA1: 53efc10af990d3f293551f3cd8ef2f8be2d9d716
User & Date: drh 2012-09-27 12:05:09
Context
2012-09-27
14:11
Enable ORDER BY clauses that span joins to be optimized out. check-in: c29538f9 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: 53efc10a 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: 96496dda user: drh tags: qp-enhancements
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/where.c.

  3063   3063       int nOrderBy;                 /* Number of ORDER BY terms */
  3064   3064       WhereTerm *pTerm;             /* A single term of the WHERE clause */
  3065   3065   #ifdef SQLITE_ENABLE_STAT3
  3066   3066       WhereTerm *pFirstTerm = 0;    /* First term matching the index */
  3067   3067   #endif
  3068   3068   
  3069   3069       nOrderBy = p->pOrderBy ? p->pOrderBy->nExpr : 0;
  3070         -    if( (p->i) > 0 ){
  3071         -      bSort = 0;
  3072         -      bDist = 0;
  3073         -    }else{
  3074         -      bSort = p->pOrderBy!=0;
  3075         -      bDist = p->pDistinct!=0;
  3076         -    }
         3070  +    bSort = nOrderBy>0 && (p->i==0 || p->aLevel[p->i-1].plan.nOBSat<nOrderBy);
         3071  +    bDist = p->i==0 && p->pDistinct!=0;
  3077   3072   
  3078   3073       /* Determine the values of nEq and nInMul */
  3079   3074       for(nEq=nOrdered=0; nEq<pProbe->nColumn; nEq++){
  3080   3075         int j = pProbe->aiColumn[nEq];
  3081   3076         pTerm = findTerm(pWC, iCur, j, p->notReady, eqTermMask, pIdx);
  3082   3077         if( pTerm==0 ) break;
  3083   3078         wsFlags |= (WHERE_COLUMN_EQ|WHERE_ROWID_EQ);
................................................................................
  3365   3360         }
  3366   3361         if( nRow<2 ) nRow = 2;
  3367   3362       }
  3368   3363   
  3369   3364   
  3370   3365       WHERETRACE((
  3371   3366         "%s(%s): nEq=%d nInMul=%d rangeDiv=%d bSort=%d bLookup=%d wsFlags=0x%x\n"
  3372         -      "         notReady=0x%llx log10N=%.1f nRow=%.1f cost=%.1f used=0x%llx\n"
  3373         -      "         nOrdered=%d nOBSat=%d\n",
         3367  +      "         notReady=0x%llx log10N=%.1f nRow=%.1f cost=%.1f\n"
         3368  +      "         used=0x%llx nOrdered=%d nOBSat=%d\n",
  3374   3369         pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk"), 
  3375   3370         nEq, nInMul, (int)rangeDiv, bSort, bLookup, wsFlags,
  3376   3371         p->notReady, log10N, nRow, cost, used, nOrdered, nOBSat
  3377   3372       ));
  3378   3373   
  3379   3374       /* If this index is the best we have seen so far, then record this
  3380   3375       ** index and its cost in the pCost structure.
................................................................................
  5020   5015               && (nUnconstrained==0 || sWBI.pSrc->pIndex==0        /* (3) */
  5021   5016                   || NEVER((sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0))
  5022   5017               && (bestJ<0 || sWBI.cost.rCost<bestPlan.rCost        /* (4) */
  5023   5018                   || (sWBI.cost.rCost<=bestPlan.rCost 
  5024   5019                    && sWBI.cost.plan.nRow<bestPlan.plan.nRow))
  5025   5020           ){
  5026   5021             WHERETRACE(("=== table %d is best so far"
  5027         -                      " with cost=%g and nRow=%g\n",
  5028         -                      j, sWBI.cost.rCost, sWBI.cost.plan.nRow));
         5022  +                      " with cost=%.1f, nRow=%.1f, nOBSat=%d\n",
         5023  +                      j, sWBI.cost.rCost, sWBI.cost.plan.nRow,
         5024  +                      sWBI.cost.plan.nOBSat));
  5029   5025             bestPlan = sWBI.cost;
  5030   5026             bestJ = j;
  5031   5027           }
  5032   5028           if( doNotReorder ) break;
  5033   5029         }
  5034   5030       }
  5035   5031       assert( bestJ>=0 );
  5036   5032       assert( sWBI.notValid & getMask(pMaskSet, pTabList->a[bestJ].iCursor) );
  5037   5033       WHERETRACE(("*** Optimizer selects table %d for loop %d"
  5038         -                " with cost=%g and nRow=%g\n",
  5039         -                bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow));
         5034  +                " with cost=%.1f, nRow=%.1f, nOBSat=%d\n",
         5035  +                bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow,
         5036  +                bestPlan.plan.nOBSat));
  5040   5037       if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){
  5041   5038         pWInfo->nOBSat = pOrderBy->nExpr;
  5042   5039       }
  5043   5040       if( (bestPlan.plan.wsFlags & WHERE_DISTINCT)!=0 ){
  5044   5041         assert( pWInfo->eDistinct==0 );
  5045   5042         pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
  5046   5043       }