>>>>> "Merlin" == Merlin Moncure <[EMAIL PROTECTED]> writes:
>> Consider this query: >> >> SELECT distinct owner from pictures; Merlin> [...] >> Any ideas, apart from more or less manually maintaining a list of >> distinct owners in another table ? Merlin> you answered your own question. With a 20 row owners table, Merlin> you should be directing your efforts there group by is Merlin> faster than distinct, but both are very wasteful and Merlin> essentially require s full seqscan of the detail table. Merlin> With a little hacking, you can change 'manual maintenance' Merlin> to 'automatic maintenance'. Merlin> 1. create table owner as select distinct owner from Merlin> pictures; 2. alter table owner add constraint Merlin> owner_pkey(owner); 3. alter table pictures add constraint Merlin> ri_picture_owner(owner) references owner; 4. make a little Merlin> append_ownder function which adds an owner to the owner Merlin> table if there is not already one there. Inline this to your Merlin> insert statement on pictures. I just wished there was a means to fully automate all this and render it transparent to the user, just like an index. Merlin> Voila! Merlin p.s. normalize your data always! I have this: pictures( PictureID serial PRIMARY KEY, Owner integer NOT NULL REFERENCES users, [...]); CREATE TABLE users ( UserID serial PRIMARY KEY, Name character varying(255), [...]); Isn't it normalized ? -- Laurent Martelli [EMAIL PROTECTED] Java Aspect Components http://www.aopsys.com/ http://jac.objectweb.org ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]