Re: Strange query plan

Oleg Bartunov <oleg(at)sai(dot)msu(dot)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 specific opclauses 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 list of 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);
}