> > You chose 11 by comparing GEQO with non-GEQO. I think you will find
> > that with your improved GEQO, GEQO is faster for smaller number of
> > joins, preventing the memory problem. Can you check the speeds again?
>
> Bruce, I have rerun a couple of tests and am getting numbers like these:
>
> # tables joined
>
> ... 10 11 ...
>
> STD OPTIMIZER 24 115
> GEQO 45 55
>
> This is after tweaking the GEQO parameters to improve speed slightly
> in the default case. (Setting EFFORT=LOW reduces the 11-way plan time
> to about 40 sec, setting EFFORT=HIGH makes it about 70.)
>
> The breakpoint for speed is still clearly at GEQO threshold 11.
> *However*, the regular optimizer uses close to 120MB of memory to
> plan these 11-way joins, and that's excessive (especially since that's
> not even counting the space that will be used for execution...).
> Until we can do something about reclaiming space more effectively,
> I recommend reducing the default GEQO threshold to 10.
Agreed.
-- 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,
Pennsylvania19026