From: Jeff Larsen <jlar310@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Union within View vs.Union of Views
Date: Sat, 3 Nov 2007 15:22:18 -0500
Message-ID: <d1f9b6f00711031322h1d2858d4xbfff4852941d405b@mail.gmail.com> (raw)
Consider:
CREATE VIEW_X AS
SELECT <query A>
UNION ALL
SELECT <query B>
UNION ALL
SELECT <query C>;
versus
CREATE VIEW_A AS
SELECT <query A>;
CREATE VIEW_B AS
SELECT <query B>;
CREATE VIEW_C AS
SELECT <query B>;
where <query A>, <query B> and <query C> are each somewhat complex
with several joins, but utilizing different tables for each of A, B
and C.
Performance on
SELECT * from VIEW_X WHERE <conditions>;
was absolutely terrible. But performance on
SELECT * from VIEW_A WHERE <conditions>
UNION ALL
SELECT * from VIEW_B WHERE <conditions>
UNION ALL
SELECT * from VIEW_C WHERE <conditions>;
was nice and speedy, perhaps 100 times faster than the first.
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.
Jeff
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: jlar310@gmail.com
Subject: Re: Union within View vs.Union of Views
In-Reply-To: <d1f9b6f00711031322h1d2858d4xbfff4852941d405b@mail.gmail.com>
* 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