select ... distinct performance

[Don Bowman]

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_trafficsta ts ;
NOTICE: QUERY PLAN:

Unique (cost=0.00..411 7.18 rows=9350 width=44) (actual time=0.59..1710 .34
rows=1 loops=1)
-> Index Scan using elem_trafficsta ts_element_idx on elem_trafficsta ts
(cost=0.00..388 3.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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Martijn van Oosterhout]

On Wed, Jan 28, 2004 at 11:20:30PM -0500, Don Bowman wrote:

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.
The problem is that the index doesn't contain info about which rows are
visibile in your current transaction, so it has to load the entire table to
check. Looks like it used the index to avoid a sort step. I don't think
there is a way to write this that doesn't need the whole table.

Hope this helps,
--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers
anywhere, to being about 20% done. Sweet. And the last 80% usually takes
20% of the time, too, right?) -- Anthony Towns, debian-devel-announce

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAHF+PY5T wig3Ge+YRAnKHAK DCSJtvMmoBQw0aF yW0luvfhgASmgCb B1Rc
s1u49h+ZlpLAtq5 7Rnhpg2U=
=R37U
-----END PGP SIGNATURE-----