diff options
| -rw-r--r-- | doc/src/sgml/runtime.sgml | 24 | ||||
| -rw-r--r-- | src/backend/optimizer/geqo/geqo_main.c | 28 | ||||
| -rw-r--r-- | src/backend/utils/misc/guc.c | 22 | ||||
| -rw-r--r-- | src/backend/utils/misc/postgresql.conf.sample | 4 | ||||
| -rw-r--r-- | src/include/optimizer/geqo.h | 15 |
5 files changed, 55 insertions, 38 deletions
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index e464e66878a..f81c920d26d 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.230 2004/01/10 23:28:43 neilc Exp $ +$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.231 2004/01/21 23:33:34 tgl Exp $ --> <Chapter Id="runtime"> @@ -1404,23 +1404,25 @@ SET ENABLE_SEQSCAN TO OFF; </varlistentry> <varlistentry> - <term><varname>geqo_effort</varname> (<type>integer</type>)</term> - <term><varname>geqo_generations</varname> (<type>integer</type>)</term> <term><varname>geqo_pool_size</varname> (<type>integer</type>)</term> + <term><varname>geqo_generations</varname> (<type>integer</type>)</term> + <term><varname>geqo_effort</varname> (<type>integer</type>)</term> <term><varname>geqo_selection_bias</varname> (<type>floating point</type>)</term> <listitem> <para> Various tuning parameters for the genetic query optimization - algorithm: The pool size is the number of individuals in one + 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 <literal>FROM</> items in the query, is taken. The effort is used - to calculate a default for generations. Valid values are between - 1 and 80, 40 being the default. Generations specifies the number - of iterations in the algorithm. The number must be a positive - integer. If 0 is specified then <literal>Effort * - Log2(PoolSize)</literal> is used. The run time of the algorithm - is roughly proportional to the sum of pool size and generations. + number of <literal>FROM</> items in the query, is used. + Generations specifies the number of iterations of the algorithm. + The value must be a positive integer. If 0 is specified then + <literal>Effort * Log2(PoolSize)</literal> is used. + The run time of the algorithm is roughly proportional to the sum of + pool size and generations. + <varname>geqo_effort</varname> 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. diff --git a/src/backend/optimizer/geqo/geqo_main.c b/src/backend/optimizer/geqo/geqo_main.c index 9b16c4a06e6..6a883fa3137 100644 --- a/src/backend/optimizer/geqo/geqo_main.c +++ b/src/backend/optimizer/geqo/geqo_main.c @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/backend/optimizer/geqo/geqo_main.c,v 1.41 2003/11/29 19:51:50 pgsql Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/geqo/geqo_main.c,v 1.42 2004/01/21 23:33:34 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -38,13 +38,13 @@ * Configuration options */ int Geqo_pool_size; -int Geqo_effort; int Geqo_generations; +int Geqo_effort; double Geqo_selection_bias; static int gimme_pool_size(int nr_rel); -static int gimme_number_generations(int pool_size, int effort); +static int gimme_number_generations(int pool_size); /* define edge recombination crossover [ERX] per default */ #if !defined(ERX) && \ @@ -92,7 +92,7 @@ geqo(Query *root, int number_of_rels, List *initial_rels) /* set GA parameters */ pool_size = gimme_pool_size(number_of_rels); - number_generations = gimme_number_generations(pool_size, Geqo_effort); + number_generations = gimme_number_generations(pool_size); status_interval = 10; /* allocate genetic pool memory */ @@ -284,7 +284,7 @@ gimme_pool_size(int nr_rel) { double size; - if (Geqo_pool_size != 0) + if (Geqo_pool_size > 0) return Geqo_pool_size; size = pow(2.0, nr_rel + 1.0); @@ -305,10 +305,20 @@ gimme_pool_size(int nr_rel) * = Effort * Log2(PoolSize) */ static int -gimme_number_generations(int pool_size, int effort) +gimme_number_generations(int pool_size) { - if (Geqo_generations <= 0) - return effort * (int) ceil(log((double) pool_size) / log(2.0)); - else + double gens; + + if (Geqo_generations > 0) return Geqo_generations; + + gens = Geqo_effort * log((double) pool_size) / log(2.0); + + /* bound it to a sane range */ + if (gens <= 0) + gens = 1; + else if (gens > 10000) + gens = 10000; + + return (int) ceil(gens); } diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 45bbfc96ea0..2633bf9e4d3 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -10,7 +10,7 @@ * Written by Peter Eisentraut <peter_e@gmx.net>. * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.177 2004/01/19 19:04:40 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.178 2004/01/21 23:33:34 tgl Exp $ * *-------------------------------------------------------------------- */ @@ -932,22 +932,22 @@ static struct config_int ConfigureNamesInt[] = DEFAULT_GEQO_POOL_SIZE, 0, MAX_GEQO_POOL_SIZE, NULL, NULL }, { - {"geqo_effort", PGC_USERSET, QUERY_TUNING_GEQO, - gettext_noop("GEQO: effort is used to calculate a default for generations."), - NULL - }, - &Geqo_effort, - 1, 1, INT_MAX, NULL, NULL - }, - { {"geqo_generations", PGC_USERSET, QUERY_TUNING_GEQO, - gettext_noop("GEQO: number of iterations in the algorithm."), - gettext_noop("The number must be a positive integer. If 0 is " + gettext_noop("GEQO: number of iterations of the algorithm."), + gettext_noop("The value must be a positive integer. If 0 is " "specified then effort * log2(poolsize) is used.") }, &Geqo_generations, 0, 0, INT_MAX, NULL, NULL }, + { + {"geqo_effort", PGC_USERSET, QUERY_TUNING_GEQO, + gettext_noop("GEQO: effort is used to set the default for generations."), + NULL + }, + &Geqo_effort, + DEFAULT_GEQO_EFFORT, MIN_GEQO_EFFORT, MAX_GEQO_EFFORT, NULL, NULL + }, { {"deadlock_timeout", PGC_SIGHUP, LOCK_MANAGEMENT, diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 98d4d76d3ae..0fdf6b2e99f 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -123,10 +123,10 @@ #geqo = true #geqo_threshold = 11 -#geqo_effort = 1 -#geqo_generations = 0 #geqo_pool_size = 0 # default based on tables in statement, # range 128-1024 +#geqo_generations = 0 # use default: effort * log2(pool_size) +#geqo_effort = 40 # range 1-100 #geqo_selection_bias = 2.0 # range 1.5-2.0 # - Other Planner Options - diff --git a/src/include/optimizer/geqo.h b/src/include/optimizer/geqo.h index 249b4772230..caa05f52ee9 100644 --- a/src/include/optimizer/geqo.h +++ b/src/include/optimizer/geqo.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/optimizer/geqo.h,v 1.33 2003/11/29 22:41:07 pgsql Exp $ + * $PostgreSQL: pgsql/src/include/optimizer/geqo.h,v 1.34 2004/01/21 23:33:34 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -44,22 +44,27 @@ /* * Configuration options + * + * If you change these, update backend/utils/misc/postgresql.sample.conf */ -/* If you change these, update backend/utils/misc/postgresql.sample.conf */ extern int Geqo_pool_size; #define DEFAULT_GEQO_POOL_SIZE 0 /* = default based on no. of relations. */ #define MIN_GEQO_POOL_SIZE 128 #define MAX_GEQO_POOL_SIZE 1024 -extern int Geqo_effort; /* 1 .. inf, only used to calculate - * generations default */ extern int Geqo_generations; /* 1 .. inf, or 0 to use default based on * pool size */ +extern int Geqo_effort; /* only used to calculate default for + * generations */ + +#define DEFAULT_GEQO_EFFORT 40 +#define MIN_GEQO_EFFORT 1 +#define MAX_GEQO_EFFORT 100 + extern double Geqo_selection_bias; -/* If you change these, update backend/utils/misc/postgresql.sample.conf */ #define DEFAULT_GEQO_SELECTION_BIAS 2.0 #define MIN_GEQO_SELECTION_BIAS 1.5 #define MAX_GEQO_SELECTION_BIAS 2.0 |