Re: slow UNIONing
От | Barry Lind |
---|---|
Тема | Re: slow UNIONing |
Дата | |
Msg-id | 3BA80342.4000305@xythos.com обсуждение исходный текст |
Ответ на | slow UNIONing (Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>) |
Список | pgsql-hackers |
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 > >
В списке pgsql-hackers по дате отправления: