X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DBC3656588 for ; Wed, 16 Mar 2005 18:38:43 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00713-01 for ; Wed, 16 Mar 2005 18:38:41 +0000 (GMT) Received: from stan.aopsys (aopsysadsl.net1.nerim.net [62.212.101.94]) by svr1.postgresql.org (Postfix) with ESMTP id 7471D56621 for ; Wed, 16 Mar 2005 18:38:38 +0000 (GMT) Received: from [127.0.0.1] (helo=localhost ident=laurent) by stan.aopsys with esmtp (Exim 4.44) id 1DBdPa-0001UV-T4; Wed, 16 Mar 2005 19:38:30 +0100 To: "Merlin Moncure" Cc: Subject: Re: Speeding up select distinct References: <6EE64EF3AB31D5448D0007DD34EEB3412A7650@Herge.rcsinc.local> From: Laurent Martelli Organization: AOPSYS Date: Wed, 16 Mar 2005 19:38:30 +0100 In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3412A7650@Herge.rcsinc.local> (Merlin Moncure's message of "Wed, 16 Mar 2005 13:19:09 -0500") Message-ID: <877jk74es9.fsf@stan.aopsys> User-Agent: Gnus/5.1007 (Gnus v5.10.7) Emacs/20.7 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/337 X-Sequence-Number: 11042 >>>>> "Merlin" == Merlin Moncure 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 laurent@aopsys.com Java Aspect Components http://www.aopsys.com/ http://jac.objectweb.org