1 /*------------------------------------------------------------------------
4 * solution to the query optimization problem
5 * by means of a Genetic Algorithm (GA)
7 * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
8 * Portions Copyright (c) 1994, Regents of the University of California
10 * $PostgreSQL: pgsql/src/backend/optimizer/geqo/geqo_main.c,v 1.43 2004/01/23 23:54:21 tgl Exp $
12 *-------------------------------------------------------------------------
16 =*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
17 * Martin Utesch * Institute of Automatic Control *
18 = = University of Mining and Technology =
19 * utesch@aut.tu-freiberg.de * Freiberg, Germany *
20 =*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
23 /* -- parts of this are adapted from D. Whitley's Genitor algorithm -- */
30 #include "optimizer/geqo.h"
31 #include "optimizer/geqo_misc.h"
32 #include "optimizer/geqo_mutation.h"
33 #include "optimizer/geqo_pool.h"
34 #include "optimizer/geqo_selection.h"
38 * Configuration options
43 double Geqo_selection_bias;
46 static int gimme_pool_size(int nr_rel);
47 static int gimme_number_generations(int pool_size);
49 /* define edge recombination crossover [ERX] per default */
50 #if !defined(ERX) && \
62 * solution of the query optimization problem
63 * similar to a constrained Traveling Salesman Problem (TSP)
67 geqo(Query *root, int number_of_rels, List *initial_rels)
69 GeqoEvalData evaldata;
82 Edge *edge_table; /* list of edges */
83 int edge_failures = 0;
86 #if defined(CX) || defined(PX) || defined(OX1) || defined(OX2)
87 City *city_table; /* list of cities */
96 evaldata.initial_rels = initial_rels;
98 /* set GA parameters */
99 pool_size = gimme_pool_size(number_of_rels);
100 number_generations = gimme_number_generations(pool_size);
101 status_interval = 10;
103 /* allocate genetic pool memory */
104 pool = alloc_pool(pool_size, number_of_rels);
106 /* random initialization of the pool */
107 random_init_pool(pool, &evaldata);
109 /* sort the pool according to cheapest path as fitness */
110 sort_pool(pool); /* we have to do it only one time, since
111 * all kids replace the worst individuals
112 * in future (-> geqo_pool.c:spread_chromo
116 elog(DEBUG1, "GEQO selected %d pool entries, best %.2f, worst %.2f",
119 pool->data[pool_size - 1].worth);
122 /* allocate chromosome momma and daddy memory */
123 momma = alloc_chromo(pool->string_length);
124 daddy = alloc_chromo(pool->string_length);
128 elog(DEBUG2, "using edge recombination crossover [ERX]");
130 /* allocate edge table memory */
131 edge_table = alloc_edge_table(pool->string_length);
134 elog(DEBUG2, "using partially matched crossover [PMX]");
136 /* allocate chromosome kid memory */
137 kid = alloc_chromo(pool->string_length);
140 elog(DEBUG2, "using cycle crossover [CX]");
142 /* allocate city table memory */
143 kid = alloc_chromo(pool->string_length);
144 city_table = alloc_city_table(pool->string_length);
147 elog(DEBUG2, "using position crossover [PX]");
149 /* allocate city table memory */
150 kid = alloc_chromo(pool->string_length);
151 city_table = alloc_city_table(pool->string_length);
154 elog(DEBUG2, "using order crossover [OX1]");
156 /* allocate city table memory */
157 kid = alloc_chromo(pool->string_length);
158 city_table = alloc_city_table(pool->string_length);
161 elog(DEBUG2, "using order crossover [OX2]");
163 /* allocate city table memory */
164 kid = alloc_chromo(pool->string_length);
165 city_table = alloc_city_table(pool->string_length);
169 /* my pain main part: */
170 /* iterative optimization */
172 for (generation = 0; generation < number_generations; generation++)
174 /* SELECTION: using linear bias function */
175 geqo_selection(momma, daddy, pool, Geqo_selection_bias);
178 /* EDGE RECOMBINATION CROSSOVER */
179 difference = gimme_edge_table(momma->string, daddy->string, pool->string_length, edge_table);
183 /* are there any edge failures ? */
184 edge_failures += gimme_tour(edge_table, kid->string, pool->string_length);
186 /* PARTIALLY MATCHED CROSSOVER */
187 pmx(momma->string, daddy->string, kid->string, pool->string_length);
189 /* CYCLE CROSSOVER */
190 cycle_diffs = cx(momma->string, daddy->string, kid->string, pool->string_length, city_table);
191 /* mutate the child */
192 if (cycle_diffs == 0)
195 geqo_mutation(kid->string, pool->string_length);
198 /* POSITION CROSSOVER */
199 px(momma->string, daddy->string, kid->string, pool->string_length, city_table);
201 /* ORDER CROSSOVER */
202 ox1(momma->string, daddy->string, kid->string, pool->string_length, city_table);
204 /* ORDER CROSSOVER */
205 ox2(momma->string, daddy->string, kid->string, pool->string_length, city_table);
209 /* EVALUATE FITNESS */
210 kid->worth = geqo_eval(kid->string, pool->string_length, &evaldata);
212 /* push the kid into the wilderness of life according to its worth */
213 spread_chromo(kid, pool);
217 if (status_interval && !(generation % status_interval))
218 print_gen(stdout, pool, generation);
224 #if defined(ERX) && defined(GEQO_DEBUG)
225 if (edge_failures != 0)
226 elog(LOG, "[GEQO] failures: %d, average: %d",
227 edge_failures, (int) number_generations / edge_failures);
229 elog(LOG, "[GEQO] no edge failures detected");
232 #if defined(CX) && defined(GEQO_DEBUG)
234 elog(LOG, "[GEQO] mutations: %d, generations: %d",
235 mutations, number_generations);
237 elog(LOG, "[GEQO] no mutations processed");
241 print_pool(stdout, pool, 0, pool_size - 1);
245 elog(DEBUG1, "GEQO best is %.2f after %d generations",
246 pool->data[0].worth, number_generations);
251 * got the cheapest query tree processed by geqo; first element of the
252 * population indicates the best query tree
254 best_tour = (Gene *) pool->data[0].string;
256 /* root->join_rel_list will be modified during this ! */
257 best_rel = gimme_tree(best_tour, pool->string_length, &evaldata);
259 if (best_rel == NULL)
260 elog(ERROR, "failed to make a valid plan");
262 /* DBG: show the query plan */
264 print_plan(best_plan, root);
267 /* ... free memory stuff */
272 free_edge_table(edge_table);
277 free_city_table(city_table);
280 free_city_table(city_table);
283 free_city_table(city_table);
286 free_city_table(city_table);
296 * Return either configured pool size or a good default
298 * The default is based on query size (no. of relations) = 2^(QS+1),
299 * but constrained to a range based on the effort value.
302 gimme_pool_size(int nr_rel)
308 /* Legal pool size *must* be at least 2, so ignore attempt to select 1 */
309 if (Geqo_pool_size >= 2)
310 return Geqo_pool_size;
312 size = pow(2.0, nr_rel + 1.0);
314 maxsize = 50 * Geqo_effort; /* 50 to 500 individuals */
318 minsize = 10 * Geqo_effort; /* 10 to 100 individuals */
322 return (int) ceil(size);
327 * Return either configured number of generations or a good default
329 * The default is the same as the pool size, which allows us to be
330 * sure that less-fit individuals get pushed out of the breeding
331 * population before the run finishes.
334 gimme_number_generations(int pool_size)
336 if (Geqo_generations > 0)
337 return Geqo_generations;