Re: Speeding up select distinct

> Consider this query:
>
> SELECT distinct owner from pictures;

[...]
> Any ideas, apart from more or less manually maintaining a list of
> distinct owners in another table ?

you answered your own question. With a 20 row owners table, you should
be directing your efforts there group by is faster than distinct, but
both are very wasteful and essentially require s full seqscan of the
detail table.

With a little hacking, you can change 'manual maintenance' to 'automatic
maintenance'.

1. create table owner as select distinct owner from pictures;
2. alter table owner add constraint owner_pkey(owner);
3. alter table pictures add constraint ri_picture_owner(owner)
references owner;
4. make a little append_ownder function which adds an owner to the owner
table if there is not already one there. Inline this to your insert
statement on pictures.

Voila!
Merlin
p.s. normalize your data always!