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?

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

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

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