/ Check-in [2d9de154]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Small tweaks to the query planner to get it to try harder to use an index for sorting and to avoid using automatic indexes on constant terms.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | branch-3.8.3
Files: files | file ages | folders
SHA1:2d9de154f8f398d66693a4bc35eef1353ae61728
User & Date: drh 2014-06-17 15:13:04
Context
2014-06-17
15:13
Small tweaks to the query planner to get it to try harder to use an index for sorting and to avoid using automatic indexes on constant terms. Leaf check-in: 2d9de154 user: drh tags: branch-3.8.3
2014-02-11
14:52
Version 3.8.3.1 check-in: ea3317a4 user: drh tags: release, version-3.8.3.1, branch-3.8.3
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

4209
4210
4211
4212
4213
4214
4215

4216
4217
4218
4219
4220
4221
4222
....
4968
4969
4970
4971
4972
4973
4974
4975
4976
4977
4978
4979
4980
4981
4982
   && !pSrc->isRecursive
  ){
    /* Generate auto-index WhereLoops */
    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.nSkip = 0;
        pNew->u.btree.pIndex = 0;
        pNew->nLTerm = 1;
        pNew->aLTerm[0] = pTerm;
        /* TUNING: One-time cost for computing the automatic index is
................................................................................
  if( pWInfo->pOrderBy==0 || nRowEst==0 ){
    aFrom[0].isOrderedValid = 1;
  }else{
    /* TUNING: Estimated cost of sorting is 48*N*log2(N) where N is the
    ** number of output rows. The 48 is the expected size of a row to sort. 
    ** FIXME:  compute a better estimate of the 48 multiplier based on the
    ** result set expressions. */
    rSortCost = nRowEst + estLog(nRowEst);
    WHERETRACE(0x002,("---- sort cost=%-3d\n", rSortCost));
  }

  /* Compute successively longer WherePaths using the previous generation
  ** of WherePaths as the basis for the next.  Keep track of the mxChoice
  ** best paths at each generation */
  for(iLoop=0; iLoop<nLoop; iLoop++){







>







 







|







4209
4210
4211
4212
4213
4214
4215
4216
4217
4218
4219
4220
4221
4222
4223
....
4969
4970
4971
4972
4973
4974
4975
4976
4977
4978
4979
4980
4981
4982
4983
   && !pSrc->isRecursive
  ){
    /* Generate auto-index WhereLoops */
    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( sqlite3ExprIsConstant(pTerm->pExpr->pRight) ) continue;
      if( termCanDriveIndex(pTerm, pSrc, 0) ){
        pNew->u.btree.nEq = 1;
        pNew->u.btree.nSkip = 0;
        pNew->u.btree.pIndex = 0;
        pNew->nLTerm = 1;
        pNew->aLTerm[0] = pTerm;
        /* TUNING: One-time cost for computing the automatic index is
................................................................................
  if( pWInfo->pOrderBy==0 || nRowEst==0 ){
    aFrom[0].isOrderedValid = 1;
  }else{
    /* TUNING: Estimated cost of sorting is 48*N*log2(N) where N is the
    ** number of output rows. The 48 is the expected size of a row to sort. 
    ** FIXME:  compute a better estimate of the 48 multiplier based on the
    ** result set expressions. */
    rSortCost = nRowEst + estLog(nRowEst) + 10;
    WHERETRACE(0x002,("---- sort cost=%-3d\n", rSortCost));
  }

  /* Compute successively longer WherePaths using the previous generation
  ** of WherePaths as the basis for the next.  Keep track of the mxChoice
  ** best paths at each generation */
  for(iLoop=0; iLoop<nLoop; iLoop++){