union vs. sort

I'm surprise with query plan that PostgreSQL planner prepare for
selects with ORDER BY if all data are from sub-select that is already
sorted.

# explain select data from
(select distinct data from addr)
as x order by x.data;
-------------------------------------------------
Subquery Scan x
-> Unique
-> Sort
Sort Key: data
-> Seq Scan on addr

This is right -- the main of query doesn't use "Sort" for ORDER BY,
because subselect is sorted by "Unique".

And almost same query, but in the subselect is union:

# explain select data from
(select data from addr
union
select data from addr2)
as x order by x.data;
-----------------------------------------
Sort
Sort Key: data
-> Subquery Scan x
-> Unique
-> Sort
Sort Key: data
-> Append
-> Subquery Scan "*SELECT* 1"
-> Seq Scan on addr
-> Subquery Scan "*SELECT* 2"
-> Seq Scan on addr2

I think it's bad, because there is used extra sort for ORDER BY for
already by "Unique" sorted data.

If I add ORDER BY to subselect:

# explain select data from
(select data from addr
union
select data from addr2 order by data)
as x order by x.data;
---------------------------------------------------
Sort
Sort Key: data
-> Subquery Scan x
-> Sort
Sort Key: data
-> Unique
-> Sort
Sort Key: data
-> Append
-> Subquery Scan "*SELECT* 1"
-> Seq Scan on addr
-> Subquery Scan "*SELECT* 2"
-> Seq Scan on addr2

I see two unnecessary sorts for unique and already sorted data.

The core of problem is probbaly UNION, because if I use simple query without
subselect it still sort already sorderd data:

# explain select data from addr
union
select data from addr2
order by data;
-----------------------------------
Sort
Sort Key: data
-> Unique
-> Sort
Sort Key: data
-> Append
-> Subquery Scan "*SELECT* 1"
-> Seq Scan on addr
-> Subquery Scan "*SELECT* 2"
-> Seq Scan on addr2

Or order of data which returns "unique" is for UNION diffrent that data
from DISTINCT? (see first example).

Karel

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/