Обсуждение: slow UNIONing
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
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 > >
> 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