Re: [HACKERS] GEQO optimizer (was Re: Backend message type 0x44 arrived while idle)

> > 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026