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

On Sun, 16 May 1999, Bruce Momjian wrote:

> Date: Sun, 16 May 1999 21:17:30 -0400 (EDT)
> From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
> To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Cc: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>, pgsql-hackers(at)postgreSQL(dot)org
> Subject: Re: [HACKERS] GEQO optimizer (was Re: Backend message type 0x44 arrived while idle)
>
> > 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?
>

I confirm big join with 11 tables doesn't eats all memory+swap on
my Linux box as before and it runs *forever* :-). It took already
18 minutes of CPU (P200, 64Mb) ! Will wait.

8438 postgres 12 0 11104 3736 2620 R 0 98.6 5.9 18:16 postmaster

This query doesn't use (expicitly) GEQO

select t0.a,t1.a as t1,t2.a as t2,t3.a as t3,t4.a as t4,t5.a as t5,t6.a as t6,t7.a as t7,t8.a as t8,t9.a as t9,t10.a as t10
from t0 ,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10
where t1.a_id = t0.a_t1_id and t2.a_id=t0.a_t2_id and t3.a_id=t0.a_t3_id and t4.a_id=t0.a_t4_id and t5.a_id=t0.a_t5_id and t6.a_id=t0.a_t6_id and t7.a_id=t0.a_t7_id and t8.a_id=t0.a_t8_id and t9.a_id=t0.a_t9_id and t10.a_id=t0.a_t10_id ;

Regards,

Oleg

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

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83