Re: query execution time faster with geqo on than off: bug?

Поиск
Список
Период
Сортировка
От David Kamholz
Тема Re: query execution time faster with geqo on than off: bug?
Дата
Msg-id CAKuxgJ47XePa2zRt40e2yVpfPYEgx1eFh=g7QEJwh7cgkfkFtg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: query execution time faster with geqo on than off: bug?  (Merlin Moncure <mmoncure@gmail.com>)
Список pgsql-hackers
Well, for starters you're looking at an estimation miss.  The
exhaustive search found the 'cheaper' plan than what geqo came up
with, but that did not correlate to execution time.  This is a common
and frustrating problem.  Generally to try and avoid it it's good to
avoid things in tables and queries that the database has difficulty
planning or to crank statistics in specific cases.

RhodiumToad on #postgresql thinks it may be a different issue -- namely, the fact that there are various estimates of rows=1 when the actual number is higher. Increasing default_statistics_target to 1000 and 10000 seems to confirm this -- if anything, the query runs slower, and the plans look to be about the same. So I'm not convinced yet that it's not a bug. The better performance with geqo on is pretty consistent. I recognize that the query is complex, and I can try to simplify it, but it would be nice if its performance were not a matter of luck. 

I've attached a file containing the original query and the EXPLAIN ANALYZE results for geqo on and default_statistics_target 100, geqo off and default_statistics_target 100, geqo on and default_statistics_target 10000, and geqo off and default_statistics_target 10000, showing that the increased statistics target doesn't help. (I figured it would be easier to read as an attachment because my email client automatically wraps long lines.)

Dave
Вложения

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

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: Collection of memory leaks for ECPG driver
Следующее
От: Xiaoyulei
Дата:
Сообщение: does tuple store subtransaction id in it?