Re: select ... distinct performance

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 <kleptog(at)svana(dot)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