I wrote:
> (We might need to tweak the planner to discourage selecting
> HashAggregate in the presence of DISTINCT aggregates --- I don't
> remember whether it accounts for the sortmem usage in deciding
> whether the hash will fit in memory or not ...)
Ah, I take that all back after checking the code: we don't use
HashAggregate at all when there are DISTINCT aggregates, precisely
because of this memory-blow-out problem.
For both your group-by-date query and the original group-by-month query,
the plan of attack is going to be to read the original input in grouping
order (either via sort or indexscan, with sorting probably preferred
unless the table is pretty well correlated with the index) and then
sort/uniq on the DISTINCT value within each group. The OP is probably
losing on that step compared to your test because it's over much larger
groups than yours, forcing some spill to disk. And most likely he's not
got an index on month, so the first sort is in fact a sort and not an
indexscan.
Bottom line is that he's probably doing a ton of on-disk sorting
where you're not doing any. This makes me think Luke's theory about
inadequate disk horsepower may be on the money.
regards, tom lane