Re: [GENERAL] GEQO and KSQO problem.

Depending on sensitivity of the data, if you want to create a pg_dump of
your data and make it available to me, I can load it up in v6.5.1 and see
if the query fails there too...

On Thu, 2 Sep 1999, Natalya S. Makushina wrote:

> Hi!
> Version of PostgreSQL is 6.4.2.
> The rezults of explain are different.
> first one was normal rezult
>
> Unique (cost=129.36 size=0 width=0)
> -> Sort (cost=129.36 size=0 width=0)
> -> Nested Loop (cost=129.36 size=1 width=304)
> -> Nested Loop (cost=127.21 size=1 width=280)
> -> Seq Scan on clients (cost=126.07 size=1 width=256)
> -> Seq Scan on prinadleg (cost=1.13 size=4 width=24)
> -> Index Scan using idxsclientidid1 on sotrud (cost=2.15 size=1925 width=24)
>
> and second one was
> 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.
>
> In the postgres log file i saw
> FATAL 1: palloc memory memory exhausted
>
>
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> First off, what version of PostgreSQL?
>
> Second...what does 'explain' show for this query...
>
> On Thu, 2 Sep 1999, Natalya S. Makushina wrote:
>
> > Hello all!
> >
> > When i had posted the SQL query like this
> >
> > "select distinct CLIENTS.CLIENTID,PRINADLEG.PRIM,CLIENTS.NAME_1,CLIENTS.NAME_2,CLIENTS.STRANA,CLIENTS.REGION, CLIENTS.INDEKC,
> > CLIENTS.GOROD,CLIENTS.OBLAST,CLIENTS.ULICA_DOM,CLIENTS.A_YA,CLIENTS.FLG_ADR,CLIENTS.TYP_CLS, CLIENTS.SITE,CLIENTS.OTRASL,
> > CLIENTS.VID_D,CLIENTS.KATEGOR,CLIENTS.METKI,CLIENTS.MANAGER,CLIENTS.MANAGER_ID, CLIENTS.PRIM,CLIENTS.ARH,CLIENTS.NEW_F,
> > CLIENTS.WRITER,CLIENTS.FLG_MY
> > from CLIENTS ,PRINADLEG ,SOTRUD
> > where CLIENTS.CLIENTID=SOTRUD.CLIENTID and
> > ( CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (NOT CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%ruslanmr(at)hotmail(dot)com%')
> > OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%matukin(at)hotmail(dot)com%')
> > OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%knirti(at)kaluga(dot)ru%')
> > OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%avk(at)vniicom(dot)vsu(dot)ru%')
> > OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%avk(at)vniicom(dot)vsu(dot)ru%')
> > )
> > order by CLIENTS.NEW_F, CLIENTS.NAME_1"
> >
> > my server worked very, very slow. When i had tried shutdown it's borrowed 1 hour aproximately.
> >
> > In postgres log file i saw message:
> > FATAL 1: palloc memory memory exhausted
> >
> > I have found in the postgres mailing lists that it was a GEQO problem.
> > I tried to turn on the KSQO, but there was no any effect.
> > In documentaion there is a phrase like this
> > "Memory exhaustion may occur with more than 10 relation involved in a query."
> > But i have only 3 relation involved in query.
> >
> > What is a solution of the this problem?
> >
> > Thanks for help
> >
> > Natalya Makushina
> > mak(at)rtsoft(dot)msk(dot)ru
> >
> >
> >
> >
> >
> >
> > ************
> >
>
> Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org
>
>
>
> ************
>

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org