Greg Stark <gsstark@mit.edu> writes:
> It was always a bit mysterious to me how postgres could implement
> count(distinct) without introducing a separate sort and aggregate for each
> occurrence.
It can't. There's a sort + uniq + aggregate process done behind the
scenes in the executor for each DISTINCT aggregate. This doesn't show
up on the EXPLAIN output, because the planner has nothing to do with it.
I thought about doing this via a separate hashtable for each group ...
for about a minute. The trouble is you have to run those things in
parallel if you're doing hashed aggregation, so the resources required
are really out of the question in most cases. With the group approach,
the executor is only processing the values for one outer group at a
time, so it only has to run one inner sort + uniq + agg process at a
time.
I suppose we could consider introducing two implementations (hash or
sort/uniq) for a DISTINCT agg within the executor, but it's code that
remains unwritten...
regards, tom lane