/ Check-in [06576590]
Login

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 Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

2419
2420
2421
2422
2423
2424
2425




2426
2427
2428
2429
2430
2431
2432

/* Check to see if a partial index with pPartIndexWhere can be used
** in the current query.  Return true if it can be and false if not.
*/
static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){
  int i;
  WhereTerm *pTerm;




  for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
    Expr *pExpr = pTerm->pExpr;
    if( sqlite3ExprImpliesExpr(pExpr, pWhere, iTab) 
     && (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
    ){
      return 1;
    }







>
>
>
>







2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436

/* Check to see if a partial index with pPartIndexWhere can be used
** in the current query.  Return true if it can be and false if not.
*/
static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){
  int i;
  WhereTerm *pTerm;
  while( pWhere->op==TK_AND ){
    if( !whereUsablePartialIndex(iTab,pWC,pWhere->pLeft) ) return 0;
    pWhere = pWhere->pRight;
  }
  for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
    Expr *pExpr = pTerm->pExpr;
    if( sqlite3ExprImpliesExpr(pExpr, pWhere, iTab) 
     && (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
    ){
      return 1;
    }

Changes to test/index6.test.

341
342
343
344
345
346
347

348





























349
  CREATE TABLE t9(a int, b int, c int, PRIMARY KEY(a)) WITHOUT ROWID;
  CREATE INDEX t9ca ON t9(c,a) WHERE a in (10,12,20);
  INSERT INTO t9 VALUES(1,1,9),(10,2,35),(11,15,82),(20,19,5);
  UPDATE t9 SET b=c WHERE a in (10,12,20);
  SELECT a,b,c,'|' FROM t9 ORDER BY a;
} {1 1 9 | 10 35 35 | 11 15 82 | 20 5 5 |}
































finish_test







>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
  CREATE TABLE t9(a int, b int, c int, PRIMARY KEY(a)) WITHOUT ROWID;
  CREATE INDEX t9ca ON t9(c,a) WHERE a in (10,12,20);
  INSERT INTO t9 VALUES(1,1,9),(10,2,35),(11,15,82),(20,19,5);
  UPDATE t9 SET b=c WHERE a in (10,12,20);
  SELECT a,b,c,'|' FROM t9 ORDER BY a;
} {1 1 9 | 10 35 35 | 11 15 82 | 20 5 5 |}

# AND-connected terms in the WHERE clause of a partial index
#
do_execsql_test index6-10.1 {
  CREATE TABLE t10(a,b,c,d,e INTEGER PRIMARY KEY);
  INSERT INTO t10 VALUES
    (1,2,3,4,5),
    (2,3,4,5,6),
    (3,4,5,6,7),
    (1,2,3,8,9);
  CREATE INDEX t10x ON t10(d) WHERE a=1 AND b=2 AND c=3;
  SELECT e FROM t10 WHERE a=1 AND b=2 AND c=3 ORDER BY d;
} {5 9}
do_execsql_test index6-10.1eqp {
  EXPLAIN QUERY PLAN
  SELECT e FROM t10 WHERE a=1 AND b=2 AND c=3 ORDER BY d;
} {/USING INDEX t10x/}
do_execsql_test index6-10.2 {
  SELECT e FROM t10 WHERE c=3 AND 2=b AND a=1 ORDER BY d DESC;
} {9 5}
do_execsql_test index6-10.2eqp {
  EXPLAIN QUERY PLAN
  SELECT e FROM t10 WHERE c=3 AND 2=b AND a=1 ORDER BY d DESC;
} {/USING INDEX t10x/}
do_execsql_test index6-10.3 {
  SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC;
} {9 5}
do_execsql_test index6-10.3eqp {
  EXPLAIN QUERY PLAN
  SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC;
} {~/USING INDEX t10x/}

finish_test