Re: BUG #4462: Adding COUNT to query causes massive slowdown

"Jussi Pakkanen" <jpakkane(at)gmail(dot)com> writes:
> Given that PostgreSQL does the scan even with the huge seqscan
> penalty, I can think of only two different causes:
> 1) some sort of a bug in the query analyzer
> 2) SELECT COUNT(DISTINCT x) for some reason requires information that
> is not available in the index.

Try (3) COUNT(DISTINCT x) ... or any DISTINCT aggregate for that matter
... is implemented by a sort-and-uniq step inside the aggregate function
itself. You can't see it in the plan.

I wouldn't actually think that this approach would be slower than an
indexscan, btw, unless maybe the index were very nearly correlated with
physical order --- but that would make the sort more efficient, too.
Perhaps you need to raise work_mem enough to allow the sort to take
place without spilling to disk? (Turning on trace_sort should let you
see what's happening there.)

regards, tom lane