Обсуждение: ïÔ×ÅÔ: [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
fromoptimizer.  
It's very strange thing!

                    Natalya
                    mak@rtsoft.msk.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@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\\.vsu\\.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



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

At 17:31 +0300 on 06/09/1999, Natalya S. Makushina wrote:


>
> I can put the part of query outside the parentheses.
...
> 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!

The postgres optimizer was never very good with OR clauses. That's why I
suggested the format with the regular expressions in the end of my message.
I wonder if you can get a better result by using a union:

SELECT .....
WHERE
  ....
  AND lower(SOTRUD.EMAIL) LIKE '%matukin@hotmail.com%'
UNION
SELECT .....
WHERE
  ....
  AND lower(SOTRUD.EMAIL) LIKE '%knirti@kaluga.ru%'
...

etc.

Also try UNION ALL.

Herouth

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