Re: Planner reluctant to start from subquery

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема Re: Planner reluctant to start from subquery
Дата
Msg-id 43E0BEDD.EE98.0025.0@wicourts.gov
обсуждение исходный текст
Ответ на Re: Planner reluctant to start from subquery  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: Planner reluctant to start from subquery  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
>>> 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



В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Planner reluctant to start from subquery
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Planner reluctant to start from subquery