Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 1118F1337B58 for ; Mon, 17 Jan 2011 18:11:20 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 38269-08 for ; Mon, 17 Jan 2011 22:11:12 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from lpo-relay-04.vmsinfo.com (lpo-relay-04.vmsinfo.com [216.169.137.115]) by mail.postgresql.org (Postfix) with ESMTP id 8914B1337B3E for ; Mon, 17 Jan 2011 18:11:12 -0400 (AST) X-IronPort-AV: E=Sophos;i="4.60,334,1291611600"; d="scan'208";a="28071847" Received: from unknown (HELO lpo-mailbox-02.vmsinfo.com) ([216.169.137.251]) by lpo-relay-04.vmsinfo.com with ESMTP; 17 Jan 2011 17:11:10 -0500 Received: from [10.101.43.35] (lpo-autonomy-p03.vmsinfo.com [216.169.135.234]) by lpo-mailbox-02.vmsinfo.com (Postfix) with ESMTP id 0D2FE5628E; Mon, 17 Jan 2011 17:11:10 -0500 (EST) Message-ID: <4D34BE7D.3070007@vmsinfo.com> Date: Mon, 17 Jan 2011 17:11:09 -0500 From: Mladen Gogala Organization: VMS User-Agent: Thunderbird 2.0.0.24 (X11/20101027) MIME-Version: 1.0 To: Tom Lane CC: Jeremy Palmer , "pgsql-performance@postgresql.org" Subject: Re: Possible to improve query plan? References: <666FB8D75E95AE42965A0E76A5E5337E06D2C683BF@prdlsmmsg01.ad.linz.govt.nz> <20065.1295295866@sss.pgh.pa.us> In-Reply-To: <20065.1295295866@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201101/143 X-Sequence-Number: 41930 Tom Lane wrote: > If I'm not mistaken, that's a DB2-ish locution It could also be a part of the Oracle vernacular. I've seen queries like that running against Oracle RDBMS, too. > for a query with DISTINCT > ON, ie, you're looking for the row with highest _revision_created for > each value of id. It might perform well on DB2, but it's going to > mostly suck on Postgres --- we don't optimize window-function queries > very much at all at the moment. Hmmm, what optimizations do you have in mind? I thought that window functions are just clever tricks with memory? Anything that can be expected for 9.0x? > Try writing it with DISTINCT ON instead > of a window function, like so: > Wouldn't "distinct" necessarily bring about the sort/merge? -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions