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])
