/ Check-in [707f0323]
Login

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

Overview
Comment:Allow tracing of whereLoopInsert() when the 0x8 bit is set on ".wheretrace". Remove the use of sqlite_query_plan from where2.test. Fix a bug in the code generator for OR clause optimizations.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1:707f0323264c35be14847a6adc49a0dc5eaf4ad2
User & Date: drh 2013-05-31 15:18:46
Context
2013-05-31
15:50
Remove the definitions of objects that are no longer used: WhereCost, WherePlan, and WhereBestIdx. check-in: 816f8add user: drh tags: nextgen-query-plan-exp
15:18
Allow tracing of whereLoopInsert() when the 0x8 bit is set on ".wheretrace". Remove the use of sqlite_query_plan from where2.test. Fix a bug in the code generator for OR clause optimizations. check-in: 707f0323 user: drh tags: nextgen-query-plan-exp
14:31
Enhance the shell to provide more flexibility when entering numeric arguments on dot-commands. In particular, allow hex arguments to .wheretrace. check-in: b9578c37 user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3754   3754     sqlite3DebugPrintf("%c %2d.%0*llx.%0*llx", p->cId,
  3755   3755                        p->iTab, nb, p->maskSelf, nb, p->prereq);
  3756   3756     sqlite3DebugPrintf(" %8s",
  3757   3757                        pItem->zAlias ? pItem->zAlias : pTab->zName);
  3758   3758     if( (p->wsFlags & WHERE_VIRTUALTABLE)==0 ){
  3759   3759       if( p->u.btree.pIndex ){
  3760   3760         const char *zName = p->u.btree.pIndex->zName;
         3761  +      if( zName==0 ) zName = "ipk";
  3761   3762         if( strncmp(zName, "sqlite_autoindex_", 17)==0 ){
  3762   3763           int i = sqlite3Strlen30(zName) - 1;
  3763   3764           while( zName[i]!='_' ) i--;
  3764   3765           zName += i;
  3765   3766         }
  3766   3767         sqlite3DebugPrintf(".%-12s %2d", zName, p->u.btree.nEq);
  3767   3768       }else{
................................................................................
  3861   3862     ** best WhereLoop.  pBuilder->pBest->maskSelf==0 indicates that no
  3862   3863     ** prior WhereLoops have been evaluated and that the current pTemplate
  3863   3864     ** is therefore the first and hence the best and should be retained.
  3864   3865     */
  3865   3866     if( (p = pBuilder->pBest)!=0 ){
  3866   3867       if( p->maskSelf!=0 ){
  3867   3868         if( p->rRun+p->rSetup < pTemplate->rRun+pTemplate->rSetup ){
  3868         -        return SQLITE_OK;
         3869  +        goto whereLoopInsert_noop;
  3869   3870         }
  3870   3871         if( p->rRun+p->rSetup == pTemplate->rRun+pTemplate->rSetup
  3871   3872          && p->prereq <= pTemplate->prereq ){
  3872         -        return SQLITE_OK;
         3873  +        goto whereLoopInsert_noop;
  3873   3874         }
  3874   3875       }
  3875   3876       *p = *pTemplate;
  3876   3877       p->aTerm = 0;
  3877   3878       p->u.vtab.needFree = 0;
         3879  +#if WHERETRACE_ENABLED
         3880  +    if( sqlite3WhereTrace & 0x8 ){
         3881  +      sqlite3DebugPrintf("ins-best: ");
         3882  +      whereLoopPrint(pTemplate, pBuilder->pTabList);
         3883  +    }
         3884  +#endif
  3878   3885       return SQLITE_OK;
  3879   3886     }
  3880   3887   
  3881   3888     /* Search for an existing WhereLoop to overwrite, or which takes
  3882   3889     ** priority over pTemplate.
  3883   3890     */
  3884   3891     for(ppPrev=&pWInfo->pLoops, p=*ppPrev; p; ppPrev=&p->pNextLoop, p=*ppPrev){
................................................................................
  3897   3904       }
  3898   3905       if( (p->prereq & pTemplate->prereq)==p->prereq
  3899   3906        && p->rSetup<=pTemplate->rSetup
  3900   3907        && p->rRun<=pTemplate->rRun
  3901   3908       ){
  3902   3909         /* Already holding an equal or better WhereLoop.
  3903   3910         ** Return without changing or adding anything */
  3904         -      return SQLITE_OK;
         3911  +      goto whereLoopInsert_noop;
  3905   3912       }
  3906   3913       if( (p->prereq & pTemplate->prereq)==pTemplate->prereq
  3907   3914        && p->rSetup>=pTemplate->rSetup
  3908   3915        && p->rRun>=pTemplate->rRun
  3909   3916       ){
  3910   3917         /* Overwrite an existing WhereLoop with a better one */
  3911   3918         pNext = p->pNextLoop;
................................................................................
  3914   3921       }
  3915   3922     }
  3916   3923   
  3917   3924     /* If we reach this point it means that either p[] should be overwritten
  3918   3925     ** with pTemplate[] if p[] exists, or if p==NULL then allocate a new
  3919   3926     ** WhereLoop and insert it.
  3920   3927     */
         3928  +#if WHERETRACE_ENABLED
         3929  +  if( sqlite3WhereTrace & 0x8 ){
         3930  +    if( p!=0 ){
         3931  +      sqlite3DebugPrintf("ins-del:  ");
         3932  +      whereLoopPrint(p, pBuilder->pTabList);
         3933  +    }
         3934  +    sqlite3DebugPrintf("ins-new:  ");
         3935  +    whereLoopPrint(pTemplate, pBuilder->pTabList);
         3936  +  }
         3937  +#endif
  3921   3938     if( p==0 ){
  3922   3939       p = pToFree = sqlite3DbMallocRaw(db, sizeof(WhereLoop));
  3923   3940       if( p==0 ) return SQLITE_NOMEM;
  3924   3941     }
  3925   3942     if( pTemplate->nTerm ){
  3926   3943       paTerm = sqlite3DbMallocRaw(db, pTemplate->nTerm*sizeof(p->aTerm[0]));
  3927   3944       if( paTerm==0 ){
................................................................................
  3941   3958       if( pIndex && pIndex->tnum==0 ){
  3942   3959         p->u.btree.pIndex = 0;
  3943   3960       }
  3944   3961     }else{
  3945   3962       pTemplate->u.vtab.needFree = 0;
  3946   3963     }
  3947   3964     return SQLITE_OK;
         3965  +
         3966  +  /* Jump here if the insert is a no-op */
         3967  +whereLoopInsert_noop:
         3968  +#if WHERETRACE_ENABLED
         3969  +  if( sqlite3WhereTrace & 0x8 ){
         3970  +    sqlite3DebugPrintf("ins-noop: ");
         3971  +    whereLoopPrint(pTemplate, pBuilder->pTabList);
         3972  +  }
         3973  +#endif
         3974  +  return SQLITE_OK;  
  3948   3975   }
  3949   3976   
  3950   3977   /*
  3951   3978   ** We have so far matched pBuilder->pNew->u.btree.nEq terms of the index pIndex.
  3952   3979   ** Try to match one more.
  3953   3980   **
  3954   3981   ** If pProbe->tnum==0, that means pIndex is a fake index used for the
................................................................................
  4152   4179       WhereTerm *pWCEnd = pWC->a + pWC->nTerm;
  4153   4180       for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){
  4154   4181         if( termCanDriveIndex(pTerm, pSrc, 0) ){
  4155   4182           pNew->u.btree.nEq = 1;
  4156   4183           pNew->u.btree.pIndex = 0;
  4157   4184           pNew->nTerm = 1;
  4158   4185           pNew->aTerm[0] = pTerm;
  4159         -        pNew->rSetup = 2*rLogSize*pSrc->pTab->nRowEst;
         4186  +        pNew->rSetup = 20*rLogSize*pSrc->pTab->nRowEst;
  4160   4187           pNew->nOut = (double)10;
  4161   4188           pNew->rRun = rLogSize + pNew->nOut;
  4162   4189           pNew->wsFlags = WHERE_TEMP_INDEX;
  4163   4190           pNew->prereq = mExtra | pTerm->prereqRight;
  4164   4191           rc = whereLoopInsert(pBuilder, pNew);
  4165   4192         }
  4166   4193       }
................................................................................
  4769   4796           for(jj=0, pTo=aTo; jj<nTo; jj++, pTo++){
  4770   4797             if( pTo->maskLoop==maskNew && pTo->isOrderedValid==isOrderedValid ){
  4771   4798               break;
  4772   4799             }
  4773   4800           }
  4774   4801           if( jj>=nTo ){
  4775   4802             if( nTo>=mxChoice && rCost>=mxCost ){
  4776         -#ifdef WHERETRACE_ENABLE
  4777         -            if( sqlite3WhereTrace>=3 ){
         4803  +#ifdef WHERETRACE_ENABLED
         4804  +            if( sqlite3WhereTrace&0x4 ){
  4778   4805                 sqlite3DebugPrintf("Skip   %s cost=%-7.2g order=%c\n",
  4779   4806                     wherePathName(pFrom, iLoop, pWLoop), rCost,
  4780   4807                     isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
  4781   4808               }
  4782   4809   #endif
  4783   4810               continue;
  4784   4811             }
................................................................................
  4788   4815               jj = nTo++;
  4789   4816             }else{
  4790   4817               /* New path replaces the prior worst to keep count below mxChoice */
  4791   4818               for(jj=nTo-1; aTo[jj].rCost<mxCost; jj--){ assert(jj>0); }
  4792   4819             }
  4793   4820             pTo = &aTo[jj];
  4794   4821   #ifdef WHERETRACE_ENABLED
  4795         -          if( sqlite3WhereTrace>=3 ){
         4822  +          if( sqlite3WhereTrace&0x4 ){
  4796   4823               sqlite3DebugPrintf("New    %s cost=%-7.2g order=%c\n",
  4797   4824                   wherePathName(pFrom, iLoop, pWLoop), rCost,
  4798   4825                   isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
  4799   4826             }
  4800   4827   #endif
  4801   4828           }else{
  4802   4829             if( pTo->rCost<=rCost ){
  4803   4830   #ifdef WHERETRACE_ENABLED
  4804         -            if( sqlite3WhereTrace>=3 ){
         4831  +            if( sqlite3WhereTrace&0x4 ){
  4805   4832                 sqlite3DebugPrintf(
  4806   4833                     "Skip   %s cost=%-7.2g order=%c",
  4807   4834                     wherePathName(pFrom, iLoop, pWLoop), rCost,
  4808   4835                     isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
  4809   4836                 sqlite3DebugPrintf("   vs %s cost=%-7.2g order=%c\n",
  4810   4837                     wherePathName(pTo, iLoop+1, 0), pTo->rCost,
  4811   4838                     pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
  4812   4839               }
  4813   4840   #endif
  4814   4841               continue;
  4815   4842             }
  4816   4843             /* A new and better score for a previously created equivalent path */
  4817   4844   #ifdef WHERETRACE_ENABLED
  4818         -          if( sqlite3WhereTrace>=3 ){
         4845  +          if( sqlite3WhereTrace&0x4 ){
  4819   4846               sqlite3DebugPrintf(
  4820   4847                   "Update %s cost=%-7.2g order=%c",
  4821   4848                   wherePathName(pFrom, iLoop, pWLoop), rCost,
  4822   4849                   isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
  4823   4850               sqlite3DebugPrintf("  was %s cost=%-7.2g order=%c\n",
  4824   4851                   wherePathName(pTo, iLoop+1, 0), pTo->rCost,
  4825   4852                   pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
................................................................................
  5235   5262       if( (pLoop->wsFlags & WHERE_TEMP_INDEX)!=0 ){
  5236   5263         constructAutomaticIndex(pParse, sWBI.pWC, pTabItem, notReady, pLevel);
  5237   5264       }else
  5238   5265   #endif
  5239   5266       if( pLoop->u.btree.pIndex!=0 ){
  5240   5267         Index *pIx = pLoop->u.btree.pIndex;
  5241   5268         KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIx);
  5242         -      /* FIXME:  Might need to be the iIdxCur parameter.  As an optimization
  5243         -      ** use pTabItem->iCursor if WHERE_IDX_ONLY */
  5244         -      int iIndexCur = pLevel->iIdxCur = pParse->nTab++;
         5269  +      /* FIXME:  As an optimization use pTabItem->iCursor if WHERE_IDX_ONLY */
         5270  +      int iIndexCur = pLevel->iIdxCur = iIdxCur ? iIdxCur : pParse->nTab++;
  5245   5271         assert( pIx->pSchema==pTab->pSchema );
  5246   5272         assert( iIndexCur>=0 );
  5247   5273         sqlite3VdbeAddOp4(v, OP_OpenRead, iIndexCur, pIx->tnum, iDb,
  5248   5274                           (char*)pKey, P4_KEYINFO_HANDOFF);
  5249   5275         VdbeComment((v, "%s", pIx->zName));
  5250   5276       }
  5251   5277       sqlite3CodeVerifySchema(pParse, iDb);

Changes to test/where2.test.

    69     69   # it appends the ::sqlite_query_plan variable.
    70     70   #
    71     71   proc queryplan {sql} {
    72     72     set ::sqlite_sort_count 0
    73     73     set data [execsql $sql]
    74     74     if {$::sqlite_sort_count} {set x sort} {set x nosort}
    75     75     lappend data $x
    76         -  return [concat $data $::sqlite_query_plan]
           76  +  set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
           77  +  # puts eqp=$eqp
           78  +  foreach {a b c x} $eqp {
           79  +    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
           80  +        $x all as tab idx]} {
           81  +      lappend data $tab $idx
           82  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
           83  +      lappend data $tab *
           84  +    }
           85  +  }
           86  +  return $data   
           87  +  # return [concat $data $::sqlite_query_plan]
    77     88   }
    78     89   
    79     90   
    80     91   # Prefer a UNIQUE index over another index.
    81     92   #
    82     93   do_test where2-1.1 {
    83     94     queryplan {