Re: View prevents index

Tatsuo Ishii <t-ishii@sra.co.jp> writes:
>> It's not immediately obvious to me that these are equivalent, or
>> perhaps I should say it's not clear under what conditions is the
>> transformation legitimate.

> Could you tell me in what cases two of above are not equivalent?

It may well be OK, I just want to see a rigorous demonstration of it.
It *looks* right, but intuition is frequently misleading.  Two points
that particularly need thought are (a) what about NULLs --- SQL's
three-way boolean logic breaks a lot of other things that seem right
intuitively; (b) does the same equivalence hold for UNION ALL,
INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL?

If you think that it's so obvious as not to require any thought,
I offer the following counterexample:

        ... WHERE random() > 0.5;

Pushing down a WHERE like this one *will* change the results.

That particular case doesn't bother me, but user-defined functions
that access other tables might have history-dependent behavior,
too.  Do we need to allow for that?

            regards, tom lane