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

Re: BUG #3979: SELECT DISTINCT slow even on indexed column

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: David Lee <david_lee(at)bigfix(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3979: SELECT DISTINCT slow even on indexed column
Date: 2008-02-22 00:37:42
Message-ID: 1203640662.7878.33.camel@dogma.ljc.laika.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Thu, 2008-02-21 at 23:34 +0000, David Lee wrote:
> Finally, I ran:
>  SELECT a, b FROM x GROUP BY a, b;
> 
> But it was still the same.
> 
> Next I created an index on ("a") and ran the query:
>  SELECT DISTINCT a FROM x
> 
> but the same thing happened (first didn't use the index; after turning
> seq-scan off, was still slow; tried using GROUP BY, still slow).
> 
> The columns "a" and "b" are NOT NULL and has 100 distinct values each. The
> indexes are all btree indexes.

If there are only 100 distinct values each, then that's only (at most)
10k distinct (a,b) pairs.

To me it sounds like it would be most efficient to use a HashAggregate,
which can only be used by the "GROUP BY" variant of the query you ran
(DISTINCT can't use that plan).

First, try to force a HashAggregate and see what the results are. If
that is faster, the planner is not choosing the right plan. Try ANALYZE
to update the statistics, and if that doesn't work, post EXPLAIN
results.

Also, this post is somewhat off-topic for -bugs, try posting to -general
or -performance with this type of question.

Regards,
	Jeff Davis


In response to

pgsql-bugs by date

Next:From: Euler Taveira de OliveiraDate: 2008-02-22 02:52:05
Subject: Re: BUG #3975: tsearch2 index should not bomb out of 1Mb limit
Previous:From: David LeeDate: 2008-02-21 23:34:12
Subject: BUG #3979: SELECT DISTINCT slow even on indexed column

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