X-Original-To: pgsql-hackers@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CC195475425 for ; Sat, 1 Feb 2003 21:28:57 -0500 (EST) Received: from wolff.to (wolff.to [66.93.249.74]) by postgresql.org (Postfix) with SMTP id 1AB4F474E53 for ; Sat, 1 Feb 2003 21:28:57 -0500 (EST) Received: (qmail 3374 invoked by uid 500); 2 Feb 2003 02:41:56 -0000 Date: Sat, 1 Feb 2003 20:41:56 -0600 From: Bruno Wolff III To: Greg Stark Cc: Tom Lane , Kevin Brown , "'pgsql-hackers@postgresql.org'" Subject: Re: [PERFORM] not using index for select min(...) Message-ID: <20030202024156.GA3353@wolff.to> Mail-Followup-To: Greg Stark , Tom Lane , Kevin Brown , "'pgsql-hackers@postgresql.org'" References: <200301311531.12605.josh@agliodbs.com> <20030201040954.GK15936@perrin.int.nxad.com> <4278.1044074132@sss.pgh.pa.us> <20030201164336.GP12957@filer> <7647.1044119012@sss.pgh.pa.us> <87el6rsqez.fsf@stark.dyndns.tv> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <87el6rsqez.fsf@stark.dyndns.tv> User-Agent: Mutt/1.3.25i X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/47 X-Sequence-Number: 34989 On Sat, Feb 01, 2003 at 15:21:24 -0500, Greg Stark wrote: > Tom Lane writes: > > That just means you need some way for aggregates to declare which records they > need. The only values that seem like they would be useful would be "first > record" "last record" and "all records". Possibly something like "all-nonnull > records" for things like count(), but that might be harder. I don't see how this is going to be all that useful for aggregates in general. min and max are special and it is unlikely that you are going to get much speed up for general aggregate functions. For the case where you really only need to scan a part of the data (say skipping nulls when nearly all of the entries are null), a DBA can add an appropiate partial index and where clause. This will probably happen infrequently enough that adding special checks for this aren't going to pay off. For min and max, it seems to me that putting special code to detect these functions and replace them with equivalent subselects in the case where an index exists (since a sort is worse than a linear scan) is a possible long term solution to make porting easier. In the short term education is the answer. At least the documentation of the min and max functions and the FAQ, and the section with performance tips should recommend the alternative form if there is an appropiate index.