Markus Schaber <[EMAIL PROTECTED]> 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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to