> i've noticed a pretty drastic slowdown going from a 4 table join
> (instantaneous) to a 5 table join (15-20 seconds) i don't think i've
> tried 6 tables yet with the same database. is there a lower limit to
> where using GEQO doesn't really make sense in terms of speed (i.e.,
> would setting GEQO to 5 be a toss up and 4 not make sense?) i'm
> guessing that the number of plans the optimizer checks without GEQO goes
> up factorially, while GEQO goes up fairly linearly (from my limited
> knowledge of genetic algorithms, basically you make a series of first
> guesses and gradually refine them and throw away the losers until you
> end up with the right one.) if my join is based purely on primary keys,
> shouldn't just about any plan work well, or at least well enough that it
> doesn't pay to make an exhaustive search of the plan space, making GEQO
> the best choice? i guess my question is "is there a rule of thumb for
> setting GEQO?" is there a reason it was set to 8 by default? does GEQO
> work better in some cases than others? (small tables, joins on
> non-indexed fields, etc.)
The bottom line is that GEQO in 6.4.* and previous releases counted the
number of tables as an indicator. The new 6.5, which will be going into
beta soon, will use the number of tables _and_ indexes, making the GEQO
start location much more reliable. The current default GEQO of 8 is too
high for some queries, and too low for others, because it did not take
into account the number of indexes involved. So, count the number of
indexes and tables, and try a proper GEQO setting for that sum. Let me
know what you find as a good GEQO number. This testing will require you
to enable/disable GEQO with SET GEQO TO 'ON=1' and SET GEQO TO 'ON=9999'
just before each query. What sum of tables and indexes seems to make
GEQO a win?
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026