Postgres Pro
Downloads
Home   >   mailing lists

Thread: slow UNIONing

slow UNIONing

From
Kovacs Zoltan
Date:
I experienced that UNIONs in 7.1.1 are rather slow:

tir=# explain (select nev from cikk) union (select tevekenyseg from log);
NOTICE:  QUERY PLAN:

Unique  (cost=667.63..687.18 rows=782 width=12) ->  Sort  (cost=667.63..667.63 rows=7817 width=12)       ->  Append
(cost=0.00..162.17rows=7817 width=12)             ->  Subquery Scan *SELECT* 1  (cost=0.00..28.16 rows=1316 width=12)
               ->  Seq Scan on cikk  (cost=0.00..28.16 rows=1316 width=12)             ->  Subquery Scan *SELECT* 2
(cost=0.00..134.01rows=6501 width=12)                   ->  Seq Scan on log  (cost=0.00..134.01 rows=6501 width=12)
 

Of course a simple SELECT is fast:

tir=# explain select nev from cikk;
NOTICE:  QUERY PLAN:

Seq Scan on cikk  (cost=0.00..28.16 rows=1316 width=12)


For me it seems to be slow due to the sorting. Is this right?
Is this normal at all? Is it possible to make it faster?

TIA, Zoltan

--                         Kov\'acs, Zolt\'an                        kovacsz@pc10.radnoti-szeged.sulinet.hu
          http://www.math.u-szeged.hu/~kovzol                        ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz

Re: slow UNIONing

From
Barry Lind
Date:
Kovacs,


A 'union all' will be much faster than 'union'.  'union all' returns all 
results from both queries, whereas 'union' will return all distinct 
records.  The 'union' requires a sort and a merge to remove the 
duplicate values.  Below are explain output for a union query and a 
union all query.

files=# explain
files-# select dummy from test
files-# union all
files-# select dummy from test;
NOTICE:  QUERY PLAN:

Append  (cost=0.00..40.00 rows=2000 width=12)  ->  Subquery Scan *SELECT* 1  (cost=0.00..20.00 rows=1000 width=12)
 ->  Seq Scan on test  (cost=0.00..20.00 rows=1000 width=12)  ->  Subquery Scan *SELECT* 2  (cost=0.00..20.00 rows=1000
width=12)       ->  Seq Scan on test  (cost=0.00..20.00 rows=1000 width=12)
 

EXPLAIN
files=# explain
files-# select dummy from test
files-# union
files-# select dummy from test;
NOTICE:  QUERY PLAN:

Unique  (cost=149.66..154.66 rows=200 width=12)  ->  Sort  (cost=149.66..149.66 rows=2000 width=12)        ->  Append
(cost=0.00..40.00rows=2000 width=12)              ->  Subquery Scan *SELECT* 1  (cost=0.00..20.00 rows=1000 
 
width=12)                    ->  Seq Scan on test  (cost=0.00..20.00 rows=1000 
width=12)              ->  Subquery Scan *SELECT* 2  (cost=0.00..20.00 rows=1000 
width=12)                    ->  Seq Scan on test  (cost=0.00..20.00 rows=1000 
width=12)

EXPLAIN
files=#


thanks,
--Barry


Kovacs Zoltan wrote:

> I experienced that UNIONs in 7.1.1 are rather slow:
> 
> tir=# explain (select nev from cikk) union (select tevekenyseg from log);
> NOTICE:  QUERY PLAN:
> 
> Unique  (cost=667.63..687.18 rows=782 width=12)
>   ->  Sort  (cost=667.63..667.63 rows=7817 width=12)
>         ->  Append  (cost=0.00..162.17 rows=7817 width=12)
>               ->  Subquery Scan *SELECT* 1  (cost=0.00..28.16 rows=1316 width=12)
>                     ->  Seq Scan on cikk  (cost=0.00..28.16 rows=1316 width=12)
>               ->  Subquery Scan *SELECT* 2  (cost=0.00..134.01 rows=6501 width=12)
>                     ->  Seq Scan on log  (cost=0.00..134.01 rows=6501 width=12)
> 
> Of course a simple SELECT is fast:
> 
> tir=# explain select nev from cikk;
> NOTICE:  QUERY PLAN:
> 
> Seq Scan on cikk  (cost=0.00..28.16 rows=1316 width=12)
> 
> 
> For me it seems to be slow due to the sorting. Is this right?
> Is this normal at all? Is it possible to make it faster?
> 
> TIA, Zoltan
> 
>

Re: slow UNIONing

From
"Zeugswetter Andreas SB SD"
Date:
> I experienced that UNIONs in 7.1.1 are rather slow:
> 
> tir=# explain (select nev from cikk) union (select 
> tevekenyseg from log);
> NOTICE:  QUERY PLAN:
> 
> Unique  (cost=667.63..687.18 rows=782 width=12)
>   ->  Sort  (cost=667.63..667.63 rows=7817 width=12)
>         ->  Append  (cost=0.00..162.17 rows=7817 width=12)
>               ->  Subquery Scan *SELECT* 1  (cost=0.00..28.16 
> rows=1316 width=12)
>                     ->  Seq Scan on cikk  (cost=0.00..28.16 
> rows=1316 width=12)
>               ->  Subquery Scan *SELECT* 2  
> (cost=0.00..134.01 rows=6501 width=12)
>                     ->  Seq Scan on log  (cost=0.00..134.01 
> rows=6501 width=12)
> 
> Of course a simple SELECT is fast:
> 
> tir=# explain select nev from cikk;
> NOTICE:  QUERY PLAN:
> 
> Seq Scan on cikk  (cost=0.00..28.16 rows=1316 width=12)
> 
> 
> For me it seems to be slow due to the sorting. Is this right?
> Is this normal at all? Is it possible to make it faster?

If you know, that your result does not produce duplicates
(which are filtered away with "union") you can use a 
"union all" which should be substantially faster, since it does 
not need to sort.

Andreas

Re: slow UNIONing

From
Peter Eisentraut
Date:
Kovacs Zoltan writes:

> I experienced that UNIONs in 7.1.1 are rather slow:
>
> tir=# explain (select nev from cikk) union (select tevekenyseg from log);

Try UNION ALL.  Plain UNION will eliminate duplicates, so it becomes
slower.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: slow UNIONing

From
Kovacs Zoltan
Date:
> > For me it seems to be slow due to the sorting. Is this right?
> > Is this normal at all? Is it possible to make it faster?
> 
> If you know, that your result does not produce duplicates
> (which are filtered away with "union") you can use a 
> "union all" which should be substantially faster, since it does 
> not need to sort.

Thank you to all who helped. I knew nothing about UNION ALL, but now it's
OK. Regards, Zoltan