On Fri, Jul 1, 2016 at 12:00 PM, Merlin Moncure <mmonc...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers