/ Check-in [e06dc6f0]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e06dc6f0c35f87c44292c71677111b74f073a5c4
User & Date: dan 2014-08-25 18:29:38
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: 91d8a8d0 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: e06dc6f0 user: dan tags: trunk
11:33
Remove the pager_lookup() function since it is redundant with sqlite3PagerLookup(). check-in: 54164ce4 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  2216   2216           rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk);
  2217   2217           if( rc==SQLITE_OK && bOk ){
  2218   2218             tRowcnt iNew;
  2219   2219             whereKeyStats(pParse, p, pRec, 0, a);
  2220   2220             iNew = a[0] + ((pLower->eOperator & WO_GT) ? a[1] : 0);
  2221   2221             if( iNew>iLower ) iLower = iNew;
  2222   2222             nOut--;
         2223  +          pLower = 0;
  2223   2224           }
  2224   2225         }
  2225   2226   
  2226   2227         /* If possible, improve on the iUpper estimate using ($P:$U). */
  2227   2228         if( pUpper ){
  2228   2229           int bOk;                    /* True if value is extracted from pExpr */
  2229   2230           Expr *pExpr = pUpper->pExpr->pRight;
................................................................................
  2231   2232           rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk);
  2232   2233           if( rc==SQLITE_OK && bOk ){
  2233   2234             tRowcnt iNew;
  2234   2235             whereKeyStats(pParse, p, pRec, 1, a);
  2235   2236             iNew = a[0] + ((pUpper->eOperator & WO_LE) ? a[1] : 0);
  2236   2237             if( iNew<iUpper ) iUpper = iNew;
  2237   2238             nOut--;
         2239  +          pUpper = 0;
  2238   2240           }
  2239   2241         }
  2240   2242   
  2241   2243         pBuilder->pRec = pRec;
  2242   2244         if( rc==SQLITE_OK ){
  2243   2245           if( iUpper>iLower ){
  2244   2246             nNew = sqlite3LogEst(iUpper - iLower);
  2245   2247           }else{
  2246   2248             nNew = 10;        assert( 10==sqlite3LogEst(2) );
  2247   2249           }
  2248   2250           if( nNew<nOut ){
  2249   2251             nOut = nNew;
  2250   2252           }
  2251         -        pLoop->nOut = (LogEst)nOut;
  2252   2253           WHERETRACE(0x10, ("range scan regions: %u..%u  est=%d\n",
  2253   2254                              (u32)iLower, (u32)iUpper, nOut));
  2254         -        return SQLITE_OK;
  2255   2255         }
  2256   2256       }else{
  2257   2257         int bDone = 0;
  2258   2258         rc = whereRangeSkipScanEst(pParse, pLower, pUpper, pLoop, &bDone);
  2259   2259         if( bDone ) return rc;
  2260   2260       }
  2261   2261     }
  2262   2262   #else
  2263   2263     UNUSED_PARAMETER(pParse);
  2264   2264     UNUSED_PARAMETER(pBuilder);
  2265         -#endif
  2266   2265     assert( pLower || pUpper );
         2266  +#endif
  2267   2267     assert( pUpper==0 || (pUpper->wtFlags & TERM_VNULL)==0 );
  2268   2268     nNew = whereRangeAdjust(pLower, nOut);
  2269   2269     nNew = whereRangeAdjust(pUpper, nNew);
  2270   2270   
  2271   2271     /* TUNING: If there is both an upper and lower limit, assume the range is
  2272   2272     ** reduced by an additional 75%. This means that, by default, an open-ended
  2273   2273     ** range query (e.g. col > ?) is assumed to match 1/4 of the rows in the

Changes to test/analyze9.test.

  1083   1083     2 "d=0 AND a='z' AND b='n' AND e<100" {/*t5e (e<?)*/}
  1084   1084   
  1085   1085     3 "d=0 AND e<300"                     {/*t5d (d=?)*/}
  1086   1086     4 "d=0 AND e<200"                     {/*t5e (e<?)*/}
  1087   1087   } {
  1088   1088     do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp
  1089   1089   }
         1090  +
         1091  +#-------------------------------------------------------------------------
         1092  +# Test that if stat4 data is available but cannot be used because the
         1093  +# rhs of a range constraint is a complex expression, the default estimates
         1094  +# are used instead.
         1095  +ifcapable stat4&&cte {
         1096  +  do_execsql_test 25.1 {
         1097  +    CREATE TABLE t6(a, b);
         1098  +    WITH ints(i,j) AS (
         1099  +      SELECT 1,1 UNION ALL SELECT i+1,j+1 FROM ints WHERE i<100
         1100  +    ) INSERT INTO t6 SELECT * FROM ints;
         1101  +    CREATE INDEX aa ON t6(a);
         1102  +    CREATE INDEX bb ON t6(b);
         1103  +    ANALYZE;
         1104  +  }
         1105  +
         1106  +  # Term (b<?) is estimated at 25%. Better than (a<30) but not as
         1107  +  # good as (a<20).
         1108  +  do_eqp_test 25.2.1 { SELECT * FROM t6 WHERE a<30 AND b<? } {
         1109  +    0 0 0 {SEARCH TABLE t6 USING INDEX bb (b<?)}
         1110  +  }
         1111  +  do_eqp_test 25.2.2 { SELECT * FROM t6 WHERE a<20 AND b<? } {
         1112  +    0 0 0 {SEARCH TABLE t6 USING INDEX aa (a<?)}
         1113  +  }
         1114  +
         1115  +  # Term (b BETWEEN ? AND ?) is estimated at 1/64.
         1116  +  do_eqp_test 25.3.1 { 
         1117  +    SELECT * FROM t6 WHERE a BETWEEN 5 AND 10 AND b BETWEEN ? AND ? 
         1118  +  } {
         1119  +    0 0 0 {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)}
         1120  +  }
         1121  +  
         1122  +  # Term (b BETWEEN ? AND 60) is estimated to return roughly 15 rows -
         1123  +  # 60 from (b<=60) multiplied by 0.25 for the b>=? term. Better than
         1124  +  # (a<20) but not as good as (a<10).
         1125  +  do_eqp_test 25.4.1 { 
         1126  +    SELECT * FROM t6 WHERE a < 10 AND (b BETWEEN ? AND 60)
         1127  +  } {
         1128  +    0 0 0 {SEARCH TABLE t6 USING INDEX aa (a<?)}
         1129  +  }
         1130  +  do_eqp_test 25.4.2 { 
         1131  +    SELECT * FROM t6 WHERE a < 20 AND (b BETWEEN ? AND 60)
         1132  +  } {
         1133  +    0 0 0 {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)}
         1134  +  }
         1135  +}
  1090   1136   
  1091   1137   finish_test