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

Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> writes:
>> I have observed that the regular optimizer requires about 50MB to plan
>> some ten-way joins, and can exceed my system's 128MB process data limit
>> on some eleven-way joins. We currently have the GEQO threshold set at
>> 11, which prevents the latter case by default --- but 50MB is a lot.
>> I wonder whether we shouldn't back the GEQO threshold off to 10.
>> (When I suggested setting it to 11, I was only looking at speed relative
>> to GEQO, not memory usage. There is now a *big* difference in memory
>> usage...) Comments?

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

regards, tom lane