Dotazovaci plan PostgreSQL

[Karel Zak]

On Tue, Apr 06, 2004 at 10:40:48AM +0200, Ing. Pavel Janousek wrote:
> > -----Original Message-----
> > From: Honza Pazdziora [mailto:adelton na informatics.muni.cz] 
> > Subject: Re: Dotazovaci plan PostgreSQL
> 
> > Je potreba si uvedomit, ze union je v podstate select distinct ...
> > union all. Ze union interne pouzije sort je jen shoda nahod, nemuzete
 
 Planner presne sestavuje posloupnost kroku v planu takze urcite to neni
 nahoda a  informaci o tom, ze  pouzije sort a ne  hash ma a mel  by byt
 schopen  naplanovat  zbytek  dotazu. IMHO tady  neni  nejake  dynamicke
 rozhodnuti v executoru ktery pracuje s daty pouzije-li hash nebo sort.

 On informaci o stavu (razeni) toho subselectu pouziva. Napriklad:

# 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

 
 Jiz  pro "order  by  x.data" zadne  rideni  neprovadi. Naopak pokud  se
 pouzije union a je to vlastne to same (zase unique za pomoci sortu) tak
 to sortuje dvakrat:

# 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


 
 Pokud dokonce rovnou explicitne reknu,  aby udelal sort (order by) toho
 subselectu tak opet sortuje dvakrat:

# 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 


 Pokud to srovnate  s prvnim prikladem, kde byl schopen  vyuzit order by
 subselectu pro  finalni select  tak mi  z toho  vychazi, ze  planner ma
 problem s unionem.

 Pokusim se na to zeptat moudrejsich.

> byt tak markantni) a je s podivem, ze se "zbytecne" dela 2x po sobe na
> jiz setridenem vysledku - to je gro meho dotazu - proc to nepozna... (a
> tim udela vysledny dotaz 2x drazsi v tomto pripade)

 Souhlas. Myslim, ze je to dobry postreh.

 Jinak   pro  nejake   nasazeni   toho  dotazu   nechapu  pouziti   toho
 subselectu. IMHO by melo stacit order by za unionem.

    select data from addr union select data from addr2 order by data

    Karel

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