Re: Problem with GEQO when using views and nested selects

 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.