Re: [GENERAL] GEQO and KSQO problem.

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