Re: Multiple Uniques

Markus Schaber <schabios@logi-track.com> writes:

> logigis=# explain select count(id) from (select  ref_in_id as id from streets union select  nref_in_id as id from
streets)as blubb; 
>                                                QUERY PLAN
> ---------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=16220893.16..16220893.16 rows=1 width=8)
>    ->  Subquery Scan blubb  (cost=15254815.03..16082881.99 rows=55204464 width=8)
>          ->  Unique  (cost=15254815.03..15530837.35 rows=55204464 width=8)
>                ->  Sort  (cost=15254815.03..15392826.19 rows=55204464 width=8)
>                      Sort Key: id
>                      ->  Append  (cost=0.00..6810225.28 rows=55204464 width=8)
>                            ->  Subquery Scan "*SELECT* 1"  (cost=0.00..3405112.64 rows=27602232 width=8)
>                                  ->  Seq Scan on streets  (cost=0.00..3129090.32 rows=27602232 width=8)
>                            ->  Subquery Scan "*SELECT* 2"  (cost=0.00..3405112.64 rows=27602232 width=8)
>                                  ->  Seq Scan on streets  (cost=0.00..3129090.32 rows=27602232 width=8)

You can actually go one step further and do:

 select count(distinct id) from (select ... union all select ...) as blubb;

I'm not sure why this is any faster since it still has to do all the same
work, but it's a different code path and it seems to be about 20% faster for
me.

--
greg