select ... distinct performance

From: Don Bowman <don(at)sandvine(dot)com>
To: "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org>
Subject: select ... distinct performance
Date: 2004-01-29 04:20:30
Message-ID: FE045D4D9F7AED4CBFF1B3B813C85337045D7D52@mail.sandvine.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a table with a large number of rows (10K in the example below,
but >1M in some databases). I would like to find the distinct
values for one of the columns. The column is indexed.

I would have expected that this would be a very fast operation,
simply walking down the index. In the example below, there is
only 1 unique value, but it takes 2 seconds. I would have
expected more like ~50ms.

explain analyze select distinct element from elem_trafficstats ;
NOTICE: QUERY PLAN:

Unique (cost=0.00..4117.18 rows=9350 width=44) (actual time=0.59..1710.34
rows=1 loops=1)
-> Index Scan using elem_trafficstats_element_idx on elem_trafficstats
(cost=0.00..3883.44 rows=93495 width=44) (actual time=0.58..1184.17
rows=93495 loops=1)
Total runtime: 1710.88 msec

is there an alternate way to construct a 'distinct' query
that will use the index properly?

--don

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Frank Millman 2004-01-29 09:08:58 Problem with function
Previous Message Steve Atkins 2004-01-29 03:24:05 Re: Specifying many rows in a table