Postgresql – Problem with PostgreSQL and the geqo optimizer

optimizationpostgresql

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?

Best Answer

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.

Related Question