"Ross J. Reedstrom" <[EMAIL PROTECTED]> writes:
> On Fri, 17 Jan 2003, Bruno Wolff III wrote:
>> The union operator already removes duplicates.
> Right, which means everyone's UNION queries have too many DISTINCTs
> in them:
Relevant to this thread: if you don't want duplicate removal, write
UNION ALL. This is considerably cheaper than UNION, so it's a good
thing to keep in mind.
Examples:
regression=# explain analyze select unique1 from tenk1 union all select unique2 from
tenk1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..916.00 rows=20000 width=4) (actual time=0.20..1416.60 rows=20000
loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..458.00 rows=10000 width=4) (actual
time=0.18..549.34 rows=10000 loops=1)
-> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=4) (actual
time=0.15..279.58 rows=10000 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..458.00 rows=10000 width=4) (actual
time=0.14..548.90 rows=10000 loops=1)
-> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=4) (actual
time=0.11..278.33 rows=10000 loops=1)
Total runtime: 1570.02 msec
(6 rows)
regression=# explain analyze select unique1 from tenk1 union select unique2 from tenk1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=2344.77..2444.77 rows=20000 width=4) (actual time=2881.85..3477.51
rows=10000 loops=1)
-> Sort (cost=2344.77..2394.77 rows=20000 width=4) (actual time=2881.82..3169.29
rows=20000 loops=1)
Sort Key: unique1
-> Append (cost=0.00..916.00 rows=20000 width=4) (actual time=0.21..1590.55
rows=20000 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..458.00 rows=10000 width=4)
(actual time=0.19..570.31 rows=10000 loops=1)
-> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=4)
(actual time=0.15..293.18 rows=10000 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..458.00 rows=10000 width=4)
(actual time=0.15..698.43 rows=10000 loops=1)
-> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=4)
(actual time=0.12..419.75 rows=10000 loops=1)
Total runtime: 3574.98 msec
(9 rows)
The sort-and-unique phases are what implement duplicate removal, and as
you can see they add a good deal to the cost of the query.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
