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(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)