Re: OT: seeking query help, where?

"Ross J. Reedstrom" <reedstrm@rice.edu> 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=10000loops=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=10000loops=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