2 $Header: /cvsroot/pgsql/doc/src/sgml/geqo.sgml,v 1.25 2003/11/24 19:08:02 petere Exp $
9 <firstname>Martin</firstname>
10 <surname>Utesch</surname>
13 University of Mining and Technology
16 Institute of Automatic Control
28 <date>1997-10-02</date>
31 <title id="geqo-title">Genetic Query Optimizer</title>
37 Written by Martin Utesch (<email>utesch@aut.tu-freiberg.de</email>)
38 for the Institute of Automatic Control at the University of Mining and Technology in Freiberg, Germany.
43 <sect1 id="geqo-intro">
44 <title>Query Handling as a Complex Optimization Problem</title>
47 Among all relational operators the most difficult one to process
48 and optimize is the <firstterm>join</firstterm>. The number of
49 alternative plans to answer a query grows exponentially with the
50 number of joins included in it. Further optimization effort is
51 caused by the support of a variety of <firstterm>join
52 methods</firstterm> (e.g., nested loop, hash join, merge join in
53 <productname>PostgreSQL</productname>) to process individual joins
54 and a diversity of <firstterm>indexes</firstterm> (e.g., R-tree,
55 B-tree, hash in <productname>PostgreSQL</productname>) as access
60 The current <productname>PostgreSQL</productname> optimizer
61 implementation performs a <firstterm>near-exhaustive
62 search</firstterm> over the space of alternative strategies. This
63 algorithm, first introduced in the <quote>System R</quote>
64 database, produces a near-optimal join order, but can take an
65 enormous amount of time and memory space when the number of joins
66 in the query grows large. This makes the ordinary
67 <productname>PostgreSQL</productname> query optimizer
68 inappropriate for database application domains that involve the
69 need for extensive queries, such as artificial intelligence.
73 The Institute of Automatic Control at the University of Mining and
74 Technology, in Freiberg, Germany, encountered the described problems as its
75 folks wanted to take the <productname>PostgreSQL</productname> DBMS as the backend for a decision
76 support knowledge based system for the maintenance of an electrical
77 power grid. The DBMS needed to handle large join queries for the
78 inference machine of the knowledge based system.
82 Performance difficulties in exploring the space of possible query
83 plans created the demand for a new optimization technique to be developed.
87 In the following we describe the implementation of a
88 <firstterm>Genetic Algorithm</firstterm> to solve the join
89 ordering problem in a manner that is efficient for queries
90 involving large numbers of joins.
94 <sect1 id="geqo-intro2">
95 <title>Genetic Algorithms</title>
98 The genetic algorithm (<acronym>GA</acronym>) is a heuristic optimization method which
100 determined, randomized search. The set of possible solutions for the
101 optimization problem is considered as a
102 <firstterm>population</firstterm> of <firstterm>individuals</firstterm>.
103 The degree of adaptation of an individual to its environment is specified
104 by its <firstterm>fitness</firstterm>.
108 The coordinates of an individual in the search space are represented
109 by <firstterm>chromosomes</firstterm>, in essence a set of character
110 strings. A <firstterm>gene</firstterm> is a
111 subsection of a chromosome which encodes the value of a single parameter
112 being optimized. Typical encodings for a gene could be <firstterm>binary</firstterm> or
113 <firstterm>integer</firstterm>.
117 Through simulation of the evolutionary operations <firstterm>recombination</firstterm>,
118 <firstterm>mutation</firstterm>, and
119 <firstterm>selection</firstterm> new generations of search points are found
120 that show a higher average fitness than their ancestors.
124 According to the <systemitem class="resource">comp.ai.genetic</> <acronym>FAQ</acronym> it cannot be stressed too
125 strongly that a <acronym>GA</acronym> is not a pure random search for a solution to a
126 problem. A <acronym>GA</acronym> uses stochastic processes, but the result is distinctly
127 non-random (better than random).
130 <figure id="geqo-diagram">
131 <title>Structured Diagram of a Genetic Algorithm</title>
133 <informaltable frame="none">
138 <entry>generation of ancestors at a time t</entry>
142 <entry>P''(t)</entry>
143 <entry>generation of descendants at a time t</entry>
149 <literallayout class="monospaced">
150 +=========================================+
151 |>>>>>>>>>>> Algorithm GA <<<<<<<<<<<<<<|
152 +=========================================+
153 | INITIALIZE t := 0 |
154 +=========================================+
156 +=========================================+
157 | evaluate FITNESS of P(t) |
158 +=========================================+
159 | while not STOPPING CRITERION do |
160 | +-------------------------------------+
161 | | P'(t) := RECOMBINATION{P(t)} |
162 | +-------------------------------------+
163 | | P''(t) := MUTATION{P'(t)} |
164 | +-------------------------------------+
165 | | P(t+1) := SELECTION{P''(t) + P(t)} |
166 | +-------------------------------------+
167 | | evaluate FITNESS of P''(t) |
168 | +-------------------------------------+
170 +===+=====================================+
175 <sect1 id="geqo-pg-intro">
176 <title>Genetic Query Optimization (<acronym>GEQO</acronym>) in PostgreSQL</title>
179 The <acronym>GEQO</acronym> module is intended for the solution of the query
180 optimization problem similar to a traveling salesman problem (<acronym>TSP</acronym>).
181 Possible query plans are encoded as integer strings. Each string
182 represents the join order from one relation of the query to the next.
183 E. g., the query tree
184 <literallayout class="monospaced">
190 is encoded by the integer string '4-1-3-2',
191 which means, first join relation '4' and '1', then '3', and
192 then '2', where 1, 2, 3, 4 are relation IDs within the
193 <productname>PostgreSQL</productname> optimizer.
197 Parts of the <acronym>GEQO</acronym> module are adapted from D. Whitley's Genitor
202 Specific characteristics of the <acronym>GEQO</acronym>
203 implementation in <productname>PostgreSQL</productname>
206 <itemizedlist spacing="compact" mark="bullet">
209 Usage of a <firstterm>steady state</firstterm> <acronym>GA</acronym> (replacement of the least fit
210 individuals in a population, not whole-generational replacement)
211 allows fast convergence towards improved query plans. This is
212 essential for query handling with reasonable time;
218 Usage of <firstterm>edge recombination crossover</firstterm>
219 which is especially suited to keep edge losses low for the
220 solution of the <acronym>TSP</acronym> by means of a
221 <acronym>GA</acronym>;
227 Mutation as genetic operator is deprecated so that no repair
228 mechanisms are needed to generate legal <acronym>TSP</acronym> tours.
235 The <acronym>GEQO</acronym> module allows
236 the <productname>PostgreSQL</productname> query optimizer to
237 support large join queries effectively through
238 non-exhaustive search.
241 <sect2 id="geqo-future">
242 <title>Future Implementation Tasks for
243 <productname>PostgreSQL</> <acronym>GEQO</acronym></title>
246 Work is still needed to improve the genetic algorithm parameter
248 In file <filename>backend/optimizer/geqo/geqo_params.c</filename>, routines
249 <function>gimme_pool_size</function> and <function>gimme_number_generations</function>,
250 we have to find a compromise for the parameter settings
251 to satisfy two competing demands:
252 <itemizedlist spacing="compact">
255 Optimality of the query plan
269 <sect1 id="geqo-biblio">
270 <title>Further Readings</title>
273 The following resources contain additional information about
279 <ulink url="http://surf.de.uu.net/encore/www/">The Hitch-Hiker's
280 Guide to Evolutionary Computation</ulink> (FAQ for <ulink
281 url="news://comp.ai.genetic">comp.ai.genetic</ulink>)
287 <ulink url="http://www.red3d.com/cwr/evolve.html">Evolutionary
288 Computation and its application to art and design</ulink> by
295 <xref linkend="ELMA99">
301 <xref linkend="FONG">
310 <!-- Keep this comment at the end of the file
315 sgml-minimize-attributes:nil
316 sgml-always-quote-attributes:t
319 sgml-parent-document:nil
320 sgml-default-dtd-file:"./reference.ced"
321 sgml-exposed-tags:nil
322 sgml-local-catalogs:("/usr/lib/sgml/catalog")
323 sgml-local-ecat-files:nil