Postgresql has a genetic query optimizer (GEQO) that has a parameters like geqo_threshold and geqo_effort that determine when genetic algorithm vs exhaustive search is used and the trade-off between planning time and query plan quality. For larger number of joins the performance does degrade because planning takes longer and/or because the generated query plan is just sub-optimal.
Does SQL Server have similar parameters to controls its query plan optimization? What would be a query to SELECT or find these parameters values?
In Postgresql you can run SHOW ALL and then find the geqo-related parameters. I am expecting there is some similar way to query the SQL Server parameters but have not found one.
1条答案
按热度按时间3b6akqbq1#
No... Because GEQO is a false good idea...
The optimizer (called "planner" in PostGreSQL language) performs an exhaustive search of all possible combinations to evaluate different query plans and stops during the development of a plan if the current cost of path is more important than the first estimated plan, otherwise, if ultimately the new plan is of lower cost, uses this plan as a new reference until all possibilities are exhausted. This can be extremely long to calculate in the presence of a complex query (exponential cost), or even much longer to compute such a plan than the execution of the query with a trivial plan...
To get around this problem beyond 12 joins, the PostGreSQL optimizer changes strategy in favor of an algorithm called GEQO ( GEnetic Query Optimization) developed in work in 1997 by the University of Mining and Technology of Freiburg in Germany . Unfortunately GEQO does not really bring any benefit and often the optimizer goes off the rails by proposing totally inadequate query plans...
As early as 2010 it was planned to replace GEQO with a simulated annealing algorithm (See the work of Dušan Petković: Comparison of Different Solutions for Solving the Optimization Problem of Large Join Queries – University of Applied Sciences, Rosenheim, Germany ). None of this work ever came to fruition...
In enterprise RDBMSs such as Microsoft SQL Server, IBM DB2 or Oracle Database, research has produced very different results arriving through very sophisticated rules to optimize very complex queries with several dozen joins and subqueries with very impressive results. Microsoft has been in the lead for many years in this optimization race. But none of these players communicate on the subject, quite simply to maintain a technological lead which has a very high R&D cost and which translates into high licensing costs in order to pay engineers' salaries...
Just have a look on performances comparison between PostGreSQL and MS SQL Server with those papers :
PostGreSQL vs Microsoft SQL Server – Comparison part 1 : DBA command performances
PostGreSQL vs Microsoft SQL Server – Comparison part 2 : COUNT performances
Since then the subject is still under construction in PostGreSQL, but obviously completely out of scope….
And I confirm to you that there is no parameter allowing you to modify the behavior of the optimizer (with the exception of trace flag for very specific cases and which should be avoided) because Microsoft SQL Server has an intelligent optimizer which learns from past queries to better optimize future queries... This process is part of a family of set of algorithms ( intelligent query processing ), very new since version 2017 in order to further improve query performance.