SQLite

Check-in [e06dc6f0c3]
Login

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

Overview
Comment:In cases where stat4 data is available but cannot be used because the rhs of a range constraint is too complex a expression, fall back to using the default estimates for number of rows scanned.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e06dc6f0c35f87c44292c71677111b74f073a5c4
User & Date: dan 2014-08-25 18:29:38.998
Context
2014-08-25
20:11
Allow CAST expressions and unary "+" operators to be used in the DEFAULT argument of an ALTER TABLE ADD COLUMN and to be understand on the RHS of range constraints interpreted by STAT3/4. This involves a rewrite of the implementation of the CAST operator. (check-in: 91d8a8d0b7 user: drh tags: trunk)
18:29
In cases where stat4 data is available but cannot be used because the rhs of a range constraint is too complex a expression, fall back to using the default estimates for number of rows scanned. (check-in: e06dc6f0c3 user: dan tags: trunk)
11:33
Remove the pager_lookup() function since it is redundant with sqlite3PagerLookup(). (check-in: 54164ce47c user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
2216
2217
2218
2219
2220
2221
2222

2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237

2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266

2267
2268
2269
2270
2271
2272
2273
        rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk);
        if( rc==SQLITE_OK && bOk ){
          tRowcnt iNew;
          whereKeyStats(pParse, p, pRec, 0, a);
          iNew = a[0] + ((pLower->eOperator & WO_GT) ? a[1] : 0);
          if( iNew>iLower ) iLower = iNew;
          nOut--;

        }
      }

      /* If possible, improve on the iUpper estimate using ($P:$U). */
      if( pUpper ){
        int bOk;                    /* True if value is extracted from pExpr */
        Expr *pExpr = pUpper->pExpr->pRight;
        assert( (pUpper->eOperator & (WO_LT|WO_LE))!=0 );
        rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk);
        if( rc==SQLITE_OK && bOk ){
          tRowcnt iNew;
          whereKeyStats(pParse, p, pRec, 1, a);
          iNew = a[0] + ((pUpper->eOperator & WO_LE) ? a[1] : 0);
          if( iNew<iUpper ) iUpper = iNew;
          nOut--;

        }
      }

      pBuilder->pRec = pRec;
      if( rc==SQLITE_OK ){
        if( iUpper>iLower ){
          nNew = sqlite3LogEst(iUpper - iLower);
        }else{
          nNew = 10;        assert( 10==sqlite3LogEst(2) );
        }
        if( nNew<nOut ){
          nOut = nNew;
        }
        pLoop->nOut = (LogEst)nOut;
        WHERETRACE(0x10, ("range scan regions: %u..%u  est=%d\n",
                           (u32)iLower, (u32)iUpper, nOut));
        return SQLITE_OK;
      }
    }else{
      int bDone = 0;
      rc = whereRangeSkipScanEst(pParse, pLower, pUpper, pLoop, &bDone);
      if( bDone ) return rc;
    }
  }
#else
  UNUSED_PARAMETER(pParse);
  UNUSED_PARAMETER(pBuilder);
#endif
  assert( pLower || pUpper );

  assert( pUpper==0 || (pUpper->wtFlags & TERM_VNULL)==0 );
  nNew = whereRangeAdjust(pLower, nOut);
  nNew = whereRangeAdjust(pUpper, nNew);

  /* TUNING: If there is both an upper and lower limit, assume the range is
  ** reduced by an additional 75%. This means that, by default, an open-ended
  ** range query (e.g. col > ?) is assumed to match 1/4 of the rows in the







>















>













<


<










<

>







2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252

2253
2254

2255
2256
2257
2258
2259
2260
2261
2262
2263
2264

2265
2266
2267
2268
2269
2270
2271
2272
2273
        rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk);
        if( rc==SQLITE_OK && bOk ){
          tRowcnt iNew;
          whereKeyStats(pParse, p, pRec, 0, a);
          iNew = a[0] + ((pLower->eOperator & WO_GT) ? a[1] : 0);
          if( iNew>iLower ) iLower = iNew;
          nOut--;
          pLower = 0;
        }
      }

      /* If possible, improve on the iUpper estimate using ($P:$U). */
      if( pUpper ){
        int bOk;                    /* True if value is extracted from pExpr */
        Expr *pExpr = pUpper->pExpr->pRight;
        assert( (pUpper->eOperator & (WO_LT|WO_LE))!=0 );
        rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk);
        if( rc==SQLITE_OK && bOk ){
          tRowcnt iNew;
          whereKeyStats(pParse, p, pRec, 1, a);
          iNew = a[0] + ((pUpper->eOperator & WO_LE) ? a[1] : 0);
          if( iNew<iUpper ) iUpper = iNew;
          nOut--;
          pUpper = 0;
        }
      }

      pBuilder->pRec = pRec;
      if( rc==SQLITE_OK ){
        if( iUpper>iLower ){
          nNew = sqlite3LogEst(iUpper - iLower);
        }else{
          nNew = 10;        assert( 10==sqlite3LogEst(2) );
        }
        if( nNew<nOut ){
          nOut = nNew;
        }

        WHERETRACE(0x10, ("range scan regions: %u..%u  est=%d\n",
                           (u32)iLower, (u32)iUpper, nOut));

      }
    }else{
      int bDone = 0;
      rc = whereRangeSkipScanEst(pParse, pLower, pUpper, pLoop, &bDone);
      if( bDone ) return rc;
    }
  }
#else
  UNUSED_PARAMETER(pParse);
  UNUSED_PARAMETER(pBuilder);

  assert( pLower || pUpper );
#endif
  assert( pUpper==0 || (pUpper->wtFlags & TERM_VNULL)==0 );
  nNew = whereRangeAdjust(pLower, nOut);
  nNew = whereRangeAdjust(pUpper, nNew);

  /* TUNING: If there is both an upper and lower limit, assume the range is
  ** reduced by an additional 75%. This means that, by default, an open-ended
  ** range query (e.g. col > ?) is assumed to match 1/4 of the rows in the
Changes to test/analyze9.test.
1083
1084
1085
1086
1087
1088
1089














































1090
1091
  2 "d=0 AND a='z' AND b='n' AND e<100" {/*t5e (e<?)*/}

  3 "d=0 AND e<300"                     {/*t5d (d=?)*/}
  4 "d=0 AND e<200"                     {/*t5e (e<?)*/}
} {
  do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp
}















































finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


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
1135
1136
1137
  2 "d=0 AND a='z' AND b='n' AND e<100" {/*t5e (e<?)*/}

  3 "d=0 AND e<300"                     {/*t5d (d=?)*/}
  4 "d=0 AND e<200"                     {/*t5e (e<?)*/}
} {
  do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp
}

#-------------------------------------------------------------------------
# Test that if stat4 data is available but cannot be used because the
# rhs of a range constraint is a complex expression, the default estimates
# are used instead.
ifcapable stat4&&cte {
  do_execsql_test 25.1 {
    CREATE TABLE t6(a, b);
    WITH ints(i,j) AS (
      SELECT 1,1 UNION ALL SELECT i+1,j+1 FROM ints WHERE i<100
    ) INSERT INTO t6 SELECT * FROM ints;
    CREATE INDEX aa ON t6(a);
    CREATE INDEX bb ON t6(b);
    ANALYZE;
  }

  # Term (b<?) is estimated at 25%. Better than (a<30) but not as
  # good as (a<20).
  do_eqp_test 25.2.1 { SELECT * FROM t6 WHERE a<30 AND b<? } {
    0 0 0 {SEARCH TABLE t6 USING INDEX bb (b<?)}
  }
  do_eqp_test 25.2.2 { SELECT * FROM t6 WHERE a<20 AND b<? } {
    0 0 0 {SEARCH TABLE t6 USING INDEX aa (a<?)}
  }

  # Term (b BETWEEN ? AND ?) is estimated at 1/64.
  do_eqp_test 25.3.1 { 
    SELECT * FROM t6 WHERE a BETWEEN 5 AND 10 AND b BETWEEN ? AND ? 
  } {
    0 0 0 {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)}
  }
  
  # Term (b BETWEEN ? AND 60) is estimated to return roughly 15 rows -
  # 60 from (b<=60) multiplied by 0.25 for the b>=? term. Better than
  # (a<20) but not as good as (a<10).
  do_eqp_test 25.4.1 { 
    SELECT * FROM t6 WHERE a < 10 AND (b BETWEEN ? AND 60)
  } {
    0 0 0 {SEARCH TABLE t6 USING INDEX aa (a<?)}
  }
  do_eqp_test 25.4.2 { 
    SELECT * FROM t6 WHERE a < 20 AND (b BETWEEN ? AND 60)
  } {
    0 0 0 {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)}
  }
}

finish_test