Re: Planner reluctant to start from subquery

>>> On Wed, Feb 1, 2006 at 1:34 pm, in message
<3759(dot)1138822464(at)sss(dot)pgh(dot)pa(dot)us>,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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