From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Jussi Pakkanen" <jpakkane(at)gmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #4462: Adding COUNT to query causes massive slowdown |
Date: | 2008-10-10 21:07:00 |
Message-ID: | 2863.1223672820@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
"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
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | fatih batuk | 2008-10-13 08:24:03 | initdb problem => creating template1 database in C:/Program Files/.. ... child process exited with exit code 1 |
Previous Message | Jussi Pakkanen | 2008-10-10 19:55:10 | Re: BUG #4462: Adding COUNT to query causes massive slowdown |