/ 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 Unified Diffs Show Whitespace Changes Patch

Changes to src/where.c.

3028
3029
3030
3031
3032
3033
3034





3035
3036
3037
3038
3039
3040
3041
....
3076
3077
3078
3079
3080
3081
3082











3083
3084
3085
3086
3087
3088
3089
....
3154
3155
3156
3157
3158
3159
3160
3161
3162
3163
3164
3165
3166
3167
3168
3169
3170
3171
3172
3173
3174
3175
3176
3177
3178
3179
3180
3181
3182
3183
3184
3185
3186
3187
3188
3189
3190
3191
3192
  Index *pIdx;                /* Copy of pProbe, or zero for IPK index */
  int eqTermMask;             /* Current mask of valid equality operators */
  int idxEqTermMask;          /* Index mask of valid equality operators */
  Index sPk;                  /* A fake index object for the primary key */
  tRowcnt aiRowEstPk[2];      /* The aiRowEst[] value for the sPk index */
  int aiColumnPk = -1;        /* The aColumn[] value for the sPk index */
  int wsFlagMask;             /* Allowed flags in p->cost.plan.wsFlag */






  /* Initialize the cost to a worst-case value */
  memset(&p->cost, 0, sizeof(p->cost));
  p->cost.rCost = SQLITE_BIG_DBL;

  /* If the pSrc table is the right table of a LEFT JOIN then we may not
  ** use an index to satisfy IS NULL constraints on that table.  This is
................................................................................
    pProbe = &sPk;
    wsFlagMask = ~(
        WHERE_COLUMN_IN|WHERE_COLUMN_EQ|WHERE_COLUMN_NULL|WHERE_COLUMN_RANGE
    );
    eqTermMask = WO_EQ|WO_IN;
    pIdx = 0;
  }












  /* Loop over all indices looking for the best one to use
  */
  for(; pProbe; pIdx=pProbe=pProbe->pNext){
    const tRowcnt * const aiRowEst = pProbe->aiRowEst;
    WhereCost pc;               /* Cost of using pProbe */
    double log10N = (double)1;  /* base-10 logarithm of nRow (inexact) */
................................................................................
    **             SELECT a, b    FROM tbl WHERE a = 1;
    **             SELECT a, b, c FROM tbl WHERE a = 1;
    */
    int bInEst = 0;               /* True if "x IN (SELECT...)" seen */
    int nInMul = 1;               /* Number of distinct equalities to lookup */
    double rangeDiv = (double)1;  /* Estimated reduction in search space */
    int nBound = 0;               /* Number of range constraints seen */
    int bSort;                    /* True if external sort required */
    int bDist;                    /* True if index cannot help with DISTINCT */
    int bLookup = 0;              /* True if not a covering index */
    int nPriorSat;                /* ORDER BY terms satisfied by outer loops */
    int nOrderBy;                 /* Number of ORDER BY terms */
    WhereTerm *pTerm;             /* A single term of the WHERE clause */
#ifdef SQLITE_ENABLE_STAT3
    WhereTerm *pFirstTerm = 0;    /* First term matching the index */
#endif

    WHERETRACE((
      "   %s(%s):\n",
      pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk")
    ));
    memset(&pc, 0, sizeof(pc));
    nOrderBy = p->pOrderBy ? p->pOrderBy->nExpr : 0;
    if( p->i ){
      nPriorSat = pc.plan.nOBSat = p->aLevel[p->i-1].plan.nOBSat;
      bSort = nPriorSat<nOrderBy;
      bDist = 0;
    }else{
      nPriorSat = pc.plan.nOBSat = 0;
      bSort = nOrderBy>0;
      bDist = p->pDistinct!=0;
    }

    /* Determine the values of pc.plan.nEq and nInMul */
    for(pc.plan.nEq=0; pc.plan.nEq<pProbe->nColumn; pc.plan.nEq++){
      int j = pProbe->aiColumn[pc.plan.nEq];
      pTerm = findTerm(pWC, iCur, j, p->notReady, eqTermMask, pIdx);
      if( pTerm==0 ) break;
      pc.plan.wsFlags |= (WHERE_COLUMN_EQ|WHERE_ROWID_EQ);







>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>







 







|
|
|
<
<










<
<
<
<
<
<
|
<
<
<







3028
3029
3030
3031
3032
3033
3034
3035
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
....
3081
3082
3083
3084
3085
3086
3087
3088
3089
3090
3091
3092
3093
3094
3095
3096
3097
3098
3099
3100
3101
3102
3103
3104
3105
....
3170
3171
3172
3173
3174
3175
3176
3177
3178
3179


3180
3181
3182
3183
3184
3185
3186
3187
3188
3189






3190



3191
3192
3193
3194
3195
3196
3197
  Index *pIdx;                /* Copy of pProbe, or zero for IPK index */
  int eqTermMask;             /* Current mask of valid equality operators */
  int idxEqTermMask;          /* Index mask of valid equality operators */
  Index sPk;                  /* A fake index object for the primary key */
  tRowcnt aiRowEstPk[2];      /* The aiRowEst[] value for the sPk index */
  int aiColumnPk = -1;        /* The aColumn[] value for the sPk index */
  int wsFlagMask;             /* Allowed flags in p->cost.plan.wsFlag */
  int nPriorSat;              /* ORDER BY terms satisfied by outer loops */
  int nOrderBy;               /* Number of ORDER BY terms */
  char bSortInit;             /* Initializer for bSort in inner loop */
  char bDistInit;             /* Initializer for bDist in inner loop */


  /* Initialize the cost to a worst-case value */
  memset(&p->cost, 0, sizeof(p->cost));
  p->cost.rCost = SQLITE_BIG_DBL;

  /* If the pSrc table is the right table of a LEFT JOIN then we may not
  ** use an index to satisfy IS NULL constraints on that table.  This is
................................................................................
    pProbe = &sPk;
    wsFlagMask = ~(
        WHERE_COLUMN_IN|WHERE_COLUMN_EQ|WHERE_COLUMN_NULL|WHERE_COLUMN_RANGE
    );
    eqTermMask = WO_EQ|WO_IN;
    pIdx = 0;
  }

  nOrderBy = p->pOrderBy ? p->pOrderBy->nExpr : 0;
  if( p->i ){
    nPriorSat = p->aLevel[p->i-1].plan.nOBSat;
    bSortInit = nPriorSat<nOrderBy;
    bDistInit = 0;
  }else{
    nPriorSat = 0;
    bSortInit = nOrderBy>0;
    bDistInit = p->pDistinct!=0;
  }

  /* Loop over all indices looking for the best one to use
  */
  for(; pProbe; pIdx=pProbe=pProbe->pNext){
    const tRowcnt * const aiRowEst = pProbe->aiRowEst;
    WhereCost pc;               /* Cost of using pProbe */
    double log10N = (double)1;  /* base-10 logarithm of nRow (inexact) */
................................................................................
    **             SELECT a, b    FROM tbl WHERE a = 1;
    **             SELECT a, b, c FROM tbl WHERE a = 1;
    */
    int bInEst = 0;               /* True if "x IN (SELECT...)" seen */
    int nInMul = 1;               /* Number of distinct equalities to lookup */
    double rangeDiv = (double)1;  /* Estimated reduction in search space */
    int nBound = 0;               /* Number of range constraints seen */
    char bSort = bSortInit;       /* True if external sort required */
    char bDist = bDistInit;       /* True if index cannot help with DISTINCT */
    char bLookup = 0;             /* True if not a covering index */


    WhereTerm *pTerm;             /* A single term of the WHERE clause */
#ifdef SQLITE_ENABLE_STAT3
    WhereTerm *pFirstTerm = 0;    /* First term matching the index */
#endif

    WHERETRACE((
      "   %s(%s):\n",
      pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk")
    ));
    memset(&pc, 0, sizeof(pc));






    pc.plan.nOBSat = nPriorSat;




    /* Determine the values of pc.plan.nEq and nInMul */
    for(pc.plan.nEq=0; pc.plan.nEq<pProbe->nColumn; pc.plan.nEq++){
      int j = pProbe->aiColumn[pc.plan.nEq];
      pTerm = findTerm(pWC, iCur, j, p->notReady, eqTermMask, pIdx);
      if( pTerm==0 ) break;
      pc.plan.wsFlags |= (WHERE_COLUMN_EQ|WHERE_ROWID_EQ);