Thread: GEQO and KSQO problem.

GEQO and KSQO problem.

From

"Natalya S. Makushina"

Date:

02 September 1999, 07:46:29

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

Re: [GENERAL] GEQO and KSQO problem.

From

The Hermit Hacker

Date:

02 September 1999, 11:46:31

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

Re: [GENERAL] GEQO and KSQO problem.

From

Herouth Maoz

Date:

06 September 1999, 11:28:54

At 11:45 +0300 on 02/09/1999, Natalya S. Makushina wrote:

> 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"

I wonder if this is all necessary? Can't you take the part

  CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH)

Outside the parentheses and leave only the LIKE comparisons inside? Also,
there is no point in running "lower" on a string which is known in advance
to contain only lowercase letters, which is true for most literal strings
(If your application creates this, you can always do the conversion on the
client side before putting it into the query). It only leaks memory.

Thus, if you try to rewrite the WHERE clause as follows, do you get any
improvement?

where CLIENTS.CLIENTID=SOTRUD.CLIENTID
  and CLIENTS.PRINADL=PRINADLEG.PRINADL
  and CLIENTS.FLG_MY
  and not CLIENTS.ARH
  and ( lower(SOTRUD.EMAIL) LIKE '%ruslanmr@hotmail.com%'
     or lower(SOTRUD.EMAIL) LIKE '%matukin@hotmail.com%'
     or lower(SOTRUD.EMAIL) LIKE '%knirti@kaluga.ru%'
     or lower(SOTRUD.EMAIL) LIKE '%avk@vniicom.vsu.ru%' )
...

I think the optimizer would be most happy if you avoid the OR altogether by
using alternatives in a regular expression instead of like. This will also
allow you to use case insensitive comparison and give up the 'lower':

where CLIENTS.CLIENTID=SOTRUD.CLIENTID
  and CLIENTS.PRINADL=PRINADLEG.PRINADL
  and CLIENTS.FLG_MY
  and not CLIENTS.ARH
  and SORTUD.EMAIL ~*
'ruslanmr@hotmail\\.com|matukin@hotmail\\.com|knirti@kaluga\\.ru|avk@vniicom\\.v
su\\.ru';

Note that you have to put two slashes before each period in the string,
because a period is special in regular expressions.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

************

Re: [GENERAL] GEQO and KSQO problem.

From

Herouth Maoz

Date:

06 March 2000, 21:06:40

At 11:45 +0300 on 02/09/1999, Natalya S. Makushina wrote:

> 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"

I wonder if this is all necessary? Can't you take the part

  CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH)

Outside the parentheses and leave only the LIKE comparisons inside? Also,
there is no point in running "lower" on a string which is known in advance
to contain only lowercase letters, which is true for most literal strings
(If your application creates this, you can always do the conversion on the
client side before putting it into the query). It only leaks memory.

Thus, if you try to rewrite the WHERE clause as follows, do you get any
improvement?

where CLIENTS.CLIENTID=SOTRUD.CLIENTID
  and CLIENTS.PRINADL=PRINADLEG.PRINADL
  and CLIENTS.FLG_MY
  and not CLIENTS.ARH
  and ( lower(SOTRUD.EMAIL) LIKE '%ruslanmr@hotmail.com%'
     or lower(SOTRUD.EMAIL) LIKE '%matukin@hotmail.com%'
     or lower(SOTRUD.EMAIL) LIKE '%knirti@kaluga.ru%'
     or lower(SOTRUD.EMAIL) LIKE '%avk@vniicom.vsu.ru%' )
...

I think the optimizer would be most happy if you avoid the OR altogether by
using alternatives in a regular expression instead of like. This will also
allow you to use case insensitive comparison and give up the 'lower':

where CLIENTS.CLIENTID=SOTRUD.CLIENTID
  and CLIENTS.PRINADL=PRINADLEG.PRINADL
  and CLIENTS.FLG_MY
  and not CLIENTS.ARH
  and SORTUD.EMAIL ~*
'ruslanmr@hotmail\\.com|matukin@hotmail\\.com|knirti@kaluga\\.ru|avk@vniicom\\.v
su\\.ru';

Note that you have to put two slashes before each period in the string,
because a period is special in regular expressions.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

************