Re: SELECT DISTINCT very slow

On Thursday 09 July 2009 17:09:13 Ben Harper wrote:
> Hi,
> Can anybody explain this:
>
> Records: 600,000
> Field Width: Approximately 20 UTF8 characters - type is VARCHAR(25)
> Field is Indexed.
>
> SELECT DISTINCT field FROM table;
>
> Takes about 6 seconds. There are 111 distinct items.
>
> On Sqlite, and another place where I have a B+Tree, this query is
> faster than my eye can measure.
>
> Is this a well known issue?
Yes, I think so.

AFAIK the primary cause is that indexes in pg do not store visibility
information. That means you need to check for existence of the tuple on the
heap.
Possibly due to that PG has no special case code for DISTINCT to optimize such
a query using mostly the index. It would be possible that for each possible
value of 'field' you check the index only long enough to prove that there is at
least one such entry.

Taking that single field into its own table is not possible?

Andres