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 D612956588 for ; Wed, 16 Mar 2005 18:41:55 +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 00738-06 for ; Wed, 16 Mar 2005 18:41:52 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id A8B4C565B4 for ; Wed, 16 Mar 2005 18:41:51 +0000 (GMT) Received: from dyn-70-66.tor.dsl.tht.net (dyn-70-66.tor.dsl.tht.net [134.22.70.66]) by tht.net (Postfix) with ESMTP id 66C2D76B09; Wed, 16 Mar 2005 13:41:55 -0500 (EST) Subject: Re: Speeding up select distinct From: Rod Taylor To: Laurent Martelli Cc: pgsql-performance@postgresql.org In-Reply-To: <87br9j4f4d.fsf@stan.aopsys> References: <87k6o74gms.fsf@stan.aopsys> <1110996623.1132.244.camel@home> <87br9j4f4d.fsf@stan.aopsys> Content-Type: text/plain Date: Wed, 16 Mar 2005 13:41:04 -0500 Message-Id: <1110998464.1132.252.camel@home> Mime-Version: 1.0 X-Mailer: Evolution 2.2.0 FreeBSD GNOME Team Port Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.011 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/338 X-Sequence-Number: 11043 On Wed, 2005-03-16 at 19:31 +0100, Laurent Martelli wrote: > >>>>> "Rod" == Rod Taylor writes: > > Rod> On Wed, 2005-03-16 at 18:58 +0100, Laurent Martelli wrote: > >> Consider this query: > >> > >> SELECT distinct owner from pictures; > > Rod> The performance has nothing to do with the number of rows > Rod> returned, but rather the complexity of calculations and amount > Rod> of data to sift through in order to find it. > > Yes, but I thought that an index might be able to know what distinct > values there are and help optime that query very much. The index does know. You just have to visit all of the pages within the index to find out, which it does, and that's why you dropped 10ms. But if you want a sub ms query, you're going to have to normalize the structure. --