select ... distinct performance
От | Don Bowman |
---|---|
Тема | select ... distinct performance |
Дата | |
Msg-id | FE045D4D9F7AED4CBFF1B3B813C85337045D7D52@mail.sandvine.com обсуждение исходный текст |
Ответы |
Re: select ... distinct performance
|
Список | 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
В списке pgsql-general по дате отправления: