Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
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. –  dezso Sep 23 '13 at 10:20
    
I tried several querys and increasing the complexity but always the same result. Whatever. Thx for your comment. –  balegor Sep 24 '13 at 9:52
    
I'd suggest to ask this on some of the postgres mailing lists. It would be interesting to have a definitive answer. –  dezso Sep 24 '13 at 10:26
    
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. –  balegor Sep 24 '13 at 23:12
add comment

1 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.

share|improve this answer
    
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. –  balegor Sep 24 '13 at 22:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.