/ Check-in [06576590]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Enhance the query planner so that it is able to use partial indexes that use AND-connected terms in the WHERE clause.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 065765902d2774d7432b9c00ea2efed26e0aaa5e
User & Date: drh 2015-08-07 20:57:00
Context
2015-08-08
15:13
Update RBU to avoid repreparing a statement immediately after it is prepared. check-in: 1d75a41b user: dan tags: trunk
2015-08-07
20:57
Enhance the query planner so that it is able to use partial indexes that use AND-connected terms in the WHERE clause. check-in: 06576590 user: drh tags: trunk
20:06
Add a specially formatted comment to shell.c to make it easier for scripts to edit. No code changes. check-in: 6d47b35a user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  2419   2419   
  2420   2420   /* Check to see if a partial index with pPartIndexWhere can be used
  2421   2421   ** in the current query.  Return true if it can be and false if not.
  2422   2422   */
  2423   2423   static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){
  2424   2424     int i;
  2425   2425     WhereTerm *pTerm;
         2426  +  while( pWhere->op==TK_AND ){
         2427  +    if( !whereUsablePartialIndex(iTab,pWC,pWhere->pLeft) ) return 0;
         2428  +    pWhere = pWhere->pRight;
         2429  +  }
  2426   2430     for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
  2427   2431       Expr *pExpr = pTerm->pExpr;
  2428   2432       if( sqlite3ExprImpliesExpr(pExpr, pWhere, iTab) 
  2429   2433        && (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
  2430   2434       ){
  2431   2435         return 1;
  2432   2436       }

Changes to test/index6.test.

   341    341     CREATE TABLE t9(a int, b int, c int, PRIMARY KEY(a)) WITHOUT ROWID;
   342    342     CREATE INDEX t9ca ON t9(c,a) WHERE a in (10,12,20);
   343    343     INSERT INTO t9 VALUES(1,1,9),(10,2,35),(11,15,82),(20,19,5);
   344    344     UPDATE t9 SET b=c WHERE a in (10,12,20);
   345    345     SELECT a,b,c,'|' FROM t9 ORDER BY a;
   346    346   } {1 1 9 | 10 35 35 | 11 15 82 | 20 5 5 |}
   347    347   
          348  +# AND-connected terms in the WHERE clause of a partial index
          349  +#
          350  +do_execsql_test index6-10.1 {
          351  +  CREATE TABLE t10(a,b,c,d,e INTEGER PRIMARY KEY);
          352  +  INSERT INTO t10 VALUES
          353  +    (1,2,3,4,5),
          354  +    (2,3,4,5,6),
          355  +    (3,4,5,6,7),
          356  +    (1,2,3,8,9);
          357  +  CREATE INDEX t10x ON t10(d) WHERE a=1 AND b=2 AND c=3;
          358  +  SELECT e FROM t10 WHERE a=1 AND b=2 AND c=3 ORDER BY d;
          359  +} {5 9}
          360  +do_execsql_test index6-10.1eqp {
          361  +  EXPLAIN QUERY PLAN
          362  +  SELECT e FROM t10 WHERE a=1 AND b=2 AND c=3 ORDER BY d;
          363  +} {/USING INDEX t10x/}
          364  +do_execsql_test index6-10.2 {
          365  +  SELECT e FROM t10 WHERE c=3 AND 2=b AND a=1 ORDER BY d DESC;
          366  +} {9 5}
          367  +do_execsql_test index6-10.2eqp {
          368  +  EXPLAIN QUERY PLAN
          369  +  SELECT e FROM t10 WHERE c=3 AND 2=b AND a=1 ORDER BY d DESC;
          370  +} {/USING INDEX t10x/}
          371  +do_execsql_test index6-10.3 {
          372  +  SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC;
          373  +} {9 5}
          374  +do_execsql_test index6-10.3eqp {
          375  +  EXPLAIN QUERY PLAN
          376  +  SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC;
          377  +} {~/USING INDEX t10x/}
   348    378   
   349    379   finish_test