Dotazovaci plan PostgreSQL

[Ing. Pavel Janousek]

	Zdravim,

	dosel jsem dneska na podivnou vec. Mam tabulky

addr a excalibur s 1 textovym sloupcem adresa (cloaking nedelam:->)

	Muj dotaz zni asi takto - spoj (za sebou) textovy sloupec obou
tabulek, vyluc duplicity a vysledek setrid a vrat.

Zkousel jsem tyto dve veci:

spam=> explain analyse select adresa from (select adresa from addr union
select adresa from excalibur) as foo  order by foo.adresa;        
NOTICE:  QUERY PLAN:

Sort  (cost=1172.33..1172.33 rows=1181 width=17) (actual
time=2707.48..2738.12 rows=10651 loops=1)
  ->  Subquery Scan foo  (cost=1082.57..1112.09 rows=1181 width=17)
(actual time=1486.32..1719.68 rows=10651 loops=1)
        ->  Unique  (cost=1082.57..1112.09 rows=1181 width=17) (actual
time=1486.25..1660.40 rows=10651 loops=1)
              ->  Sort  (cost=1082.57..1082.57 rows=11806 width=17)
(actual time=1486.23..1543.06 rows=12945 loops=1)
                    ->  Append  (cost=0.00..284.06 rows=11806 width=17)
(actual time=0.28..234.98 rows=12945 loops=1)
                          ->  Subquery Scan *SELECT* 1
(cost=0.00..231.65 rows=9465 width=17) (actual time=0.27..155.01
rows=10604 loops=1)
                                ->  Seq Scan on addr  (cost=0.00..231.65
rows=9465 width=17) (actual time=0.25..103.88 rows=10604 loops=1)
                          ->  Subquery Scan *SELECT* 2
(cost=0.00..52.41 rows=2341 width=16) (actual time=2.87..45.75 rows=2341
loops=1)
                                ->  Seq Scan on excalibur
(cost=0.00..52.41 rows=2341 width=16) (actual time=2.84..23.61 rows=2341
loops=1)
Total runtime: 2809.38 msec

EXPLAIN
spam=> explain analyse select distinct(adresa) from (select adresa from
addr union select adresa from excalibur) as foo  order by foo.adresa;
NOTICE:  QUERY PLAN:

Unique  (cost=1172.33..1175.28 rows=118 width=17) (actual
time=2708.67..2817.99 rows=10651 loops=1)
  ->  Sort  (cost=1172.33..1172.33 rows=1181 width=17) (actual
time=2708.64..2752.43 rows=10651 loops=1)
        ->  Subquery Scan foo  (cost=1082.57..1112.09 rows=1181
width=17) (actual time=1499.46..1731.97 rows=10651 loops=1)
              ->  Unique  (cost=1082.57..1112.09 rows=1181 width=17)
(actual time=1499.39..1673.21 rows=10651 loops=1)
                    ->  Sort  (cost=1082.57..1082.57 rows=11806
width=17) (actual time=1499.36..1555.44 rows=12945 loops=1)
                          ->  Append  (cost=0.00..284.06 rows=11806
width=17) (actual time=0.29..228.93 rows=12945 loops=1)
                                ->  Subquery Scan *SELECT* 1
(cost=0.00..231.65 rows=9465 width=17) (actual time=0.29..154.29
rows=10604 loops=1)
                                      ->  Seq Scan on addr
(cost=0.00..231.65 rows=9465 width=17) (actual time=0.27..103.68
rows=10604 loops=1)
                                ->  Subquery Scan *SELECT* 2
(cost=0.00..52.41 rows=2341 width=16) (actual time=2.74..38.10 rows=2341
loops=1)
                                      ->  Seq Scan on excalibur
(cost=0.00..52.41 rows=2341 width=16) (actual time=2.71..24.92 rows=2341
loops=1)
Total runtime: 2880.62 msec

EXPLAIN
spam=> 

	Zatimco v druhem pripade distinct (Unique v nevyssi urovni
planu) v podstate nic nestal (celkove 70ms, to neresim), tak v obou
pripadech je nasledujici:

Sort
Unique
Sort

	Predpokladam, ze Unique je implementovan prave tak, ze
"potrebuje" sort pred sebou a pak je znacne efektivni a navic
nedestruktivni (?) => druhy Sort je zcela zbytecny, protoze Unique
setridenost neporusi... Kdyby to bylo zanedbatelne, tak to neresim, ale
ten druhy sort v podstate cely dotaz zdrazuje skoro na dvojnasobek (viz
actual time).

	Takze jak to, ze unique jednou pozna, ze nemusi nic delat a kdyz
ma sortit, tak nepozna, ze uz to ma hotovo?

-------------------------------------------------------------------
Ing. Pavel Janousek (PaJaSoft)             FoNet, spol. s r. o.
Technicka podpora, Intranet/Internet     Sokolova 67, 619 00 Brno
E-mail: mailto:Janousek na FoNet.Cz         Tel.: +420  5  4324 4749
WWW:    http://WWW.FoNet.Cz/           E-mail: mailto:Info na FoNet.Cz
-------------------------------------------------------------------