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
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 |