/ Check-in [40567fdd]
Login

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

Overview
Comment:Continue refining the NGQP
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1: 40567fddd468d00295275af8df09a7a1785e684a
User & Date: drh 2013-06-12 03:48:41
Context
2013-06-12
14:52
Add the "queryplanner" test permutation. Continuing refinements to NGQP. check-in: 25e2cde1 user: drh tags: nextgen-query-plan-exp
03:48
Continue refining the NGQP check-in: 40567fdd user: drh tags: nextgen-query-plan-exp
2013-06-11
18:59
Improved processing of DISTINCT. check-in: ba897100 user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

    70     70     int iLeftJoin;        /* Memory cell used to implement LEFT OUTER JOIN */
    71     71     int iTabCur;          /* The VDBE cursor used to access the table */
    72     72     int iIdxCur;          /* The VDBE cursor used to access pIdx */
    73     73     int addrBrk;          /* Jump here to break out of the loop */
    74     74     int addrNxt;          /* Jump here to start the next IN combination */
    75     75     int addrCont;         /* Jump here to continue with the next loop cycle */
    76     76     int addrFirst;        /* First instruction of interior of the loop */
    77         -  u8 iFrom;       /* FIXME: Which entry in the FROM clause */
           77  +  u8 iFrom;             /* Which entry in the FROM clause */
    78     78     u8 op, p5;            /* Opcode and P5 of the opcode that ends the loop */
    79     79     int p1, p2;           /* Operands of the opcode used to ends the loop */
    80     80     union {               /* Information that depends on plan.wsFlags */
    81     81       struct {
    82     82         int nIn;              /* Number of entries in aInLoop[] */
    83     83         struct InLoop {
    84     84           int iCur;              /* The VDBE cursor used by this IN operator */
................................................................................
  4244   4244       pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
  4245   4245       pNew->rRun = rLogSize;
  4246   4246       if( pTerm->eOperator & WO_IN ){
  4247   4247         Expr *pExpr = pTerm->pExpr;
  4248   4248         pNew->wsFlags |= WHERE_COLUMN_IN;
  4249   4249         if( ExprHasProperty(pExpr, EP_xIsSelect) ){
  4250   4250           /* "x IN (SELECT ...)":  Assume the SELECT returns 25 rows */
  4251         -        nIn = 46;  /* whereCostFromInt(25) */
         4251  +        nIn = 46;  assert( 46==whereCostFromInt(25) );
  4252   4252         }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
  4253   4253           /* "x IN (value, value, ...)" */
  4254   4254           nIn = whereCostFromInt(pExpr->x.pList->nExpr);
  4255   4255         }
  4256   4256         pNew->rRun += nIn;
  4257   4257         pNew->u.btree.nEq++;
  4258   4258         pNew->nOut = nRowEst + nInMul + nIn;
................................................................................
  4268   4268           pNew->wsFlags |= WHERE_ONEROW;
  4269   4269         }
  4270   4270         pNew->u.btree.nEq++;
  4271   4271         pNew->nOut = nRowEst + nInMul;
  4272   4272       }else if( pTerm->eOperator & (WO_ISNULL) ){
  4273   4273         pNew->wsFlags |= WHERE_COLUMN_NULL;
  4274   4274         pNew->u.btree.nEq++;
  4275         -      nIn = 10;  /* Assume IS NULL matches two rows */
         4275  +      nIn = 10;  assert( 10==whereCostFromInt(2) );
  4276   4276         pNew->nOut = nRowEst + nInMul + nIn;
  4277   4277       }else if( pTerm->eOperator & (WO_GT|WO_GE) ){
  4278   4278         pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT;
  4279   4279         pBtm = pTerm;
  4280   4280         pTop = 0;
  4281   4281       }else if( pTerm->eOperator & (WO_LT|WO_LE) ){
  4282   4282         pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT;
................................................................................
  4299   4299         }else if( (pTerm->eOperator & WO_IN)
  4300   4300                &&  !ExprHasProperty(pTerm->pExpr, EP_xIsSelect)  ){
  4301   4301           rc = whereInScanEst(pParse, pProbe, pTerm->pExpr->x.pList, &nOut);
  4302   4302         }
  4303   4303         pNew->nOut = whereCostFromInt(nOut);
  4304   4304       }
  4305   4305   #endif
  4306         -    if( pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK) ){
  4307         -      /* Step cost for each output row */
  4308         -      pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut);
  4309         -    }else{
         4306  +    if( (pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK))==0 ){
  4310   4307         /* Each row involves a step of the index, then a binary search of
  4311   4308         ** the main table */
  4312         -      WhereCost rStepAndSearch = whereCostAdd(10, rLogSize>17 ? rLogSize-17 : 1);
  4313         -      pNew->rRun =  whereCostAdd(pNew->rRun, rStepAndSearch);
         4309  +      pNew->rRun =  whereCostAdd(pNew->rRun, rLogSize>27 ? rLogSize-17 : 10);
  4314   4310       }
         4311  +    /* Step cost for each output row */
         4312  +    pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut);
  4315   4313       /* TBD: Adjust nOut for additional constraints */
  4316   4314       rc = whereLoopInsert(pBuilder, pNew);
  4317   4315       if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0
  4318   4316        && pNew->u.btree.nEq<=pProbe->nColumn
  4319   4317        && pProbe->zName!=0
  4320   4318       ){
  4321   4319         whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn);
................................................................................
  5247   5245       if( pFrom->rCost>aFrom[ii].rCost ) pFrom = &aFrom[ii];
  5248   5246     }
  5249   5247     assert( pWInfo->nLevel==nLoop );
  5250   5248     /* Load the lowest cost path into pWInfo */
  5251   5249     for(iLoop=0; iLoop<nLoop; iLoop++){
  5252   5250       WhereLevel *pLevel = pWInfo->a + iLoop;
  5253   5251       pLevel->pWLoop = pWLoop = pFrom->aLoop[iLoop];
  5254         -    pLevel->iFrom = pWLoop->iTab; /* FIXME: Omit the iFrom field */
         5252  +    pLevel->iFrom = pWLoop->iTab;
  5255   5253       pLevel->iTabCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor;
  5256   5254     }
  5257   5255     if( (pWInfo->wctrlFlags & WHERE_DISTINCTBY)==0 
  5258   5256      && pWInfo->pDistinct
  5259   5257      && nRowEst
  5260   5258     ){
  5261   5259       Bitmask notUsed;
................................................................................
  5568   5566       goto whereBeginError;
  5569   5567     }
  5570   5568   
  5571   5569     /* If the ORDER BY (or GROUP BY) clause contains references to general
  5572   5570     ** expressions, then we won't be able to satisfy it using indices, so
  5573   5571     ** go ahead and disable it now.
  5574   5572     */
  5575         -  if( pOrderBy ){
         5573  +  if( pOrderBy && pDistinct ){
  5576   5574       for(ii=0; ii<pOrderBy->nExpr; ii++){
  5577   5575         Expr *pExpr = sqlite3ExprSkipCollate(pOrderBy->a[ii].pExpr);
  5578   5576         if( pExpr->op!=TK_COLUMN ){
  5579   5577           pWInfo->pOrderBy = pOrderBy = 0;
         5578  +        break;
         5579  +      }else if( pExpr->iColumn<0 ){
  5580   5580           break;
  5581   5581         }
  5582   5582       }
  5583   5583     }
  5584   5584   
  5585   5585     /* Check if the DISTINCT qualifier, if there is one, is redundant. 
  5586   5586     ** If it is, then set pDistinct to NULL and WhereInfo.eDistinct to