Re: Strange query plan - Mailing list pgsql-hackers

Oleg Bartunov <oleg@sai.msu.su> writes:
> select msg_prt.tid as mid from msg_prt
>  where exists (select idx.tid from idx where msg_prt.tid=idx.tid
>                 and idx.did=1 and idx.lid in (1207,59587) )
> NOTICE:  QUERY PLAN:

> Seq Scan on msg_prt  (cost=0.00..119090807.13 rows=69505 width=4)
>   SubPlan
>     ->  Index Scan using idxidx, idxidx on idx  (cost=0.00..1713.40 rows=1 width=4)

Actually, this example does reveal an unnecessary inefficiency: the
planner is only using the "idx.lid in (1207,59587)" clause for the
indexscan, ignoring the fact that the did and tid clauses match the
additional columns of your three-column index.  The attached patch
should improve matters.
        regards, tom lane


*** src/backend/optimizer/path/indxpath.c.orig    Sun May 20 16:28:18 2001
--- src/backend/optimizer/path/indxpath.c    Tue Jun  5 12:38:21 2001
***************
*** 397,403 ****                                         clause, false); } 
! /*  * Given an OR subclause that has previously been determined to match  * the specified index, extract a list of
specificopclauses that can be  * used as indexquals.
 
--- 397,403 ----                                         clause, false); } 
! /*----------  * Given an OR subclause that has previously been determined to match  * the specified index, extract a
listof specific opclauses that can be  * used as indexquals.
 
***************
*** 406,415 ****  * given opclause.    However, if the OR subclause is an AND, we have to  * scan it to find the
opclause(s)that match the index.  (There should  * be at least one, if match_or_subclause_to_indexkey succeeded, but
there
!  * could be more.)    Also, we apply expand_indexqual_conditions() to convert
!  * any special matching opclauses to indexable operators.  *  * The passed-in clause is not changed.  */ List *
extract_or_indexqual_conditions(RelOptInfo*rel,
 
--- 406,430 ----  * given opclause.    However, if the OR subclause is an AND, we have to  * scan it to find the
opclause(s)that match the index.  (There should  * be at least one, if match_or_subclause_to_indexkey succeeded, but
there
!  * could be more.)
!  *
!  * Also, we can look at other restriction clauses of the rel to discover
!  * additional candidate indexquals: for example, consider
!  *            ... where (a = 11 or a = 12) and b = 42;
!  * If we are dealing with an index on (a,b) then we can include the clause
!  * b = 42 in the indexqual list generated for each of the OR subclauses.
!  * Essentially, we are making an index-specific transformation from CNF to
!  * DNF.  (NOTE: when we do this, we end up with a slightly inefficient plan
!  * because create_indexscan_plan is not very bright about figuring out which
!  * restriction clauses are implied by the generated indexqual condition.
!  * Currently we'll end up rechecking both the OR clause and the transferred
!  * restriction clause as qpquals.  FIXME someday.)
!  *
!  * Also, we apply expand_indexqual_conditions() to convert any special
!  * matching opclauses to indexable operators.  *  * The passed-in clause is not changed.
+  *----------  */ List * extract_or_indexqual_conditions(RelOptInfo *rel,
***************
*** 417,470 ****                                 Expr *orsubclause) {     List       *quals = NIL; 
!     if (and_clause((Node *) orsubclause))     { 
!         /*
!          * Extract relevant sub-subclauses in indexkey order.  This is
!          * just like group_clauses_by_indexkey() except that the input and
!          * output are lists of bare clauses, not of RestrictInfo nodes.
!          */
!         int           *indexkeys = index->indexkeys;
!         Oid           *classes = index->classlist; 
!         do         {
!             int            curIndxKey = indexkeys[0];
!             Oid            curClass = classes[0];
!             List       *clausegroup = NIL;
!             List       *item; 
!             foreach(item, orsubclause->args)             {                 if (match_clause_to_indexkey(rel, index,
                                          curIndxKey, curClass,
 
!                                              lfirst(item), false))
!                     clausegroup = lappend(clausegroup, lfirst(item));             } 
!             /*
!              * If no clauses match this key, we're done; we don't want to
!              * look at keys to its right.
!              */
!             if (clausegroup == NIL)
!                 break;
! 
!             quals = nconc(quals, clausegroup);
! 
!             indexkeys++;
!             classes++;
!         } while (!DoneMatchingIndexKeys(indexkeys, index));
! 
!         if (quals == NIL)
!             elog(ERROR, "extract_or_indexqual_conditions: no matching clause");
!     }
!     else
!     {
!         /* we assume the caller passed a valid indexable qual */
!         quals = makeList1(orsubclause);
!     }      return expand_indexqual_conditions(quals); }
--- 432,503 ----                                 Expr *orsubclause) {     List       *quals = NIL;
+     int           *indexkeys = index->indexkeys;
+     Oid           *classes = index->classlist; 
!     /*
!      * Extract relevant indexclauses in indexkey order.  This is essentially
!      * just like group_clauses_by_indexkey() except that the input and
!      * output are lists of bare clauses, not of RestrictInfo nodes.
!      */
!     do     {
+         int            curIndxKey = indexkeys[0];
+         Oid            curClass = classes[0];
+         List       *clausegroup = NIL;
+         List       *item; 
!         if (and_clause((Node *) orsubclause))
!         {
!             foreach(item, orsubclause->args)
!             {
!                 Expr   *subsubclause = (Expr *) lfirst(item); 
!                 if (match_clause_to_indexkey(rel, index,
!                                              curIndxKey, curClass,
!                                              subsubclause, false))
!                     clausegroup = lappend(clausegroup, subsubclause);
!             }
!         }
!         else if (match_clause_to_indexkey(rel, index,
!                                           curIndxKey, curClass,
!                                           orsubclause, false))         {
!             clausegroup = makeList1(orsubclause);
!         } 
!         /*
!          * If we found no clauses for this indexkey in the OR subclause
!          * itself, try looking in the rel's top-level restriction list.
!          */
!         if (clausegroup == NIL)
!         {
!             foreach(item, rel->baserestrictinfo)             {
+                 RestrictInfo *rinfo = (RestrictInfo *) lfirst(item);
+                  if (match_clause_to_indexkey(rel, index,                                              curIndxKey,
curClass,
!                                              rinfo->clause, false))
!                     clausegroup = lappend(clausegroup, rinfo->clause);             }
+         } 
!         /*
!          * If still no clauses match this key, we're done; we don't want to
!          * look at keys to its right.
!          */
!         if (clausegroup == NIL)
!             break;
! 
!         quals = nconc(quals, clausegroup);
! 
!         indexkeys++;
!         classes++;
!     } while (!DoneMatchingIndexKeys(indexkeys, index));
! 
!     if (quals == NIL)
!         elog(ERROR, "extract_or_indexqual_conditions: no matching clause");      return
expand_indexqual_conditions(quals);}