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 A31F1D1B50E for ; Tue, 9 Sep 2003 02:21:36 +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 68728-10 for ; Mon, 8 Sep 2003 23:21:29 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id A3C92D1B4E2 for ; Mon, 8 Sep 2003 23:21:25 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h892LQAn016729; Mon, 8 Sep 2003 22:21:26 -0400 (EDT) To: "Matt Clark" Cc: "scott.marlowe" , pgsql-performance@postgresql.org Subject: Re: slow plan for min/max In-reply-to: References: Comments: In-reply-to "Matt Clark" message dated "Tue, 09 Sep 2003 01:42:03 +0100" Date: Mon, 08 Sep 2003 22:21:26 -0400 Message-ID: <16728.1063074086@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/139 X-Sequence-Number: 3505 "Matt Clark" writes: > Know what we (OK, I) need? An explicitly non-aggregate max() and min(), > implemented differently, so they can be optimised. Not per se. The way I've been visualizing this is that we add to pg_aggregate a column named, say, aggsortop, with the definition: zero: no index optimization possible not zero: OID of a comparison operator ('<' or '>') A nonzero entry means that the aggregate's value is the same as the first item of the aggregate's input when sorted by the given operator. (So MIN uses the '<' operator for its datatype and MAX uses '>'.) Of course we have to add a clause to CREATE AGGREGATE to allow this to be set for max/min aggregates of user-defined types. But that's just a small matter of programming. This gives us all the type-specific info we need; the aggsortop can be matched against the opclasses of indexes to figure out whether a particular index is relevant to a particular max or min call. The hard part comes in teaching the planner to use this information intelligently. Exactly which queries is it even *possible* to use the transformation for? Which queries is it really a win for? (Obviously it's not if there's no matching index, but even if there is, the presence of WHERE or GROUP BY clauses has got to affect the answer.) How do you structure the resulting query plan, if it's at all complex (think multiple aggregate calls...)? I'm not clear on the answers to any of those questions, so I'm not volunteering to try to code it up ... regards, tom lane