Re: Union within View vs.Union of Views

Jeff Larsen wrote:
> If it's possible to consider this abstractly, is there any particular
> reason why there is such a vast difference in performance? I would
> guess that is has something to do with how the WHERE conditions are
> applied to a view composed of a UNION of queries. Perhaps this is an
> opportunity for improvement in the code. In the first case, it's as if
> the server is doing the union on all rows (over 10 million altogether
> in my case) without filtering, then applying the conditions to the
> result. Maybe there is no better way.
>
> I can post query plans if anyone is interested. I haven't really
> learned how to make sense out of them myself yet.
>
> For my purposes, I'm content to use the union of separate views in my
> application, so if this doesn't pique anyone's interest, feel free to
> ignore it.
>
I hit this as well in less impacting statements. I found myself curious
that the sub-plan would have to be executed in full before it applied
the filter. Perhaps PostgreSQL has difficulty pushing WHERE conditions
through the rule system? It's an area I only barely understand, so I
never looked further...

I'm interested, but do not have anything of value to provide either. :-)

Cheers,
mark

--
Mark Mielke <mark@mielke.cc>