Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?

От: Tom Lane
Тема: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
Дата: ,
Msg-id: 26772.1266005254@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Karl Denninger)
Ответы: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Bryce Nesbitt)
Список: pgsql-performance

Скрыть дерево обсуждения

512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Bryce Nesbitt, )
 Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Bryce Nesbitt, )
  Re: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Pavel Stehule, )
  Re: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Robert Haas, )
   Re: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  ("Jorge Montero", )
   Re: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Bryce Nesbitt, )
    Re: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Tom Lane, )
     Re: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Bryce Nesbitt, )
     Re: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Bryce Nesbitt, )
    Re: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Robert Haas, )
 Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  ("Kevin Grittner", )
  Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Karl Denninger, )
   Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  ("Kevin Grittner", )
    Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Karl Denninger, )
     Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  ("Kevin Grittner", )
     Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Tom Lane, )
      Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Bryce Nesbitt, )
       Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Pierre Frédéric Caillaud<>, )
       Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Robert Haas, )
  Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?  (Bryce Nesbitt, )

Karl Denninger <> writes:
> Explain Analyze on the alternative CLAIMS the same query planner time
> (within a few milliseconds) with explain analyze.  But if I replace the
> executing code with one that has the alternative ("not exists") syntax
> in it, the system load goes to crap instantly and the execution times
> "in the wild" go bananas.

Could we see the actual explain analyze output, and not some handwaving?

What I would expect 8.4 to do with the NOT EXISTS version is to convert
it to an antijoin --- probably a hash antijoin given that the subtable
is apparently small.  That should be a significant win compared to
repeated seqscans as you have now.  The only way I could see for it to
be a loss is that that join would probably be performed after the other
subplan tests instead of before.  However, the rowcounts for your
original query suggest that all the subplans get executed the same
number of times; so at least on the test values you used here, all
those conditions succeed.  Maybe your test values were not
representative of "in the wild" cases, and in the real usage it's
important to make this test before the others.

If that's what it is, you might see what happens when all of the
sub-selects are converted to exists/not exists style, instead of
having a mishmash...

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: "Kevin Grittner"
Дата:
Сообщение: Re: Almost infinite query -> Different Query Plan when changing where clause value
От: Greg Smith
Дата:
Сообщение: Re: moving pg_xlog -- yeah, it's worth it!