SQLite

Check-in [e5d9371da9]
Login

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

Overview
Comment:Further refinements of the index scanning speed logic.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | index-scan-rate
Files: files | file ages | folders
SHA1: e5d9371da92b4cc2c1df177cf40e0ced4d4c7421
User & Date: drh 2013-10-04 18:29:25.135
Context
2013-10-04
20:39
Merge trunk changes. (check-in: c6ac80ed8d user: drh tags: index-scan-rate)
18:29
Further refinements of the index scanning speed logic. (check-in: e5d9371da9 user: drh tags: index-scan-rate)
15:58
Fix test cases so that they work when the query planner uses index size estimates to determine whether or not to try an covering index scan. (check-in: 2f394de88f user: drh tags: index-scan-rate)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/analyze.c.
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283

1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
  tRowcnt *aOut,         /* Store integers here */
  Index *pIndex          /* Handle extra flags for this index, if not NULL */
){
  char *z = zIntArray;
  int c;
  int i;
  tRowcnt v;
  int v32;

#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  if( z==0 ) z = "";
#else
  if( NEVER(z==0) ) z = "";
#endif
  for(i=0; *z && i<nOut; i++){
    v = 0;
    while( (c=z[0])>='0' && c<='9' ){
      v = v*10 + c - '0';
      z++;
    }
    aOut[i] = v;
    if( *z==' ' ) z++;
  }
  if( pIndex ){
    while( z[0] ){
      for(i=1; z[i] && z[i]!=' '; i++){}
      if( i==9 && memcmp(z, "unordered", 9)==0 ){
        pIndex->bUnordered = 1;
      }else if( i>2 && memcmp(z, "r=", 2)==0

                && sqlite3GetInt32(z+2, &v32) ){
        if( v32>=200 ){
          v32 = 255;
        }else if( v32<0 ){
          v32 = 0;
        }else{
          v32 = (128*v32)/100;
        }
        pIndex->iScanRatio = (u8)v32;
      }
      z += i;
      if( z[0]==' ' ) z++;
    }
  }
}

/*
** This callback is invoked once for each index when reading the
** sqlite_stat1 table.  







<
















<
<
|
|
|
>
|
|
|
|
|
|
|
|
|
<
<
<







1255
1256
1257
1258
1259
1260
1261

1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277


1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290



1291
1292
1293
1294
1295
1296
1297
  tRowcnt *aOut,         /* Store integers here */
  Index *pIndex          /* Handle extra flags for this index, if not NULL */
){
  char *z = zIntArray;
  int c;
  int i;
  tRowcnt v;


#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  if( z==0 ) z = "";
#else
  if( NEVER(z==0) ) z = "";
#endif
  for(i=0; *z && i<nOut; i++){
    v = 0;
    while( (c=z[0])>='0' && c<='9' ){
      v = v*10 + c - '0';
      z++;
    }
    aOut[i] = v;
    if( *z==' ' ) z++;
  }
  if( pIndex ){


    if( strcmp(z, "unordered")==0 ){
      pIndex->bUnordered = 1;
    }else if( sqlite3_strglob("r=[0-9]*", z)==0 ){
      int v32 = 0;
      sqlite3GetInt32(z+2, &v32);
      if( v32>=200 ){
        v32 = 255;
      }else if( v32<=0 ){
        v32 = 1;
      }else{
        v32 = (128*v32)/100;
      }
      pIndex->iScanRatio = (u8)v32;



    }
  }
}

/*
** This callback is invoked once for each index when reading the
** sqlite_stat1 table.  
Changes to src/build.c.
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089

1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105



1106
1107
1108

1109
1110
1111

1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122

1123
1124
1125
1126
1127
1128
1129
**
** If none of the substrings in the above table are found,
** SQLITE_AFF_NUMERIC is returned.
*/
char sqlite3AffinityType(const char *zIn, u8 *pszEst){
  u32 h = 0;
  char aff = SQLITE_AFF_NUMERIC;
  const char *zChar;

  if( zIn==0 ) return aff;
  while( zIn[0] ){
    h = (h<<8) + sqlite3UpperToLower[(*zIn)&0xff];
    zIn++;
    if( h==(('c'<<24)+('h'<<16)+('a'<<8)+'r') ){             /* CHAR */
      aff = SQLITE_AFF_TEXT;
      zChar = zIn;
    }else if( h==(('c'<<24)+('l'<<16)+('o'<<8)+'b') ){       /* CLOB */
      aff = SQLITE_AFF_TEXT;
    }else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){       /* TEXT */
      aff = SQLITE_AFF_TEXT;
    }else if( h==(('b'<<24)+('l'<<16)+('o'<<8)+'b')          /* BLOB */
        && (aff==SQLITE_AFF_NUMERIC || aff==SQLITE_AFF_REAL) ){
      aff = SQLITE_AFF_NONE;

#ifndef SQLITE_OMIT_FLOATING_POINT
    }else if( h==(('r'<<24)+('e'<<16)+('a'<<8)+'l')          /* REAL */
        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_REAL;
    }else if( h==(('f'<<24)+('l'<<16)+('o'<<8)+'a')          /* FLOA */
        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_REAL;
    }else if( h==(('d'<<24)+('o'<<16)+('u'<<8)+'b')          /* DOUB */
        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_REAL;
#endif
    }else if( (h&0x00FFFFFF)==(('i'<<16)+('n'<<8)+'t') ){    /* INT */
      aff = SQLITE_AFF_INTEGER;
      break;
    }
  }



  if( pszEst ){
    if( aff>=SQLITE_AFF_NUMERIC ){
      *pszEst = 1;

    }else if( zChar ){
      *pszEst = 1;
      while( zChar[0] ){

        int v;
        if( sqlite3Isdigit(zChar[0]) && sqlite3GetInt32(zChar, &v) ){
          v = v/4 + 1;
          if( v>255 ) v = 255;
          *pszEst = v;
          break;
        }
        zChar++;
      }
    }else{
      *pszEst = 3;

    }
  }
  return aff;
}

/*
** This routine is called by the parser while in the middle of







|















>
















>
>
>

<
|
>
|
<
|
>
|
|
|
|
|
|
|
|
|
|
|
>







1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110

1111
1112
1113

1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
**
** If none of the substrings in the above table are found,
** SQLITE_AFF_NUMERIC is returned.
*/
char sqlite3AffinityType(const char *zIn, u8 *pszEst){
  u32 h = 0;
  char aff = SQLITE_AFF_NUMERIC;
  const char *zChar = 0;

  if( zIn==0 ) return aff;
  while( zIn[0] ){
    h = (h<<8) + sqlite3UpperToLower[(*zIn)&0xff];
    zIn++;
    if( h==(('c'<<24)+('h'<<16)+('a'<<8)+'r') ){             /* CHAR */
      aff = SQLITE_AFF_TEXT;
      zChar = zIn;
    }else if( h==(('c'<<24)+('l'<<16)+('o'<<8)+'b') ){       /* CLOB */
      aff = SQLITE_AFF_TEXT;
    }else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){       /* TEXT */
      aff = SQLITE_AFF_TEXT;
    }else if( h==(('b'<<24)+('l'<<16)+('o'<<8)+'b')          /* BLOB */
        && (aff==SQLITE_AFF_NUMERIC || aff==SQLITE_AFF_REAL) ){
      aff = SQLITE_AFF_NONE;
      if( zIn[0]=='(' ) zChar = zIn;
#ifndef SQLITE_OMIT_FLOATING_POINT
    }else if( h==(('r'<<24)+('e'<<16)+('a'<<8)+'l')          /* REAL */
        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_REAL;
    }else if( h==(('f'<<24)+('l'<<16)+('o'<<8)+'a')          /* FLOA */
        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_REAL;
    }else if( h==(('d'<<24)+('o'<<16)+('u'<<8)+'b')          /* DOUB */
        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_REAL;
#endif
    }else if( (h&0x00FFFFFF)==(('i'<<16)+('n'<<8)+'t') ){    /* INT */
      aff = SQLITE_AFF_INTEGER;
      break;
    }
  }

  /* If pszEst is not NULL, store an estimate of the field size.  The
  ** estimate is scaled so that the size of an integer is 1.  */
  if( pszEst ){

    *pszEst = 1;   /* default size is approx 4 bytes */
    if( aff<=SQLITE_AFF_NONE ){
      if( zChar ){

        while( zChar[0] ){
          if( sqlite3Isdigit(zChar[0]) ){
            int v;
            sqlite3GetInt32(zChar, &v);
            v = v/4 + 1;
            if( v>255 ) v = 255;
            *pszEst = v; /* BLOB(k), VARCHAR(k), CHAR(k) -> r=(k/4+1) */
            break;
          }
          zChar++;
        }
      }else{
        *pszEst = 5;   /* BLOB, TEXT, CLOB -> r=5  (approx 20 bytes)*/
      }
    }
  }
  return aff;
}

/*
** This routine is called by the parser while in the middle of
Changes to src/select.c.
4603
4604
4605
4606
4607
4608
4609

4610
4611
4612
4613
4614
4615
4616
        **
        ** In practice the KeyInfo structure will not be used. It is only 
        ** passed to keep OP_OpenRead happy.
        */
        for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          if( pIdx->bUnordered==0
           && pIdx->iScanRatio<128

           && (!pBest || pIdx->iScanRatio<pBest->iScanRatio)
          ){
            pBest = pIdx;
          }
        }
        if( pBest ){
          iRoot = pBest->tnum;







>







4603
4604
4605
4606
4607
4608
4609
4610
4611
4612
4613
4614
4615
4616
4617
        **
        ** In practice the KeyInfo structure will not be used. It is only 
        ** passed to keep OP_OpenRead happy.
        */
        for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          if( pIdx->bUnordered==0
           && pIdx->iScanRatio<128
           && pIdx->pPartIdxWhere==0
           && (!pBest || pIdx->iScanRatio<pBest->iScanRatio)
          ){
            pBest = pIdx;
          }
        }
        if( pBest ){
          iRoot = pBest->tnum;