On Fri, Jul 1, 2016 at 12:00 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> Sure (I didn't put you on that position, just thinking out loud). The
> problem with UNION ALL is that it's only safe to do so when you know
> for sure the both sides of the partition are non-overlapping. The
> author of the query often knows this going in but for the planner it's
> not so simple to figure out in many cases. If there's a subset of
> cases. UNION sans ALL is probably a dead end on performance grounds.
I'm not sure about that. It's certainly true that things are much
more likely to work out when you can prove that UNION ALL is
sufficient, because now you avoid de-duplication. But if the number
of output rows is really small, it might work out anyway. I mean,
consider this:
SELECT * FROM enormous WHERE rarely_one = 1 OR EXISTS (SELECT 1 FROM
tiny WHERE tiny.x = enormous.x)
As written, you're not going to be able to answer this query without
scanning a full scan of the enormous table. If you rewrite it to use
UNION, then the first half can be implemented with an index scan or a
bitmap index scan, and the second half can be implemented with a
nested loop over the tiny table with an inner index scan on the
enormous table. The fact that you have to deduplicate the results may
be a small price to pay for avoiding an enormous scan.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company