Re: [HACKERS] OR clause status

> > Perhaps this is an artifact of the type-coercion issue (see "indexes
> > and floats" thread on pg-hackers).  I find I have to write something
> > like
> >         WHERE oid = 123456::oid
> > to get the system to use an index on OID.  If I write
> >         WHERE oid = 123456
> > it takes it, but does it by sequential scan :-(
> > I do not know if it's acted like that all along or it's a result
> > of Tom's type coercion fixes of a couple months ago.
>
> Hi Bruce. You are right, the optimizer is confusing :)
>
> I'm not sure if you were looking at this already, but I was thinking of
> finding the place where the optimizer decides whether an index can be
> used in a query, in particular when constants are involved. Seems like
> the overhead/operations involved should be identical whether the terms
> have the same type or not; in the cases above
>   WHERE oid = 123456::oid
> would use oideq() and
>   WHERE oid = 123456
> would use oidint4eq().
>
> Why would Postgres give up on using an index in the second case? In both
> cases there is one call to a function to evaluate the equality. Do the
> types need to match up for other reasons?
>
> I was thinking of adding the IS_BINARY_COMPATIBLE() macro as an
> optimization in the place where indices are being chosen, but then got
> confused as to why Postgres would care in the first place. Also, haven't
> found the area where these decisions are made.
>
> Any hints? Anyone else rummaged around that code?

In looking at the code, part of the problem is that you are creating a
FuncCall node in coerce_type, so I have to make sure I convert normal
funcs with constants to constants, and your type-conversion funcs too,
which will not be picked up in the normal expression parsing because
they were not there originally.  (Assuming the function is cache-able.)

In the case of x=3, does your code try to convert the constant to the
type of the variable, or does it try and do both.  We can convert:

    x = int4(3)

and not
    int4(x) = 3

or
    int4(x) = int4(3)


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.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)