Tom> Jeff Davis <davis@netcomuk.co.uk> writes:
>> I have been trying tune joins against a view we use a lot for which
>> the optimizer generates very poor query plans when it uses the GEQO.
>> The long involved version (and more readable version) of the problem
>> is here: http://xarg.net/writing/misc/GEQO
Tom> This is not actually using GEQO. The reason you are seeing an effect
Tom> from raising geqo_threshold is that geqo_threshold determines whether
Tom> or not the view will be flattened into the upper query. For this
Tom> particular query situation, flattening the view is essential (since you
Tom> don't want the thing to compute the whole view). The relevant source
Tom> code tidbit is
Tom> /*
Tom> * Yes, so do we want to merge it into parent? Always do
Tom> * so if child has just one element (since that doesn't
Tom> * make the parent's list any longer). Otherwise we have
Tom> * to be careful about the increase in planning time
Tom> * caused by combining the two join search spaces into
Tom> * one. Our heuristic is to merge if the merge will
Tom> * produce a join list no longer than GEQO_RELS/2.
Tom> * (Perhaps need an additional user parameter?)
Tom> */
Tom> AFAICS, your only good solution is to make geqo_threshold at least 14,
Tom> since you want a 7-way join after flattening.
Thanks very much. I have to admit it was all very mysterious to me
and the only knobs I had seemed to indicate that the GEQO was the
issue.
I think having another user parameter as mentioned in the comment is a
good idea (although I see it's been discussed before), that or maybe
some better guidance on the actual interpretation of GEQO_THRESHOLD
(the comment is hugely more illuminating than the documentation on
this point).
Now that I understand what is going on, I know in our case this crops
up a fair bit and no one had really figured ever figured out what was
causing views to work ok some of the time and then fall over in other
queries.