/ Check-in [8e5aad37]
Login

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

Overview
Comment:More bug fixes to the WhereLoop generator and the solver in NGQP. Now finds the best plan for TPC-H Q8. This seems to prove the concept, but there is still much work to be done.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1: 8e5aad37529ec3042e3468acf15186f566e2df8a
User & Date: drh 2013-05-08 04:22:59
Context
2013-05-08
14:13
Fix the wholenumber virtual table so that it returns higher costs for unconstrained usage. check-in: ceff8955 user: drh tags: nextgen-query-plan-exp
04:22
More bug fixes to the WhereLoop generator and the solver in NGQP. Now finds the best plan for TPC-H Q8. This seems to prove the concept, but there is still much work to be done. check-in: 8e5aad37 user: drh tags: nextgen-query-plan-exp
03:22
Bug fixes in the solver. check-in: b36034bb user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

   706    706     WhereTerm *pTerm;    /* The term being tested */
   707    707   
   708    708     while( pScan->iEquiv<=pScan->nEquiv ){
   709    709       iCur = pScan->aEquiv[pScan->iEquiv-2];
   710    710       iColumn = pScan->aEquiv[pScan->iEquiv-1];
   711    711       while( (pWC = pScan->pWC)!=0 ){
   712    712         for(pTerm=pWC->a+pScan->k; pScan->k<pWC->nTerm; pScan->k++, pTerm++){
   713         -        if( pTerm->iParent>=0 ){
   714         -          WhereTerm *pParent = &pWC->a[pTerm->iParent];
   715         -          int j;
   716         -          for(j=pScan->iEquiv-4; j>=0; j-=2 ){
   717         -            if( pParent->leftCursor==pScan->aEquiv[j]
   718         -             && pParent->u.leftColumn==pScan->aEquiv[j+1] ) break;
   719         -          }
   720         -          if( j>=0 ) continue;
   721         -        }
   722    713           if( pTerm->leftCursor==iCur && pTerm->u.leftColumn==iColumn ){
   723    714             if( (pTerm->eOperator & WO_EQUIV)!=0
   724    715              && pScan->nEquiv<ArraySize(pScan->aEquiv)
   725    716             ){
   726    717               int j;
   727    718               pX = sqlite3ExprSkipCollate(pTerm->pExpr->pRight);
   728    719               assert( pX->op==TK_COLUMN );
................................................................................
   749    740                 assert(pX->pLeft);
   750    741                 pColl = sqlite3BinaryCompareCollSeq(pWC->pParse,
   751    742                                                     pX->pLeft, pX->pRight);
   752    743                 if( pColl==0 ) pColl = pWC->pParse->db->pDfltColl;
   753    744                 if( sqlite3StrICmp(pColl->zName, pScan->zCollName) ){
   754    745                   continue;
   755    746                 }
          747  +            }
          748  +            if( (pTerm->eOperator & WO_EQ)!=0
          749  +             && (pX = pTerm->pExpr->pRight)->op==TK_COLUMN
          750  +             && pX->iTable==pScan->aEquiv[0]
          751  +             && pX->iColumn==pScan->aEquiv[1]
          752  +            ){
          753  +              continue;
   756    754               }
   757    755               pScan->pCurrent = pTerm;
   758    756               pScan->k++;
   759    757               return pTerm;
   760    758             }
   761    759           }
   762    760         }
................................................................................
  5054   5052   /*
  5055   5053   ** Print a WhereLoop object for debugging purposes
  5056   5054   */
  5057   5055   static void whereLoopPrint(WhereLoop *p, SrcList *pTabList){
  5058   5056     int nb = 2*((pTabList->nSrc+15)/16);
  5059   5057     struct SrcList_item *pItem = pTabList->a + p->iTab;
  5060   5058     Table *pTab = pItem->pTab;
  5061         -  sqlite3DebugPrintf("%02d.%0*llx", p->iTab, nb, p->prereq);
  5062         -  sqlite3DebugPrintf(" %6s",
         5059  +  sqlite3DebugPrintf("%2d.%0*llx.%0*llx",
         5060  +                     p->iTab, nb, p->maskSelf, nb, p->prereq);
         5061  +  sqlite3DebugPrintf(" %8s",
  5063   5062                        pItem->zAlias ? pItem->zAlias : pTab->zName);
  5064   5063     if( p->pIndex ){
  5065         -    sqlite3DebugPrintf(".%-8s %2d", p->pIndex->zName, p->nEq);
         5064  +    sqlite3DebugPrintf(".%-12s %2d", p->pIndex->zName, p->nEq);
  5066   5065     }else{
  5067         -    sqlite3DebugPrintf("%12s","");
         5066  +    sqlite3DebugPrintf("%16s","");
  5068   5067     }
  5069   5068     sqlite3DebugPrintf(" fg %08x OB %d,%d N %2d",
  5070   5069                        p->wsFlags, p->iOb, p->nOb, p->nTerm);
  5071   5070     sqlite3DebugPrintf(" cost %.4g,%.4g,%.4g\n",
  5072   5071                        p->prereq, p->rSetup, p->rRun, p->nOut);
  5073   5072   }
  5074   5073   #endif
................................................................................
  5278   5277     struct SrcList_item *pSrc;  /* The FROM clause btree term to add */
  5279   5278     sqlite3 *db;                /* The database connection */
  5280   5279     WhereLoop *pNew;            /* Template WhereLoop object */
  5281   5280   
  5282   5281     pNew = pBuilder->pNew;
  5283   5282     db = pBuilder->db;
  5284   5283     pSrc = pBuilder->pTabList->a + iTab;
  5285         -  pNew->maskSelf = getMask(pBuilder->pWC->pMaskSet, iTab);
         5284  +  pNew->maskSelf = getMask(pBuilder->pWC->pMaskSet, pSrc->iCursor);
  5286   5285   
  5287   5286     if( pSrc->pIndex ){
  5288   5287       /* An INDEXED BY clause specifies a particular index to use */
  5289   5288       pProbe = pSrc->pIndex;
  5290   5289     }else{
  5291   5290       /* There is no INDEXED BY clause.  Create a fake Index object in local
  5292   5291       ** variable sPk to represent the rowid primary key index.  Make this
................................................................................
  5456   5455           maskNew = pFrom->maskLoop | pWLoop->maskSelf;
  5457   5456           for(jj=0, pTo=aTo; jj<nTo && pTo->maskLoop!=maskNew; jj++){}
  5458   5457           if( jj>=nTo ){
  5459   5458             if( nTo>=mxChoice && rCost>=mxCost ) continue;
  5460   5459             if( nTo<mxChoice ){
  5461   5460               jj = nTo++;
  5462   5461             }else{
  5463         -            for(jj=nTo-1; aTo[jj].rCost>=mxCost; jj++){ assert(jj>0); }
         5462  +            for(jj=nTo-1; aTo[jj].rCost>=mxCost; jj--){ assert(jj>0); }
  5464   5463             }
  5465   5464             pTo = &aTo[jj];
  5466   5465           }else{
  5467   5466             if( pTo->rCost<=rCost ) continue;
  5468   5467           }
  5469   5468           pTo->maskLoop = pFrom->maskLoop | pWLoop->maskSelf;
  5470   5469           pTo->nRow = pFrom->nRow * pWLoop->nOut;