The following bug has been logged online:
Bug reference: 4900
Logged by: Peter Headland
Email address: pheadland@actuate.com
PostgreSQL version: 8.4.0
Operating system: Windows
Description: Query planner misses obvious optimization on ordered
UNION DISTINCT
Details:
Consider the following union:
SELECT a, b, c FROM t WHERE d = 1
UNION DISTINCT
SELECT a, b, c FROM t WHERE d = 2
ORDER BY b, c;
I have a table for which the plan for the above is ...->sort->unique->sort.
I infer that the first sort is a,b,c.
The obvious optimization is to reorder the columns used in the first sort to
eliminate the need for the second sort. To illustrate this, I change the
query to
SELECT b, c, a FROM t WHERE d = 1
UNION DISTINCT
SELECT b, c, a FROM t WHERE d = 2
ORDER BY b, c;
the plan now becomes ...->sort->distinct.
