Re: View vs. Statement Query Plan

On Tue, Jun 04, 2002 at 01:15:38AM -0400, Tom Lane wrote:
> Curt Sampson <cjs(at)cynic(dot)net> writes:
> > But for some reason this view doesn't use the indices that an
> > equivalant query uses:
>
> You're essentially supposing that
>
> select * from (select * from a union select * from b) where foo;
>
> may be transformed into
>
> (select * from a where foo) union (select * from b where foo);
>
> I don't doubt that this transformation is valid in some cases ... but
> I do doubt that it is valid in all cases. If someone can supply a
> rigorous proof about when it is valid, I'd be willing to look into
> doing the necessary programming.

IIRC, union does an implicit DISTINCT (there's UNION ALL, right). So if what
is being selected is anything other than a simple statement, it'll be very
hard to prove equivalence (i guess this is what the iscachable is for).

HTH,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.