Re: New thoughts about indexing cross-type comparisons

On Wed, 17 Sep 2003, Tom Lane wrote:

> Another thing to keep in mind is that it's not always the case that
> assigning the right type to a literal constant would solve the problem.
> We have the same issues with variables; for example, a join with
> "WHERE a.int8col = b.int4col" may fail to take advantage of available
> indexes.

Sure, which is why subtyping is needed to insert the correct coercions
(relations like int4 < int8 and such). Generating all constraints and then
have a planner phase/inference phase in the end that makes the chioces. By
not forcing anything too early you give the inference/planner the
possibility to make good choices (which might depend on what indexes are
available and so on).

> Something else that's been troubling me about the issue is that the most
> efficient operator to use is really context-dependent.  In the example
> of "WHERE a.int8col = b.int4col", we basically have two possible
> choices; use a cross-type operator (int8 = int4), or introduce a
> coercion, so that the clause becomes "WHERE a.int8col = b.int4col::int8"
> using an int8 = int8 operator.  The latter is a win when considering
> indexscans on the int8 column, and also a win for hash joins (cross-type
> operators in general aren't hashable).  *But* the former is a win for
> merge joins, and particularly for implied equality deduction.

Yes, by not making the decission directly but just setting constrants,
then you in the end have a set of possible solutions. And then the planner
have to choose among those.

> If we force the coercion-based representation to be used then the
> planner will be able to deduce that a.int8col and b.int4col::int8 are
> equal, but it would have to make a leap of faith to relate that
> knowledge to the bare b.int4col column.  This would eg. prevent the use
> of a mergejoin with use of an index on the int4 column.

If I understand what you say correctly, then yes. If you have an inference
phase before the planner phase that decides to insert a coercion from int4
to int8 then you have made a decission that forces you to use one plan
over another. And that should be made by the planner and not the type
inference.

> So I'm beginning to think that avoiding cross-type operators is not the
> right route to a solution anyway.  It may be better to leave the parser
> alone and teach the planner how to switch to the alternate
> representation when and where appropriate.

Yes, and the planner needs information about what alternative
representations there are (which is the same as letting the planner insert
coercions and selecting what == operator to use, or are you thinking of
something else).

Another problem is of course if one let the planner do too much work and 
have to many possible plans to choose from, it has to be fast.

-- 
/Dennis