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@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/