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