Re: int8 primary keys still not using index without manual

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We can try removing most int2 functions and see if that makes such
> conversions much easier, or try casting incoming constants to int2 to
> see what happens.

I already did that --- that was exactly the substance of the tests I was
reporting in that message.

I have been thinking lately that the fundamental approach is wrong
anyway.  Basically the idea was to make the world safe for
single-datatype index handling by removing all the cross-type comparison
operators.  The reason

    SELECT ... WHERE int8col = 42

isn't indexable is that the = operator is int84eq, which is not to be
found in the set of operators associated with an index on int8.  What
we were thinking was that if we didn't have int84eq then the parser
would be forced to promote the 42 to int8, and then the comparison using
int8eq would be recognized as indexable.

I think this might actually be workable for int8, but it's not going to
work for int2 without changing the initial typing of small integer
constants, and we already know that that opens a Pandora's box of other
problems.

But quite aside from the semantic difficulties of rejiggering all that
stuff, it's going to break other parts of the optimizer if we do it.
In particular it will interfere with handling of mergejoins and
recognizing transitive equality.  For example consider

    SELECT ... WHERE a.int8col = b.int4col AND b.int4col = 42;

Currently we are able to deduce a.int8col = 42 (where the operator
is int84eq).  If we remove int84eq then the output of the parser for
this example will look like

    SELECT ... WHERE a.int8col = b.int4col::int8 AND b.int4col = 42;

and the transitive equality will not be recognized because
b.int4col::int8 is not the same expression as b.int4col.

So I'm currently thinking we'd be better off not to try to eliminate
the cross-type comparison operators.  Instead we need some solution
that is narrowly focused on the problem of making a non-indexable
comparison indexable, by converting a comparison value of the wrong
datatype into the right datatype locally to the indexscan plan
generation code.  I posted some speculation about that here:
http://archives.postgresql.org/pgsql-hackers/2003-09/msg00983.php

            regards, tom lane