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 EDA5B9DCC07 for ; Wed, 1 Feb 2006 16:43:13 -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 28879-06 for ; Wed, 1 Feb 2006 16:43:14 -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 BCCC69DCC3F for ; Wed, 1 Feb 2006 16:43:11 -0400 (AST) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Wed, 01 Feb 2006 14:43:13 -0600 Message-Id: <43E0C8F5.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0 Date: Wed, 01 Feb 2006 14:43:01 -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> <43E0C4A7.EE98.0025.0@wicourts.gov><43E0C4A7.EE98.0025.0@wicourts.gov> <4359.1138826175@sss.pgh.pa.us> In-Reply-To: <4359.1138826175@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.038 required=5 tests=[AWL=0.038] X-Spam-Score: 0.038 X-Spam-Level: X-Archive-Number: 200602/29 X-Sequence-Number: 17015 >>> On Wed, Feb 1, 2006 at 2:36 pm, in message <4359.1138826175@sss.pgh.pa.us>, Tom Lane wrote: > "Kevin Grittner" writes: >> Tom Lane wrote: >>> 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? > > I think that the only aspect of the data that really matters here is the > number of distinct values, which would affect decisions about whether > HashAggregate is appropriate or not. And you could probably get the > same thing to happen with at most a few tens of thousands of rows. > > Also, all we need to worry about is the columns used in the WHERE/JOIN > conditions, which looks to be mostly case numbers, dates, and county > identification ... how much confidential info is there in that? At > worst you could translate the case numbers to some randomly generated > identifiers. OK, I could probably obliterate name, addresses, etc. in a copy of the data (those aren't significant to the query anyway) and provide a test case. However, I just found another clue. Since you were so confident it couldn't be the outer join, I went looking for what else I changed at the same time. I eliminated the code referencing that table, which contained an OR. I've seen ORs cause nasty problems with optimizers in the past. I took out the OR in the where clause, without eliminating that last outer join, and it optimized fine. I'll hold off a bit to see if you still need the test case. ;-) -Kevin