Problem with PostgreSQL and the geqo optimizer

Question

I'm trying to compare the two execution plans (exhaustive search and geqo) from the optimizer, but I always get the same plan from both types. These are the variables I use for geqo:

SET geqo = true;
SET geqo_threshold = 2;

My queries have 8-12 FROM items involved, so the optimizer would definitely use the geqo algorithm.

I tried to add more variables to my queries to force the optimizer to choose an other plan:

SET geqo_effort = 2;
SET geqo_pool_size = 2;
SET geqo_generations = 1;

But like I said, I always get the same plan as the plan from exhaustive search.
Is there any possibility to show which algorithm (exhaustive search or geqo) the optimizer used to create the execution plan? Or are there other ways to force the optimizer to use geqo?

• András Váczi: Have you tried it with a more complex query? I can imagine that GEQO finds the same plan for the given query. As complexity raises it is more possible to see the difference... Anyway, I don't know a possibility to indicate the algorithm that created the given plan. Possibly you can modify the source to emit an additional line in the EXPLAIN output.
• balegor: I tried several querys and increasing the complexity but always the same result. Whatever. Thx for your comment.
• András Váczi: I'd suggest to ask this on some of the postgres mailing lists. It would be interesting to have a definitive answer.
• balegor: Finally i've got a diffent plan with a lot more joins. Looks like the initial query was still to simple. And it looks like the geqo algorithim is really good, I have to add some redundant joins to get the result I want. At the end, still with the poor variables for geqo the plan is not that bad. Thx for your help.

Answer 1

I believe the settings you mentioned already ensure that geqo will be used, except that the values for geqo_pool_size and geqo_generations are so low that the results won't be good.

Apart from that, to get different plans, you should play with geqo_seed.

From the doc's section Generating Possible Plans with GEQO:

As long as geqo_seed and the other GEQO parameters are kept fixed, the same plan will be generated for a given query (and other planner inputs such as statistics). To experiment with different search paths, try changing geqo_seed.

About geqo_seed:

Controls the initial value of the random number generator used by GEQO to select random paths through the join order search space. The value can range from zero (the default) to one. Varying the value changes the set of join paths explored, and may result in a better or worse best path being found.

• balegor: Primary I want a different plan, doesn't matter if good or not. I also tried different values for geqo_seed, but like I said, always the same plan and it doesn't matter if I used implicit or explicit joins.