Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 5C0CA9F9978 for ; Wed, 5 Sep 2007 11:29:06 -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 47956-02 for ; Wed, 5 Sep 2007 11:28:55 -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 B81749F9BDA for ; Wed, 5 Sep 2007 11:29:01 -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 1ISvsD-0004yB-Vb; Wed, 05 Sep 2007 15:28:54 +0100 To: "Pavel Stehule" Cc: "Peter Childs" , Subject: Re: optimize query with a maximum(date) extraction In-Reply-To: <162867790709050548l3e1fd155g40b745f2ae42834e@mail.gmail.com> (Pavel Stehule's message of "Wed\, 5 Sep 2007 14\:48\:18 +0200") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.performance" 2267) References: <302443.65402.qm@web26209.mail.ukl.yahoo.com> <87abs1qrhu.fsf@oxford.xeocode.com> <87642pqpue.fsf@oxford.xeocode.com> <162867790709050548l3e1fd155g40b745f2ae42834e@mail.gmail.com> From: Gregory Stark Organization: EnterpriseDB Date: Wed, 05 Sep 2007 15:28:53 +0100 Message-ID: <87ps0xp4nu.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/47 X-Sequence-Number: 26592 "Pavel Stehule" writes: >> >> 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. As I mentioned in the other post that's not true for this query. > without any limits, seq scan is optimal. That's not necessarily true either. You could have ten distinct ids and millions of dates for them. In that case a scan of the index which jumped around to scan from the beginning and end of each distinct id value would be faster. There's just no such plan type in Postgres currently. You can simulate such a plan with the subqueries I described but there's a bit more overhead than necessary and you need a reasonably efficient source of the distinct ids. Also it may or may not be faster than simply scanning the whole table like above and simulating it with subqueries makes it impossible to choose the best plan. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com