Re: Multiple Uniques

Markus Schaber <schabios(at)logi-track(dot)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