Re: To use a VIEW or not to use a View.....

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> On Thu, 23 Jan 2003, Tom Lane wrote:
>> What I was thinking was that any time the code sees a "var = const"
>> clause as part of a mergejoin equivalence set, we could mark all the
>> "var = var" clauses in the same set as no-ops. For example, given
>> WHERE a.f1 = b.f2 AND b.f2 = c.f3 AND c.f3 = 42
>> then after we finish deducing a.f1 = 42 and b.f2 = 42, there is no
>> longer any value in either of the original clauses a.f1 = b.f2 and
>> b.f2 = c.f3, nor in the other deduced clause a.f1 = c.f3. This would
>> take a little bit of restructuring of generate_implied_equalities() and
>> process_implied_equality(), but it doesn't seem too difficult to do.
>>
>> Thoughts? Are there any holes in that logic?

> The main thing I can think of is being careful when the types are
> different (like padding vs no padding in strings).

This is a matter of being careful about marking cross-datatype operators
as mergejoinable. We do not mark 'bpchar = text' as mergejoinable ---
in fact we don't even have such an operator. AFAICS any pitfalls in
those semantics come up already from the existing logic to treat
mergejoinable equality as transitive for variables. Extending that
transitivity to constants can't create problems that wouldn't exist
anyway.

For reference, these are the only cross-datatype mergejoinable operators
as of CVS tip:

regression=# select oid::regoperator,oprcode from pg_operator where oprlsortop!=0 and oprleft!=oprright;
oid | oprcode
--------------------------+-----------
=(integer,bigint) | int48eq
=(bigint,integer) | int84eq
=(smallint,integer) | int24eq
=(integer,smallint) | int42eq
=(real,double precision) | float48eq
=(double precision,real) | float84eq
=(smallint,bigint) | int28eq
=(bigint,smallint) | int82eq
(8 rows)

regards, tom lane