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

On Sun, Jun 14, 2015 at 6:43 PM, David Kamholz <lautgesetz(at)gmail(dot)com> wrote:
> I've encountered a query with 11 joins whose execution time (i.e., the time
> not taken up by planning) is significantly faster with geqo on rather than
> off. This is surprising to me and seems like it might be a bug in the
> planner, so I am posting it here rather than to -performance.
>
> The query is below, along with EXPLAIN ANALYZE results with geqo on and off.
> The server version is 9.4.4. The various geqo options are all set to the
> default. join_collapse_limit is set to 12 (the query is much slower with it
> set to the default of 8). Let me know what other information might be
> helpful in debugging this further. Thanks!

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.

Anyways, In the non geqo plan, I see lines like this:

-> Nested Loop (cost=0.76..107.61 rows=27 width=20) (actual
time=0.116..797.027 rows=1047967 loops=1)

...that suggest any good result is a matter of luck, more or less; a 5
order of magnitude miss into a nestloop is fodder for unpleasant
results because that error is carried into the estimate itself.

merlin