Re: [HACKERS] GEQO optimizer (was Re: Backend message type 0x44 arrived while idle) - Mailing list pgsql-hackers

Oops,

it seems that was my fault, I didn't specified @nitems (sizes of tables)
for all tables. Now it works fine.
Tom, in case of my fault, why did postgres die ?
Regards,
    Oleg



On Mon, 17 May 1999, Oleg Bartunov wrote:

> Date: Mon, 17 May 1999 18:08:45 +0400 (MSD)
> From: Oleg Bartunov <oleg@sai.msu.su>
> To: Tom Lane <tgl@sss.pgh.pa.us>
> Cc: Oleg Bartunov <oleg@sai.msu.su>, pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] GEQO optimizer (was Re: Backend message type 0x44 arrived while idle) 
> 
> Tom,
> 
> I was so happy with the problem solved so I decided to play with more joins :-)
> Really, queries were processed very quickly but at 14 tables backend died :
> 
> COPY t13 FROM STDIN USING DELIMITERS '|';
> vacuum analyze;
> VACUUM
>  
> 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
ast10,t11.a as t11,t12.a as t12,t13.a as t13
 
>  from t0  ,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13
>  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_idand 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_idand t11.a_id=t0.
 
a_
> t11_id and t12.a_id=t0.a_t12_id and t13.a_id=t0.a_t13_id ;
> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated abnormally
>         before or while processing the request.
> We have lost the connection to the backend, so further processing is impossible.  Terminating.
> 
> 
> Tom, could you try my script at your machine ?
> I attached the script. You need perl to run it.
> 
> mkjoindata.pl | psql test
> 
>     Regards,
> 
>         Oleg
> 
> 
> On Mon, 17 May 1999, Tom Lane wrote:
> 
> > Date: Mon, 17 May 1999 09:44:18 -0400
> > From: Tom Lane <tgl@sss.pgh.pa.us>
> > To: Oleg Bartunov <oleg@sai.msu.su>
> > Cc: pgsql-hackers@postgreSQL.org
> > Subject: Re: [HACKERS] GEQO optimizer (was Re: Backend message type 0x44 arrived while idle) 
> > 
> > Oleg Bartunov <oleg@sai.msu.su> writes:
> > > 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. 
> > 
> > 18 minutes???  It takes barely over a minute on my aging 75MHz HP-PA
> > box.  (Practically all of which is planning time, since there are only
> > 10 tuples to join... or are you doing this on a realistically sized
> > set of tables now?)
> > 
> > > 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.aas 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_idand 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;
 
> > 
> > No, but since there are 11 tables mentioned, it will be sent to the GEQO
> > optimizer anyway with the default GEQO threshold of 11...
> > 
> >             regards, tom lane
> > 
> 
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
> 

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