X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id A726D9DCBD2 for ; Wed, 1 Feb 2006 16:24:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 25203-08 for ; Wed, 1 Feb 2006 16:24:59 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id F00279DCB4B for ; Wed, 1 Feb 2006 16:24:55 -0400 (AST) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Wed, 01 Feb 2006 14:24:57 -0600 Message-Id: <43E0C4A7.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0 Date: Wed, 01 Feb 2006 14:24:39 -0600 From: "Kevin Grittner" To: "Tom Lane" Cc: Subject: Re: Planner reluctant to start from subquery References: <43E0AA7E.EE98.0025.0@wicourts.gov> <43E0AA7E.EE98.0025.0@wicourts.gov> <3759.1138822464@sss.pgh.pa.us> <43E0BEDD.EE98.0025.0@wicourts.gov><43E0BEDD.EE98.0025.0@wicourts.gov> <4218.1138824885@sss.pgh.pa.us> In-Reply-To: <4218.1138824885@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.028 required=5 tests=[AWL=0.028] X-Spam-Score: 0.028 X-Spam-Level: X-Archive-Number: 200602/25 X-Sequence-Number: 17011 >>> On Wed, Feb 1, 2006 at 2:14 pm, in message <4218.1138824885@sss.pgh.pa.us>, Tom Lane wrote: > "Kevin Grittner" writes: >> Tom Lane wrote: >>> ... expected an equivalent IN clause to work better. In fact, I'm not >>> clear why the planner isn't finding the cheapest plan (which it does >>> estimate as cheapest) from the IN version you posted. > >> All I know is that trying various permutations, I saw it pick a good >> plan for the IN format when I eliminated the last outer join in the FROM >> clause. I know it isn't conclusive, but it was a correlation which >> suggested a possible causality to me. > > But there is still an outer join in your third example (the one with the > best plan), so that doesn't seem to hold water. Right, if I moved the DocImageMetaData from a subquery in the WHERE clause up to the FROM clause, or I eliminated all OUTER JOINs, it chose a good plan. Of course, this was just playing with a few dozen permutations, so it proves nothing -- I'm just sayin'.... > In any case, the way > that IN planning works these days it really should have considered the > plan equivalent to your JOIN- against- GROUP- BY variant. > > I'm interested to poke at this ... are you in a position to provide a > test case? I can't supply the original data, since many of the tables have millions of rows, with some of the data (related to juvenile, paternity, sealed, and expunged cases) protected by law. I could try to put together a self-contained example, but I'm not sure the best way to do that, since the table sizes and value distributions may be significant here. Any thoughts on that? -Kevin