> 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?
- Tom
