Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id AA1CF9F9BFA for ; Wed, 5 Sep 2007 11:23:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 15770-10 for ; Wed, 5 Sep 2007 11:23:27 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from oxford.xeocode.com (unknown [62.232.55.118]) by postgresql.org (Postfix) with ESMTP id 170559F9BB6 for ; Wed, 5 Sep 2007 11:23:33 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.67) (envelope-from ) id 1ISvmt-0004xz-V5; Wed, 05 Sep 2007 15:23:24 +0100 To: "Peter Childs" Cc: Subject: Re: optimize query with a maximum(date) extraction In-Reply-To: (Peter Childs's message of "Wed\, 5 Sep 2007 13\:43\:24 +0100") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.performance" 2266) References: <302443.65402.qm@web26209.mail.ukl.yahoo.com> <87abs1qrhu.fsf@oxford.xeocode.com> <87642pqpue.fsf@oxford.xeocode.com> From: Gregory Stark Organization: EnterpriseDB Date: Wed, 05 Sep 2007 15:23:23 +0100 Message-ID: <87tzq9p4x0.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/46 X-Sequence-Number: 26591 "Peter Childs" writes: > My personal reaction is why are you using distinct at all? > > why not > > select id, > min(the_date) as min_date, > max(the_date) as max_date > from my_table group by id; > > Since 8.0 or was it earlier this will use an index should a reasonable one > exist. That's not true for this query. In fact that was precisely the original query he as looking to optimize. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com