Re: slow UNIONing

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.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=#

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
>
>