/ Check-in [93642a65]
Login

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

Overview
Comment:Consider using an automatic-index for a scan even if there exists a possible skip-scan that uses one or more "=" operators.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental-autoindex-fix
Files: files | file ages | folders
SHA1: 93642a65ef3d53ece322ffd85233b68fc9a86c9d
User & Date: dan 2014-11-14 19:34:20
Context
2014-11-18
21:45
Allow an automatic index to be used if the only uses of declared indexes for the same loop use the skip-scan algorithm. check-in: c52f7971 user: drh tags: trunk
2014-11-14
19:34
Consider using an automatic-index for a scan even if there exists a possible skip-scan that uses one or more "=" operators. Closed-Leaf check-in: 93642a65 user: dan tags: experimental-autoindex-fix
15:28
Do not automatically remove the DISTINCT keyword from "a IN (SELECT DISTINCT ...)" expressions. Fix for [db87229497]. check-in: 55e453aa user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  4126   4126       /* whereLoopAddBtree() always generates and inserts the automatic index
  4127   4127       ** case first.  Hence compatible candidate WhereLoops never have a larger
  4128   4128       ** rSetup. Call this SETUP-INVARIANT */
  4129   4129       assert( p->rSetup>=pTemplate->rSetup );
  4130   4130   
  4131   4131       /* Any loop using an appliation-defined index (or PRIMARY KEY or
  4132   4132       ** UNIQUE constraint) with one or more == constraints is better
  4133         -    ** than an automatic index. */
         4133  +    ** than an automatic index. Unless it is a skip-scan. */
  4134   4134       if( (p->wsFlags & WHERE_AUTO_INDEX)!=0
         4135  +     && (pTemplate->nSkip)==0
  4135   4136        && (pTemplate->wsFlags & WHERE_INDEXED)!=0
  4136   4137        && (pTemplate->wsFlags & WHERE_COLUMN_EQ)!=0
  4137   4138        && (p->prereq & pTemplate->prereq)==pTemplate->prereq
  4138   4139       ){
  4139   4140         break;
  4140   4141       }
  4141   4142   

Changes to test/autoindex3.test.

    13     13   # focus of this script is testing automatic index creation logic,
    14     14   # and specifically that an automatic index will not be created that
    15     15   # shadows a declared index.
    16     16   #
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
           20  +set testprefix autoindex3
    20     21   
    21     22   # The t1b and t2d indexes are not very selective.  It used to be that
    22     23   # the autoindex mechanism would create automatic indexes on t1(b) or
    23     24   # t2(d), make assumptions that they were reasonably selective, and use
    24     25   # them instead of t1b or t2d.  But that would be cheating, because the
    25     26   # automatic index cannot be any more selective than the real index.
    26     27   #
................................................................................
    49     50   } {/AUTO/}
    50     51   do_execsql_test autoindex3-130 {
    51     52     EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IS NULL AND x=y;
    52     53   } {/AUTO/}
    53     54   do_execsql_test autoindex3-140 {
    54     55     EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IN (5,b) AND x=y;
    55     56   } {/AUTO/}
           57  +
           58  +reset_db
           59  +do_execsql_test 210 {
           60  +  CREATE TABLE v(b, d, e);
           61  +  CREATE TABLE u(a, b, c);
           62  +  ANALYZE sqlite_master;
           63  +  INSERT INTO "sqlite_stat1" VALUES('u','uab','40000 400 1');
           64  +  INSERT INTO "sqlite_stat1" VALUES('v','vbde','40000 400 1 1');
           65  +  INSERT INTO "sqlite_stat1" VALUES('v','ve','40000 21');
           66  +
           67  +  CREATE INDEX uab on u(a, b);
           68  +  CREATE INDEX ve on v(e);
           69  +  CREATE INDEX vbde on v(b,d,e);
           70  +
           71  +  DROP TABLE IF EXISTS sqlite_stat4;
           72  +  ANALYZE sqlite_master;
           73  +}
           74  +
           75  +# At one point, SQLite was using the inferior plan:
           76  +#
           77  +#   0|0|1|SEARCH TABLE v USING INDEX ve (e>?)
           78  +#   0|1|0|SEARCH TABLE u USING COVERING INDEX uab (ANY(a) AND b=?)
           79  +#
           80  +# on the basis that the real index "uab" must be better than the automatic
           81  +# index. This is not right - a skip-scan is not necessarily better than an
           82  +# automatic index scan.
           83  +#
           84  +do_eqp_test 220 {
           85  +  select count(*) from u, v where u.b = v.b and v.e > 34;
           86  +} {
           87  +  0 0 1 {SEARCH TABLE v USING INDEX ve (e>?)} 
           88  +  0 1 0 {SEARCH TABLE u USING AUTOMATIC COVERING INDEX (b=?)}
           89  +}
    56     90   
    57     91   
    58     92   finish_test