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 <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
