/ Check-in [92c9ab56]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Factor some work out of the index loop of the bestBtreeIndex() routine for a small performance increase.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 92c9ab56b1c67b9468bec57ab1d2c483a69a2810
User & Date: drh 2012-12-08 22:14:29
Context
2012-12-10
09:08
Remove a reference to JumpOnce from a comment in vdbe.c. No code changes. check-in: ee662c03 user: dan tags: trunk
2012-12-08
23:37
Pull the latest trunk changes into the sessions branch, and in particular the collating-sequence refactorization. check-in: 4f6d69ae user: drh tags: sessions
22:14
Factor some work out of the index loop of the bestBtreeIndex() routine for a small performance increase. check-in: 92c9ab56 user: drh tags: trunk
21:51
Refactor collating-sequence handling as a fix for ticket [71e333e7d2e642]. The Expr.pColl field is removed from the Expr object. The COLLATE operator now becomes a separate instance of Expr in the expression tree. The code generator looks up the correct collating function as needed, rather than referring to Expr.pColl. check-in: 8542e618 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3028   3028     Index *pIdx;                /* Copy of pProbe, or zero for IPK index */
  3029   3029     int eqTermMask;             /* Current mask of valid equality operators */
  3030   3030     int idxEqTermMask;          /* Index mask of valid equality operators */
  3031   3031     Index sPk;                  /* A fake index object for the primary key */
  3032   3032     tRowcnt aiRowEstPk[2];      /* The aiRowEst[] value for the sPk index */
  3033   3033     int aiColumnPk = -1;        /* The aColumn[] value for the sPk index */
  3034   3034     int wsFlagMask;             /* Allowed flags in p->cost.plan.wsFlag */
         3035  +  int nPriorSat;              /* ORDER BY terms satisfied by outer loops */
         3036  +  int nOrderBy;               /* Number of ORDER BY terms */
         3037  +  char bSortInit;             /* Initializer for bSort in inner loop */
         3038  +  char bDistInit;             /* Initializer for bDist in inner loop */
         3039  +
  3035   3040   
  3036   3041     /* Initialize the cost to a worst-case value */
  3037   3042     memset(&p->cost, 0, sizeof(p->cost));
  3038   3043     p->cost.rCost = SQLITE_BIG_DBL;
  3039   3044   
  3040   3045     /* If the pSrc table is the right table of a LEFT JOIN then we may not
  3041   3046     ** use an index to satisfy IS NULL constraints on that table.  This is
................................................................................
  3076   3081       pProbe = &sPk;
  3077   3082       wsFlagMask = ~(
  3078   3083           WHERE_COLUMN_IN|WHERE_COLUMN_EQ|WHERE_COLUMN_NULL|WHERE_COLUMN_RANGE
  3079   3084       );
  3080   3085       eqTermMask = WO_EQ|WO_IN;
  3081   3086       pIdx = 0;
  3082   3087     }
         3088  +
         3089  +  nOrderBy = p->pOrderBy ? p->pOrderBy->nExpr : 0;
         3090  +  if( p->i ){
         3091  +    nPriorSat = p->aLevel[p->i-1].plan.nOBSat;
         3092  +    bSortInit = nPriorSat<nOrderBy;
         3093  +    bDistInit = 0;
         3094  +  }else{
         3095  +    nPriorSat = 0;
         3096  +    bSortInit = nOrderBy>0;
         3097  +    bDistInit = p->pDistinct!=0;
         3098  +  }
  3083   3099   
  3084   3100     /* Loop over all indices looking for the best one to use
  3085   3101     */
  3086   3102     for(; pProbe; pIdx=pProbe=pProbe->pNext){
  3087   3103       const tRowcnt * const aiRowEst = pProbe->aiRowEst;
  3088   3104       WhereCost pc;               /* Cost of using pProbe */
  3089   3105       double log10N = (double)1;  /* base-10 logarithm of nRow (inexact) */
................................................................................
  3154   3170       **             SELECT a, b    FROM tbl WHERE a = 1;
  3155   3171       **             SELECT a, b, c FROM tbl WHERE a = 1;
  3156   3172       */
  3157   3173       int bInEst = 0;               /* True if "x IN (SELECT...)" seen */
  3158   3174       int nInMul = 1;               /* Number of distinct equalities to lookup */
  3159   3175       double rangeDiv = (double)1;  /* Estimated reduction in search space */
  3160   3176       int nBound = 0;               /* Number of range constraints seen */
  3161         -    int bSort;                    /* True if external sort required */
  3162         -    int bDist;                    /* True if index cannot help with DISTINCT */
  3163         -    int bLookup = 0;              /* True if not a covering index */
  3164         -    int nPriorSat;                /* ORDER BY terms satisfied by outer loops */
  3165         -    int nOrderBy;                 /* Number of ORDER BY terms */
         3177  +    char bSort = bSortInit;       /* True if external sort required */
         3178  +    char bDist = bDistInit;       /* True if index cannot help with DISTINCT */
         3179  +    char bLookup = 0;             /* True if not a covering index */
  3166   3180       WhereTerm *pTerm;             /* A single term of the WHERE clause */
  3167   3181   #ifdef SQLITE_ENABLE_STAT3
  3168   3182       WhereTerm *pFirstTerm = 0;    /* First term matching the index */
  3169   3183   #endif
  3170   3184   
  3171   3185       WHERETRACE((
  3172   3186         "   %s(%s):\n",
  3173   3187         pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk")
  3174   3188       ));
  3175   3189       memset(&pc, 0, sizeof(pc));
  3176         -    nOrderBy = p->pOrderBy ? p->pOrderBy->nExpr : 0;
  3177         -    if( p->i ){
  3178         -      nPriorSat = pc.plan.nOBSat = p->aLevel[p->i-1].plan.nOBSat;
  3179         -      bSort = nPriorSat<nOrderBy;
  3180         -      bDist = 0;
  3181         -    }else{
  3182         -      nPriorSat = pc.plan.nOBSat = 0;
  3183         -      bSort = nOrderBy>0;
  3184         -      bDist = p->pDistinct!=0;
  3185         -    }
         3190  +    pc.plan.nOBSat = nPriorSat;
  3186   3191   
  3187   3192       /* Determine the values of pc.plan.nEq and nInMul */
  3188   3193       for(pc.plan.nEq=0; pc.plan.nEq<pProbe->nColumn; pc.plan.nEq++){
  3189   3194         int j = pProbe->aiColumn[pc.plan.nEq];
  3190   3195         pTerm = findTerm(pWC, iCur, j, p->notReady, eqTermMask, pIdx);
  3191   3196         if( pTerm==0 ) break;
  3192   3197         pc.plan.wsFlags |= (WHERE_COLUMN_EQ|WHERE_ROWID_EQ);