/ Check-in [5c243eec]
Login

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

Overview
Comment:When the left-hand side of a WHERE clause contraint is a UNIQUE column, and that term of the WHERE clause is not used for indexing, assume that the term reduces the number of output rows by half. This is one proposed fix for ticket [e8b674241947eb3ba4]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | unique-constraint-weights
Files: files | file ages | folders
SHA3-256: 5c243eec7bcccda3f97c20c2f8a44e2811807615cf0ef378bb548f0791c55964
User & Date: drh 2018-10-05 20:09:07
Context
2018-10-05
20:09
When the left-hand side of a WHERE clause contraint is a UNIQUE column, and that term of the WHERE clause is not used for indexing, assume that the term reduces the number of output rows by half. This is one proposed fix for ticket [e8b674241947eb3ba4] Leaf check-in: 5c243eec user: drh tags: unique-constraint-weights
15:10
Changes to geopoly to silience false-positive warnings coming out of clang. check-in: 11d9015f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/rtree/rtreeC.test.

   173    173   } {1 1 3 {}}
   174    174   
   175    175   #--------------------------------------------------------------------
   176    176   # Test that the sqlite_stat1 data is used correctly.
   177    177   #
   178    178   reset_db
   179    179   do_execsql_test 5.1 {
   180         -  CREATE TABLE t1(x PRIMARY KEY, y);
          180  +  CREATE TABLE t1(x, y);
          181  +  CREATE INDEX t1x ON t1(x);
   181    182     CREATE VIRTUAL TABLE rt USING rtree(id, x1, x2, +d1);
   182    183   
   183    184     INSERT INTO t1(x) VALUES(1);
   184    185     INSERT INTO t1(x) SELECT x+1 FROM t1;   --   2
   185    186     INSERT INTO t1(x) SELECT x+2 FROM t1;   --   4
   186    187     INSERT INTO t1(x) SELECT x+4 FROM t1;   --   8
   187    188     INSERT INTO t1(x) SELECT x+8 FROM t1;   --  16
................................................................................
   217    218   db close
   218    219   sqlite3 db test.db
   219    220   do_eqp_test 5.4 {
   220    221     SELECT * FROM t1, rt WHERE x==id;
   221    222   } {
   222    223     QUERY PLAN
   223    224     |--SCAN TABLE rt VIRTUAL TABLE INDEX 2:
   224         -  `--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (x=?)
          225  +  `--SEARCH TABLE t1 USING INDEX t1x (x=?)
   225    226   }
   226    227   
   227    228   # Delete the ANALYZE data. "t1" should be the outer loop again.
   228    229   #
   229    230   do_execsql_test 5.5 { DROP TABLE sqlite_stat1; }
   230    231   db close
   231    232   sqlite3 db test.db

Changes to src/where.c.

  2472   2472        && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
  2473   2473       ){
  2474   2474         continue;
  2475   2475       }
  2476   2476   
  2477   2477       if( IsUniqueIndex(pProbe) && saved_nEq==pProbe->nKeyCol-1 ){
  2478   2478         pBuilder->bldFlags |= SQLITE_BLDF_UNIQUE;
         2479  +      if( pProbe->nKeyCol==1
         2480  +       && pTerm->truthProb>=0
         2481  +       && (pTerm->eOperator & (WO_EQ|WO_IS|WO_IN))!=0
         2482  +      ){
         2483  +        /* If the LHS of an == or IS or IN operator is unique, then
         2484  +        ** make the guess that the truth probability of the expression is 50%.
         2485  +        ** This is probably an overestimate, but we want to be safe.  Without
         2486  +        ** this guess, the truth probability would be 93.75%, which is usually
         2487  +        ** a little too high for such a constraint, resulting in an output row
         2488  +        ** count that is too large, and throwing off the calcualations on the
         2489  +        ** cost of an external sort. */
         2490  +        pTerm->truthProb = -10;
         2491  +      }
  2479   2492       }else{
  2480   2493         pBuilder->bldFlags |= SQLITE_BLDF_INDEXED;
  2481   2494       }
  2482   2495       pNew->wsFlags = saved_wsFlags;
  2483   2496       pNew->u.btree.nEq = saved_nEq;
  2484   2497       pNew->u.btree.nBtm = saved_nBtm;
  2485   2498       pNew->u.btree.nTop = saved_nTop;

Changes to test/whereF.test.

    48     48   source $testdir/tester.tcl
    49     49   set testprefix whereF
    50     50   
    51     51   do_execsql_test 1.0 {
    52     52     PRAGMA automatic_index = 0;
    53     53     CREATE TABLE t1(a, b, c);
    54     54     CREATE TABLE t2(d, e, f);
    55         -  CREATE UNIQUE INDEX i1 ON t1(a);
    56         -  CREATE UNIQUE INDEX i2 ON t2(d);
           55  +  CREATE INDEX i1 ON t1(a);
           56  +  CREATE INDEX i2 ON t2(d);
    57     57   } {}
    58     58   
    59     59   foreach {tn sql} {
    60     60     1 "SELECT * FROM t1,           t2 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
    61     61     2 "SELECT * FROM t2,           t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
    62     62     3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
    63     63   } {
................................................................................
    68     68   
    69     69   do_execsql_test 2.0 {
    70     70     DROP TABLE t1;
    71     71     DROP TABLE t2;
    72     72     CREATE TABLE t1(a, b, c);
    73     73     CREATE TABLE t2(d, e, f);
    74     74   
    75         -  CREATE UNIQUE INDEX i1 ON t1(a);
    76         -  CREATE UNIQUE INDEX i2 ON t1(b);
    77         -  CREATE UNIQUE INDEX i3 ON t2(d);
           75  +  CREATE INDEX i1 ON t1(a);
           76  +  CREATE INDEX i2 ON t1(b);
           77  +  CREATE INDEX i3 ON t2(d);
    78     78   } {}
    79     79   
    80     80   foreach {tn sql} {
    81     81     1 "SELECT * FROM t1,           t2 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
    82     82     2 "SELECT * FROM t2,           t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
    83     83     3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
    84     84   } {

Changes to test/whereG.test.

   147    147          AND album.aid=track.aid;
   148    148     }
   149    149   } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
   150    150   
   151    151   # Commuting a term of the WHERE clause should not change the query plan
   152    152   #
   153    153   do_execsql_test whereG-3.0 {
   154         -  CREATE TABLE a(a1 PRIMARY KEY, a2);
   155         -  CREATE TABLE b(b1 PRIMARY KEY, b2);
          154  +  CREATE TABLE a(a1, a2);  CREATE INDEX a_1 ON a(a1);
          155  +  CREATE TABLE b(b1, b2);  CREATE INDEX b_1 ON b(b1);
   156    156   } {}
   157    157   do_eqp_test whereG-3.1 {
   158    158     SELECT * FROM a, b WHERE b1=a1 AND a2=5;
   159    159   } {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/}
   160    160   do_eqp_test whereG-3.2 {
   161    161     SELECT * FROM a, b WHERE a1=b1 AND a2=5;
   162    162   } {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/}

Changes to test/whereL.test.

    43     43   do_eqp_test 120 {
    44     44     SELECT * FROM t1, t2, t3
    45     45      WHERE t1.a=t2.a AND t2.a=t3.j AND t3.j=5
    46     46     ORDER BY t1.a;
    47     47   } {
    48     48     QUERY PLAN
    49     49     |--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
    50         -  |--SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
    51         -  `--SCAN TABLE t3
           50  +  |--SEARCH TABLE t2 USING AUTOMATIC PARTIAL COVERING INDEX (a=?)
           51  +  `--SEARCH TABLE t3 USING AUTOMATIC PARTIAL COVERING INDEX (j=?)
    52     52   }
    53     53   
    54     54   # Constant propagation in the face of collating sequences:
    55     55   #
    56     56   do_execsql_test 200 {
    57     57     CREATE TABLE c3(x COLLATE binary, y COLLATE nocase, z COLLATE binary);
    58     58     CREATE INDEX c3x ON c3(x);

Changes to test/with3.test.

   122    122     |  |  |--SCAN CONSTANT ROW
   123    123     |  |  `--SCALAR SUBQUERY
   124    124     |  |     `--SCAN TABLE w2
   125    125     |  `--RECURSIVE STEP
   126    126     |     |--SCAN TABLE w1
   127    127     |     `--SCAN TABLE c
   128    128     |--SCAN SUBQUERY xxxxxx
   129         -  |--SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)
   130         -  `--SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)
          129  +  |--SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)
          130  +  `--SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)
   131    131   }
   132    132   
   133    133   finish_test