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 C5D8CD1B4EE for ; Wed, 10 Sep 2003 00:22:54 +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 14592-09 for ; Tue, 9 Sep 2003 21:22:26 -0300 (ADT) Received: from wight.ymogen.net (unknown [217.27.240.153]) by svr1.postgresql.org (Postfix) with SMTP id BD970D1B4E5 for ; Tue, 9 Sep 2003 21:22:21 -0300 (ADT) Received: (qmail 8596 invoked from network); 10 Sep 2003 00:22:09 -0000 Received: from dsl-217-155-239-51.zen.co.uk (HELO finisterre) (217.155.239.51) by wight.ymogen.net with SMTP; 10 Sep 2003 00:22:09 -0000 From: "Matt Clark" To: "Tom Lane" Cc: "scott.marlowe" , Subject: Re: slow plan for min/max Date: Wed, 10 Sep 2003 01:22:09 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) In-Reply-To: <16728.1063074086@sss.pgh.pa.us> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/163 X-Sequence-Number: 3529 > > 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: ...snip of cunning potentially geralisable plan... > 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 ... So, you're not going to code it, I'm not going to code it, I doubt anyone else is soon. The issue is going to remain then, that max() and min() are implemented in a way that is grossly counterintuitively slow for 99% of uses. It's not bad, or wrong, just a consequence of many higher level factors. This should therefore be very prominently flagged in the docs until there is either a general or specific solution. FYI I have rewritten 4 queries today to work around this (with nice performance benefits) as a result of this thread. Yeah, I should have spotted the _silly_ seq scans beforehand, but if you're not looking, you don't tend to see. Best improvement is 325msec to 0.60msec! I'm happy to do the doc work. M