SQLite
Check-in [650a3fe03d]
Not logged in
Overview
Comment:Add a test-control to disable the skip-scan optimization.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:650a3fe03d61068e06f3097878a777ca8ed713a769444cdb3f8be7d1e19a0b83
User & Date: dan 2018-07-12 19:14:39
Context
2018-07-13
19:52
Add the "categories" option to the unicode61 tokenizer in fts5. check-in: 80d2b9e635 user: dan tags: trunk
2018-07-12
19:28
Merge latest trunk changes into this branch. Leaf check-in: 6a00a34e19 user: dan tags: begin-concurrent
19:14
Add a test-control to disable the skip-scan optimization. check-in: 650a3fe03d user: dan tags: trunk
11:28
Add a test case to check that SQLITE_DBCONFIG_RESET_DATABASE can be used with wal mode databases even if there are active readers. check-in: 6145f5b3ba user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

1579
1580
1581
1582
1583
1584
1585

1586
1587
1588
1589
1590
1591
1592
#define SQLITE_OmitNoopJoin   0x0100   /* Omit unused tables in joins */
#define SQLITE_CountOfView    0x0200   /* The count-of-view optimization */
#define SQLITE_CursorHints    0x0400   /* Add OP_CursorHint opcodes */
#define SQLITE_Stat34         0x0800   /* Use STAT3 or STAT4 data */
   /* TH3 expects the Stat34  ^^^^^^ value to be 0x0800.  Don't change it */
#define SQLITE_PushDown       0x1000   /* The push-down optimization */
#define SQLITE_SimplifyJoin   0x2000   /* Convert LEFT JOIN to JOIN */

#define SQLITE_AllOpts        0xffff   /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)
#define OptimizationEnabled(db, mask)   (((db)->dbOptFlags&(mask))==0)







>







1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
#define SQLITE_OmitNoopJoin   0x0100   /* Omit unused tables in joins */
#define SQLITE_CountOfView    0x0200   /* The count-of-view optimization */
#define SQLITE_CursorHints    0x0400   /* Add OP_CursorHint opcodes */
#define SQLITE_Stat34         0x0800   /* Use STAT3 or STAT4 data */
   /* TH3 expects the Stat34  ^^^^^^ value to be 0x0800.  Don't change it */
#define SQLITE_PushDown       0x1000   /* The push-down optimization */
#define SQLITE_SimplifyJoin   0x2000   /* Convert LEFT JOIN to JOIN */
#define SQLITE_SkipScan       0x4000   /* Skip-scans */
#define SQLITE_AllOpts        0xffff   /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)
#define OptimizationEnabled(db, mask)   (((db)->dbOptFlags&(mask))==0)

Changes to src/test1.c.

6950
6951
6952
6953
6954
6955
6956

6957
6958
6959
6960
6961
6962
6963
    { "distinct-opt",        SQLITE_DistinctOpt    },
    { "cover-idx-scan",      SQLITE_CoverIdxScan   },
    { "order-by-idx-join",   SQLITE_OrderByIdxJoin },
    { "transitive",          SQLITE_Transitive     },
    { "omit-noop-join",      SQLITE_OmitNoopJoin   },
    { "stat3",               SQLITE_Stat34         },
    { "stat4",               SQLITE_Stat34         },

  };

  if( objc!=4 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;







>







6950
6951
6952
6953
6954
6955
6956
6957
6958
6959
6960
6961
6962
6963
6964
    { "distinct-opt",        SQLITE_DistinctOpt    },
    { "cover-idx-scan",      SQLITE_CoverIdxScan   },
    { "order-by-idx-join",   SQLITE_OrderByIdxJoin },
    { "transitive",          SQLITE_Transitive     },
    { "omit-noop-join",      SQLITE_OmitNoopJoin   },
    { "stat3",               SQLITE_Stat34         },
    { "stat4",               SQLITE_Stat34         },
    { "skip-scan",           SQLITE_SkipScan       },
  };

  if( objc!=4 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;

Changes to src/where.c.

2668
2669
2670
2671
2672
2673
2674

2675
2676
2677
2678
2679
2680
2681
  ** the code). And, even if it is not, it should not be too much slower. 
  ** On the other hand, the extra seeks could end up being significantly
  ** more expensive.  */
  assert( 42==sqlite3LogEst(18) );
  if( saved_nEq==saved_nSkip
   && saved_nEq+1<pProbe->nKeyCol
   && pProbe->noSkipScan==0

   && pProbe->aiRowLogEst[saved_nEq+1]>=42  /* TUNING: Minimum for skip-scan */
   && (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
  ){
    LogEst nIter;
    pNew->u.btree.nEq++;
    pNew->nSkip++;
    pNew->aLTerm[pNew->nLTerm++] = 0;







>







2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
  ** the code). And, even if it is not, it should not be too much slower. 
  ** On the other hand, the extra seeks could end up being significantly
  ** more expensive.  */
  assert( 42==sqlite3LogEst(18) );
  if( saved_nEq==saved_nSkip
   && saved_nEq+1<pProbe->nKeyCol
   && pProbe->noSkipScan==0
   && OptimizationEnabled(db, SQLITE_SkipScan)
   && pProbe->aiRowLogEst[saved_nEq+1]>=42  /* TUNING: Minimum for skip-scan */
   && (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
  ){
    LogEst nIter;
    pNew->u.btree.nEq++;
    pNew->nSkip++;
    pNew->aLTerm[pNew->nLTerm++] = 0;

Changes to test/skipscan1.test.

333
334
335
336
337
338
339








340
  ANALYZE sqlite_master;
  INSERT INTO sqlite_stat1 VALUES('t9a','t9a_ab','1000000 250000 1');
  ANALYZE sqlite_master;
  EXPLAIN QUERY PLAN
  SELECT * FROM t9a WHERE b IN (SELECT x FROM t9b WHERE y!=5);
} {/USING INDEX t9a_ab .ANY.a. AND b=./}









finish_test







>
>
>
>
>
>
>
>

333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
  ANALYZE sqlite_master;
  INSERT INTO sqlite_stat1 VALUES('t9a','t9a_ab','1000000 250000 1');
  ANALYZE sqlite_master;
  EXPLAIN QUERY PLAN
  SELECT * FROM t9a WHERE b IN (SELECT x FROM t9b WHERE y!=5);
} {/USING INDEX t9a_ab .ANY.a. AND b=./}


optimization_control db skip-scan 0
do_execsql_test skipscan1-9.3 {
  EXPLAIN QUERY PLAN
  SELECT  * FROM t9a WHERE b IN (SELECT x FROM t9b WHERE y!=5);
} {/{SCAN TABLE t9a}/}
optimization_control db skip-scan 1

finish_test