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?
Дата: ,
Msg-id: 28370.1265860369@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: 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?  (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, )

Bryce Nesbitt <> writes:
> The query plans are now attached (sorry I did not start there: many
> lists reject attachments). Or you can click on "text" at the query
> planner analysis site http://explain.depesz.com/s/qYq

At least some of the problem is the terrible quality of the rowcount
estimates in the IN subquery, as you extracted here:

>  Nested Loop  (cost=0.00..23393.15 rows=23 width=4) (actual time=0.077..15.637 rows=4003 loops=1)
>    ->  Index Scan using words_word on words  (cost=0.00..5.47 rows=1 width=4) (actual time=0.049..0.051 rows=1
loops=1)
>          Index Cond: ((word)::text = 'insider'::text)
>    ->  Index Scan using article_words_wc on article_words (cost=0.00..23234.38 rows=12264 width=8) (actual
time=0.020..7.237rows=4003 loops=1) 
>          Index Cond: (article_words.word_key = words.word_key)
>  Total runtime: 19.776 ms

Given that it estimated 1 row out of "words" (quite correctly) and 12264
rows out of each scan on article_words, you'd think that the join size
estimate would be 12264, which would be off by "only" a factor of 3 from
the true result.  Instead it's 23, off by a factor of 200 :-(.

Running a roughly similar test case here, I see that 8.4 gives
significantly saner estimates, which I think is because of this patch:
http://archives.postgresql.org/pgsql-committers/2008-10/msg00191.php

At the time I didn't want to risk back-patching it, because there
were a lot of other changes in the same general area in 8.4.  But
it would be interesting to see what happens with your example if
you patch 8.3 similarly.  (Note: I think only the first diff hunk
is relevant to 8.3.)

            regards, tom lane


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

От: Tom Lane
Дата:
Сообщение: Re: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
От: jesper@krogh.cc
Дата:
Сообщение: Re: perf problem with huge table