From: Mark Mielke <mark@mark.mielke.cc>
To: Jeff Larsen <jlar310@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Union within View vs.Union of Views
Date: Sat, 03 Nov 2007 17:39:21 -0400
Message-ID: <472CEA89.30808@mark.mielke.cc> (raw)
In-Reply-To: <d1f9b6f00711031322h1d2858d4xbfff4852941d405b@mail.gmail.com>
References: <d1f9b6f00711031322h1d2858d4xbfff4852941d405b@mail.gmail.com>
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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark@mark.mielke.cc, jlar310@gmail.com
Subject: Re: Union within View vs.Union of Views
In-Reply-To: <472CEA89.30808@mark.mielke.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox