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 A60649DC866 for ; Wed, 1 Feb 2006 16:00:20 -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 22618-02 for ; Wed, 1 Feb 2006 16:00:20 -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 EC79C9DC85F for ; Wed, 1 Feb 2006 16:00:14 -0400 (AST) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Wed, 01 Feb 2006 14:00:15 -0600 Message-Id: <43E0BEDD.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0 Date: Wed, 01 Feb 2006 13:59:57 -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> In-Reply-To: <3759.1138822464@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.016 required=5 tests=[AWL=0.016] X-Spam-Score: 0.016 X-Spam-Level: X-Archive-Number: 200602/21 X-Sequence-Number: 17007 >>> On Wed, Feb 1, 2006 at 1:34 pm, in message <3759.1138822464@sss.pgh.pa.us>, Tom Lane wrote: > "Kevin Grittner" writes: >> We do have a few queries where PostgreSQL is several orders of >> magnitude slower. It appears that the reason it is choosing a bad plan >> is that it is reluctant to start from a subquery when there is an outer >> join in the FROM clause. > > AFAICT this case doesn't really hinge on the outer join at all. The > problem is that EXISTS subqueries aren't well optimized. I would have > 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. The EXISTS never chose a reasonable plan on this one, although we haven't had a problem with them in most cases. > What PG version is this exactly? select version() reports: PostgreSQL 8.1.2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) However, this was actually built off the 8.1 stable branch as of Jan. 13th at about 3 p.m. This build does contain the implementation of standard_conforming_strings for which I recently posted a patch. The make was configured with: --enable-integer-datetimes --enable-debug --disable-nls > >> ... The third query is the fastest, but isn't >> portable enough for our mixed environment. > > Not really relevant to the problem, but what's wrong with it? Looks > like standard SQL to me. It is absolutely compliant with the standards. Unfortunately, we are under a "lowest common denominator" portability mandate. I notice that support for this syntax has improved since we last set our limits; I'll try to get this added to our allowed techniques. I can't complain about the portability mandate -- without it, we would undoubtedly have had product specific code for the commercial product which would have made migration to PostgreSQL much more painful. -Kevin