Re: Views with unions

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> Yeah, but I think what he's hoping is that it'll notice that
> "key=1 and key=3" would be noticed as a false condition so that it doesn't
> scan those tables since a row presumably can't satisify both. The question
> would be, is the expense of checking the condition for all queries
> greater than the potential gain for these sorts of queries.

Yes, this is the key point: we won't put in an optimization that wins on
a small class of queries unless there is no material cost added for
planning cases where it doesn't apply.

> In addition, you'd have to be careful to make it work correctly with
> operator overloading, since someone could make operators whose
> semantics in cross-datatype comparisons are wierd.

In practice we would restrict such deductions to mergejoinable =
operators, which are sufficiently semantics-constrained that I think
you can treat equality at face value.

Actually, in CVS tip we are on the hairy edge of being able to do this:
generate_implied_equalities() actually detects that the given conditions
imply that two constants are equal. But it doesn't do anything with the
knowledge, because I couldn't figure out just what to do --- it's not
always correct to add a "WHERE false" constraint to the top level, but
where exactly do we add it? Exactly which relations are guaranteed to
produce zero rows in such a case? (When there are outer joins in the
picture, zero rows out of some relations doesn't mean zero rows out
overall.) And how do we exploit that knowledge once we've got it?
It'd be a pretty considerable amount of work to optimize a plan tree
fully for this sort of thing (eg, suppressing unnecessary joins), and
I doubt it's worth the trouble.

regards, tom lane