Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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

In response to

Responses

pgsql-bugs by date

Next:From: fatih batukDate: 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 PakkanenDate: 2008-10-10 19:55:10
Subject: Re: BUG #4462: Adding COUNT to query causes massive slowdown

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group