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@hotmail.com%')
> >   OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE
lower('%matukin@hotmail.com%') 
> >   OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE
lower('%knirti@kaluga.ru%') 
> >   OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE
lower('%avk@vniicom.vsu.ru%')
> >   OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE
lower('%avk@vniicom.vsu.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@rtsoft.msk.ru
> >
> >
> >
> >
> >
> >
> > ************
> >
>
> Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org
>
>
>
> ************
>

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