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

"Jussi Pakkanen" <jpakkane@gmail.com> writes:
> On Thu, Oct 9, 2008 at 6:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.

> Does this mean that the sort-and-uniq will always lead to a full table
> scan?

The sort-and-uniq doesn't care where the data came from.  But if we have
to feed it all rows of the table, as we do here, we're going to use a
seqscan.  An indexscan can never beat a seqscan for retrieving the whole
table.

> A quick calculation says that the table scan needs to access 32
> million elements (and sort them, and uniq them). An index scan needs
> only 2 million (or 4 million I suppose, if you account for the higher
> levels in the B-tree).

You have a fundamental misunderstanding of how Postgres indexes work.
It is never possible to retrieve data without consulting the table too,
because indexes do not store transaction visibility information.

            regards, tom lane