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 2CDD7D1B4EC for ; Mon, 8 Sep 2003 22:43:03 +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 46563-03 for ; Mon, 8 Sep 2003 19:42:33 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id E3441D1B4E9 for ; Mon, 8 Sep 2003 19:42:30 -0300 (ADT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3622037; Mon, 08 Sep 2003 15:42:44 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "scott.marlowe" , Neil Conway Subject: Re: slow plan for min/max Date: Mon, 8 Sep 2003 15:40:48 -0700 User-Agent: KMail/1.4.3 Cc: Pailloncy =?iso-8859-1?q?Jean-G=E9rard?= , PostgreSQL Performance References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200309081540.48926.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/133 X-Sequence-Number: 3499 Scott, =20 > Not exactly. While max(id) is easily optimized by query replacement,=20 > more complex aggregates will still have perfomance issues that would not= =20 > be present in a row locking database. i.e. max((field1/field2)*field3) i= s=20 > still going to cost more to process, isn't it? Sorry, no.=20=20=20 The issue has nothing to do with MVCC. It has everything to do with the f= act=20 that PostgreSQL allows you to create your own aggregates using functions in= =20 any of 11 languages. This forces the planner to treat aggregates as a=20 "black box" which does not allow index utilization, because the planner=20 simply doesn't know what the aggregate is doing internally. To put it another way, the planner sees SUM() or CONCAT() -- which require= =20 table scans as they must include all values -- as identical to MAX() and=20 MIN().=20=20=20=20 Escaping this would require programming a special exception for MAX() and= =20 MIN() into the planner and parser. This has been discussed numerous times= =20 on HACKERS; the problem is, making special exceptions for MAX() and MIN()= =20 would then make it very difficult to implement MAX() or MIN() for new data= =20 types, as well as requiring a lot of debugging in numerous places. So far= ,=20 nobody has been frustrated enough to spend 3 months tackling the problem. --=20 -Josh Berkus Aglio Database Solutions San Francisco