Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id A629813364DE for ; Tue, 1 Feb 2011 13:24:52 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 54446-10 for ; Tue, 1 Feb 2011 17:24:45 +0000 (UTC) X-Greylist: delayed 00:41:49.567637 by SQLgrey-1.7.6 Received: from cooker.cnx.rice.edu (cooker.cnx.rice.edu [168.7.5.70]) by mail.postgresql.org (Postfix) with ESMTP id E9262133616B for ; Tue, 1 Feb 2011 13:24:44 -0400 (AST) Received: from reedstrm by cooker.cnx.rice.edu with local (Exim 4.63) (envelope-from ) id 1PkJJd-0007GS-JU for pgsql-performance@postgresql.org; Tue, 01 Feb 2011 10:42:53 -0600 Date: Tue, 1 Feb 2011 10:42:53 -0600 From: "Ross J. Reedstrom" To: pgsql-performance@postgresql.org Subject: Re: postgres 9 query performance Message-ID: <20110201164253.GA8310@rice.edu> References: <201101282219.29777.andres@anarazel.de> <21572.1296425895@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <21572.1296425895@sss.pgh.pa.us> User-Agent: Mutt/1.5.13 (2006-08-11) 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: 201102/5 X-Sequence-Number: 42143 On Sun, Jan 30, 2011 at 05:18:15PM -0500, Tom Lane wrote: > Andres Freund writes: > > What happens if you change the > > left join event.origin on event.id = origin.eventid > > into > > join event.origin on event.id = origin.eventid > > ? > > > The EXISTS() requires that origin is not null anyway. (Not sure why the > > planner doesn't recognize that though). > > Sloppy thinking in reduce_outer_joins() is why. Fixed now: > http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=1df57f63f3f60c684aa8918910ac410e9c780713 > > regards, tom lane This is one of the reasons I love open source in general, and PostgreSQL in particular: Tom has the bandwidth to notice these kinds of workarounds being discussed on support lists, and turn them immediately into improvements in the planner. Partly because (I assume, based on the commit message) Andres's parenthetical comment red-flagged it for him, since he knew he could trust Andres's opinion that there was probably a planner improvement hiding here. Amazing! Ross -- Ross Reedstrom, Ph.D. reedstrm@rice.edu Systems Engineer & Admin, Research Scientist phone: 713-348-6166 Connexions http://cnx.org fax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE