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@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