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-09 15:55:10
Message-ID: 16635.1223567710@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"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

In response to

Responses

pgsql-bugs by date

Next:From: Peter EisentrautDate: 2008-10-09 16:09:10
Subject: Re: Locale (unsupported) bug. uk_UA.KOI8-U
Previous:From: Jussi PakkanenDate: 2008-10-09 14:51:48
Subject: Re: BUG #4462: Adding COUNT to query causes massive slowdown

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