Re: [HACKERS] Re: type coersion (was OR clause status)

> > > I claim the parser is doing the wrong thing by representing this as
> > > "where int4(i2) = 400::int4" rather than "where i2 = 400::int2".
> > > It is not really reasonable to expect the optimizer to clean up
> > > after that initial mistake.
> > I don't see that.
>
> Yup. The parser is behaving as Bruce describes. The new type conversion
> stuff isn't the fundamental problem. It's the original features in the
> planner when trying to use indices.
>
> > The problem is how do we use indexes for this? I am still researching
> > this.
>
> OK, let me know if I can help look into anything. In the meantime, I'll
> keep poking at it a bit...

The optimizer does a loop for each index on every relation:

In match_clause_to_indexkey(), there is code that takes the
operator, in the case of "oid = 3", value 1137, oideqint4:

if ((rightop && IsA(rightop, Const)) ||
(rightop && IsA(rightop, Param)))
{
restrict_op = ((Oper *) ((Expr *) clause)->oper)->opno;
isIndexable = (op_class(restrict_op, xclass, index->relam) &&
IndexScanableOperand(leftop,
indexkey,
rel,
index));
}

and calls opclass(), which does a lookup in the pg_amop cache, passing
the operator oid (1137), the access method class oid, and the index
access method:

#0 op_class (opno=1137, opclass=427, amopid=403) at lsyscache.c:58
oideqint4 oid_ops btree_am_oid

and it returns false because there is no access operator for oid_ops and
btree_am_oid that matches oideqint4.

The fundamental problem is that index scans are made to compare columns
all of the same type. That is how indexes are built and traversed.
Now, we want to bring in a constant of another type, and have it use an
index.

Sounds like if we add the proper pg_am functions for binary
compatability, the optimizer should then use the proper indexes.

Thomas?

[I am going to bed now.]

--
Bruce Momjian | 830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)