Re: [GENERAL] GEQO and KSQO problem.
От | The Hermit Hacker |
---|---|
Тема | Re: [GENERAL] GEQO and KSQO problem. |
Дата | |
Msg-id | Pine.BSF.4.10.9909021612510.52139-100000@thelab.hub.org обсуждение исходный текст |
Ответ на | Re: [GENERAL] GEQO and KSQO problem. ("Natalya S. Makushina" <mak@rtsoft.msk.ru>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: