From 3969f2924bead7847adbe1fd736eefaf138af942 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 23 Jan 2004 23:54:21 +0000 Subject: Revise GEQO planner to make use of some heuristic knowledge about SQL, namely that it's good to join where there are join clauses rather than where there are not. Also enable it to generate bushy plans at need, so that it doesn't fail in the presence of multiple IN clauses containing sub-joins. These changes appear to improve the behavior enough that we can substantially reduce the default pool size and generations count, thereby decreasing the runtime, and yet get as good or better plans as we were getting in 7.4. Consequently, adjust the default GEQO parameters. I also modified the way geqo_effort is used so that it affects both population size and number of generations; it's now useful as a single control to adjust the GEQO runtime-vs-plan-quality tradeoff. Bump geqo_threshold to 12, since even with these changes GEQO seems to be slower than the regular planner at 11 relations. --- doc/src/sgml/runtime.sgml | 32 ++++++++++++++++++++------------ 1 file changed, 20 insertions(+), 12 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index f81c920d26d..ad4a6f98456 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -1,5 +1,5 @@ @@ -1396,7 +1396,7 @@ SET ENABLE_SEQSCAN TO OFF; Use genetic query optimization to plan queries with at least this many FROM items involved. (Note that an outer JOIN construct counts as only one FROM - item.) The default is 11. For simpler queries it is usually best + item.) The default is 12. For simpler queries it is usually best to use the deterministic, exhaustive planner, but for queries with many tables the deterministic planner takes too long. @@ -1404,25 +1404,33 @@ SET ENABLE_SEQSCAN TO OFF; + geqo_effort (integer) geqo_pool_size (integer) geqo_generations (integer) - geqo_effort (integer) geqo_selection_bias (floating point) Various tuning parameters for the genetic query optimization - algorithm. The pool size is the number of individuals in one - population. Valid values are between 128 and 1024. If it is set - to 0 (the default) a pool size of 2^(QS+1), where QS is the - number of FROM items in the query, is used. + algorithm. The recommended one to modify is + geqo_effort, which can range from 1 to 10 with + a default of 5. Larger values increase the time spent in planning + but make it more likely that a good plan will be found. + geqo_effort doesn't actually do anything directly, + it is just used to compute the default values for the other + parameters. If you prefer, you can set the other parameters by hand + instead. + The pool size is the number of individuals in the genetic population. + It must be at least two, and useful values are typically 100 to 1000. + If it is set to zero (the default setting) then a suitable default + is chosen based on geqo_effort and the number of + tables in the query. Generations specifies the number of iterations of the algorithm. - The value must be a positive integer. If 0 is specified then - Effort * Log2(PoolSize) is used. + It must be at least one, and useful values are in the same range + as the pool size. + If it is set to zero (the default setting) then a suitable default + is chosen based on the pool size. The run time of the algorithm is roughly proportional to the sum of pool size and generations. - geqo_effort is only used in computing the default - generations setting, as just described. The default value is 40, - and the allowed range 1 to 100. The selection bias is the selective pressure within the population. Values can be from 1.50 to 2.00; the latter is the default. -- cgit v1.2.3