Re: Problem with GEQO when using views and nested selects

Jeff Davis <davis(at)netcomuk(dot)co(dot)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

This is not actually using GEQO. The reason you are seeing an effect
from raising geqo_threshold is that geqo_threshold determines whether
or not the view will be flattened into the upper query. For this
particular query situation, flattening the view is essential (since you
don't want the thing to compute the whole view). The relevant source
code tidbit is

/*
* Yes, so do we want to merge it into parent? Always do
* so if child has just one element (since that doesn't
* make the parent's list any longer). Otherwise we have
* to be careful about the increase in planning time
* caused by combining the two join search spaces into
* one. Our heuristic is to merge if the merge will
* produce a join list no longer than GEQO_RELS/2.
* (Perhaps need an additional user parameter?)
*/

AFAICS, your only good solution is to make geqo_threshold at least 14,
since you want a 7-way join after flattening.

regards, tom lane