"Jussi Pakkanen" <jpakkane(at)gmail(dot)com> writes:
> On Thu, Oct 9, 2008 at 6:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
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
> 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
In response to
pgsql-bugs by date
|Next:||From: fatih batuk||Date: 2008-10-13 08:24:03|
|Subject: initdb problem => creating template1 database in C:/Program Files/.. ... child process exited with exit code 1|
|Previous:||From: Jussi Pakkanen||Date: 2008-10-10 19:55:10|
|Subject: Re: BUG #4462: Adding COUNT to query causes massive slowdown|