From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Planner reluctant to start from subquery
Date: Wed, 01 Feb 2006 13:59:57 -0600
Message-ID: <43E0BEDD.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <3759.1138822464@sss.pgh.pa.us>
References: <43E0AA7E.EE98.0025.0@wicourts.gov>
<43E0AA7E.EE98.0025.0@wicourts.gov>
<3759.1138822464@sss.pgh.pa.us>
>>> On Wed, Feb 1, 2006 at 1:34 pm, in message
<3759.1138822464@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, tgl@sss.pgh.pa.us
Subject: Re: Planner reluctant to start from subquery
In-Reply-To: <43E0BEDD.EE98.0025.0@wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox