Re: Proposal for resolving casting issues

"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> It is not sufficient for the optimizer for joins though, since it
> cannot use the int4 index when confronted with "where tab1.int4col =
> tab2.numericcol".

For cross-datatype joins, the proposal as I sketched it would result in
the parser producing, eg,where tab1.int4col::numeric = tab2.numericcol
that is, we'd have a single-datatype operator and a runtime cast in the
expression.

The optimizer is today capable of producing a nested loop with inner
indexscan join from this --- so long as the inner indexscan is on the
uncasted column (numericcol in this case).  It won't consider an int4
index on int4col for this.  This seems okay to me, actually.  It's
better than what you get now with a cross-datatype comparison operator
(neither side can be indexscanned since the operator matches neither
index opclass).

The major failing that needs to be rectified is that merge and hash
joins won't even be considered, because that code only works with
quals that are unadorned "Var = Var".  I don't believe there is any
fundamental reason for this restriction.  As long as the top operator
is merge/hashjoinable, any expression should work on either side.
It's just a matter of cleaning up a few unwarranted shortcuts in the
planner.

But that work does need to be done before we can rip out all the
cross-datatype operators ... so this is definitely not happening
for 7.3 ...
        regards, tom lane