: [GENERAL] GEQO and KSQO problem.

Hi!

Thanks for help.

I can put the part of query outside the parentheses.
There two results from explain ( after vacuum).

Result from my query in original form:

NOTICE: QUERY PLAN

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)

Result from your query:
NOTICE: QUERY PLAN:

Unique (cost=129.33 size=0 width=0)
-> Sort (cost=129.33 size=0 width=0)
-> Nested Loop (cost=129.33 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.12 size=1925 width=24)

EXPLAIN

Unfortunaly, differents is too small.

But if i increased number of "OR" in query, the server was down and worked very,very slowly. I can't see any rezult from optimizer.
It's very strange thing!

Natalya
mak(at)rtsoft(dot)msk(dot)ru

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

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(at)hotmail(dot)com%'
or lower(SOTRUD.EMAIL) LIKE '%matukin(at)hotmail(dot)com%'
or lower(SOTRUD.EMAIL) LIKE '%knirti(at)kaluga(dot)ru%'
or lower(SOTRUD.EMAIL) LIKE '%avk(at)vniicom(dot)vsu(dot)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(at)hotmail\\(dot)com|matukin(at)hotmail\\(dot)com|knirti(at)kaluga\\(dot)ru|avk(at)vniicom\\(dot)vsu\\(dot)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

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