PgSQL-dotazy

[Karel Zak]

On Tue, Dec 03, 2002 at 12:49:28PM +0100, Zdenek Prchal wrote:
> > 
> > On Tue, Dec 03, 2002 at 09:17:13AM +0100, Frantisek Darena wrote:
> > > select sl1 sloupec from tab1
> > > union all
> > > select sl2 sloupec from tab2
> > > order by sloupec (nebo order by 1)
> > 
> >  Ooops ...todle je pochopitelne lepsi sesortovani nez jsem pred 
> >  chvilkou poslal s tim subselectem, jo v jednoduchosti je sila :-) 
> > 
> 
> No lepsi, je to jednodussi zapis, to ano, ale IMHO by to v obou pripadech
> melo vest na stejny execution plan, ne? Co na to rika EXPLAIN?

 Jasne. To by se jinak nedalo tvrdit, ze PostgreSQL ma nejlepsi planner 
dotazu na svete :-)

# EXPLAIN SELECT data FROM a UNION ALL SELECT data FROM b AS x ORDER BY data;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Sort  (cost=149.66..154.66 rows=2000 width=32)
   Sort Key: data
   ->  Append  (cost=0.00..40.00 rows=2000 width=32)
         ->  Subquery Scan "*SELECT* 1"  (cost=0.00..20.00 rows=1000 width=32)
               ->  Seq Scan on a  (cost=0.00..20.00 rows=1000 width=32)
         ->  Subquery Scan "*SELECT* 2"  (cost=0.00..20.00 rows=1000 width=32)
               ->  Seq Scan on b x  (cost=0.00..20.00 rows=1000 width=32)
(7 řádek)

# EXPLAIN SELECT * FROM (SELECT data FROM a UNION ALL SELECT data FROM b) as x ORDER BY data;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Sort  (cost=149.66..154.66 rows=2000 width=32)
   Sort Key: data
   ->  Subquery Scan x  (cost=0.00..40.00 rows=2000 width=32)
         ->  Append  (cost=0.00..40.00 rows=2000 width=32)
               ->  Subquery Scan "*SELECT* 1"  (cost=0.00..20.00 rows=1000 width=32)
                     ->  Seq Scan on a  (cost=0.00..20.00 rows=1000 width=32)
               ->  Subquery Scan "*SELECT* 2"  (cost=0.00..20.00 rows=1000 width=32)
                     ->  Seq Scan on b  (cost=0.00..20.00 rows=1000 width=32)
(8 řádek)


    Karel

-- 
 Karel Zak  <zakkr na zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/