X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 1489AD1B546 for ; Tue, 9 Sep 2003 17:15:58 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 40340-06 for ; Tue, 9 Sep 2003 14:15:27 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id D0625D1B4E6 for ; Tue, 9 Sep 2003 14:15:25 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3624233; Tue, 09 Sep 2003 10:14:42 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Greg Stark Subject: Re: slow plan for min/max Date: Tue, 9 Sep 2003 10:14:03 -0700 User-Agent: KMail/1.4.3 Cc: PostgreSQL Performance References: <87ad9d52dv.fsf@stark.dyndns.tv> In-Reply-To: <87ad9d52dv.fsf@stark.dyndns.tv> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200309091014.03405.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/157 X-Sequence-Number: 3523 Greg, > The only connection to MVCC is that the "obvious" solution doesn't work, > namely storing a cache of the aggregate in the table information. Well, that solution also doesn't work if you use a WHERE condition or JOIN, now does it? > So what would it take to implement this for "all" aggregates? Where I think > "all" really just means min(), max(), first(), last(). Um, what the heck are first() and last()? These are not supported aggregates ... table rows are *not* ordered. > For min() and max() it would have to indicate not only that only the first > or last record is necessary but also the sort order to impose. I think Tom already suggested this based on adding a field to CREATE AGGREGATE. But I think implementation isn't as simple as you think it is. > Now the problem I see is if there's no index on the sort order imposed, and > the previous step wasn't a merge join or something else that would return > the records in order then it's not necessarily any faster to sort the > records and return only some. It might be for small numbers of records, but > it might be faster to just read them all in and check each one for min/max > the linear way. Yes, Tom mentioned this also. Working out the rules whereby the planner could decide the viability of index use is a non-trivial task. -- Josh Berkus Aglio Database Solutions San Francisco