/ Check-in [31e480f6]
Login

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

Overview
Comment:Improved comments an presentation for the recent IN operator decision improvement.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 31e480f68dfd887cfd9114f9f9fec53d751d3ecc27e36f55c0166b51b2fbb08c
User & Date: drh 2018-06-09 14:13:46
Context
2018-06-09
16:49
Slightly smaller and faster code by encapsulating wal-index hash table location information in a separate WalHashLoc object rather than passing around the various elements as separate variables. check-in: 538a365b user: drh tags: trunk
14:13
Improved comments an presentation for the recent IN operator decision improvement. check-in: 31e480f6 user: drh tags: trunk
02:49
Performance improvement to sqlite3WhereExprUsage(). check-in: fd093413 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  2447   2447           || (pNew->wsFlags & WHERE_COLUMN_NULL)!=0 
  2448   2448           || (pNew->wsFlags & WHERE_COLUMN_IN)!=0 
  2449   2449           || (pNew->wsFlags & WHERE_SKIPSCAN)!=0 
  2450   2450       );
  2451   2451   
  2452   2452       if( eOp & WO_IN ){
  2453   2453         Expr *pExpr = pTerm->pExpr;
  2454         -      LogEst M, logK;
  2455   2454         if( ExprHasProperty(pExpr, EP_xIsSelect) ){
  2456   2455           /* "x IN (SELECT ...)":  TUNING: the SELECT returns 25 rows */
  2457   2456           int i;
  2458   2457           nIn = 46;  assert( 46==sqlite3LogEst(25) );
  2459   2458   
  2460   2459           /* The expression may actually be of the form (x, y) IN (SELECT...).
  2461   2460           ** In this case there is a separate term for each of (x) and (y).
................................................................................
  2468   2467         }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
  2469   2468           /* "x IN (value, value, ...)" */
  2470   2469           nIn = sqlite3LogEst(pExpr->x.pList->nExpr);
  2471   2470           assert( nIn>0 );  /* RHS always has 2 or more terms...  The parser
  2472   2471                             ** changes "x IN (?)" into "x=?". */
  2473   2472         }
  2474   2473         if( pProbe->hasStat1 ){
         2474  +        LogEst M, logK, safetyMargin;
  2475   2475           /* Let:
  2476   2476           **   N = the total number of rows in the table
  2477   2477           **   K = the number of entries on the RHS of the IN operator
  2478   2478           **   M = the number of rows in the table that match terms to the 
  2479   2479           **       to the left in the same index.  If the IN operator is on
  2480   2480           **       the left-most index column, M==N.
  2481   2481           **
................................................................................
  2489   2489           ** a safety margin of 2 (LogEst: 10) that favors using the IN operator
  2490   2490           ** with the index, as using an index has better worst-case behavior.
  2491   2491           ** If we do not have real sqlite_stat1 data, always prefer to use
  2492   2492           ** the index.
  2493   2493           */
  2494   2494           M = pProbe->aiRowLogEst[saved_nEq];
  2495   2495           logK = estLog(nIn);
  2496         -        if( M + logK + 10 < nIn + rLogSize ){
         2496  +        safetyMargin = 10;  /* TUNING: extra weight for indexed IN */
         2497  +        if( M + logK + safetyMargin < nIn + rLogSize ){
  2497   2498             WHERETRACE(0x40,
  2498   2499               ("Scan preferred over IN operator on column %d of \"%s\" (%d<%d)\n",
  2499   2500                saved_nEq, pProbe->zName, M+logK+10, nIn+rLogSize));
  2500   2501             continue;
  2501   2502           }else{
  2502   2503             WHERETRACE(0x40,
  2503   2504               ("IN operator preferred on column %d of \"%s\" (%d>=%d)\n",