[GENERAL] GEQO and KSQO problem.

i tried your query.
This is query plan for select with union clause
NOTICE: QUERY PLAN:

Unique (cost=1573.19 size=0 width=0)
-> Sort (cost=1573.19 size=0 width=0)
-> Append (cost=1573.19 size=0 width=0)
-> Hash Join (cost=314.64 size=69 width=292)
-> Nested Loop (cost=310.65 size=22 width=268)
-> Seq Scan on clients (cost=126.07 size=87 width=256)
-> Index Scan using idxsclientidid1 on sotrud (cost=2.12 size=369 width=12)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on prinadleg (cost=1.13 size=4 width=24)
-> Unique (cost=314.64 size=0 width=0)
-> Sort (cost=314.64 size=0 width=0)
-> Hash Join (cost=314.64 size=69 width=292)
-> Nested Loop (cost=310.65 size=22 width=268)
-> Seq Scan on clients (cost=126.07 size=87 width=256)
-> Index Scan using idxsclientidid1 on sotrud (cost=2.12 size=369 width=12)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on prinadleg (cost=1.13 size=4 width=24)
-> Unique (cost=314.64 size=0 width=0)
-> Sort (cost=314.64 size=0 width=0)
-> Hash Join (cost=314.64 size=69 width=292)
-> Nested Loop (cost=310.65 size=22 width=268)
-> Seq Scan on clients (cost=126.07 size=87 width=256)
-> Index Scan using idxsclientidid1 on sotrud (cost=2.12 size=369 width=12)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on prinadleg (cost=1.13 size=4 width=24)
-> Unique (cost=314.64 size=0 width=0)
-> Sort (cost=314.64 size=0 width=0)
-> Hash Join (cost=314.64 size=69 width=292)
-> Nested Loop (cost=310.65 size=22 width=268)
-> Seq Scan on clients (cost=126.07 size=87 width=256)
-> Index Scan using idxsclientidid1 on sotrud (cost=2.12 size=369 width=12)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on prinadleg (cost=1.13 size=4 width=24)
-> Unique (cost=314.64 size=0 width=0)
-> Sort (cost=314.64 size=0 width=0)
-> Hash Join (cost=314.64 size=69 width=292)
-> Nested Loop (cost=310.65 size=22 width=268)
-> Seq Scan on clients (cost=126.07 size=87 width=256)
-> Index Scan using idxsclientidid1 on sotrud (cost=2.12 size=369 width=12)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on prinadleg (cost=1.13 size=4 width=24)

EXPLAIN

I think that it is too slow.
May be optimizer is more intellect in Postgres v.6.5.1?
I use Postgres v.6.4.2.

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(at)hotmail(dot)com%'
UNION
SELECT .....
WHERE
....
AND lower(SOTRUD.EMAIL) LIKE '%knirti(at)kaluga(dot)ru%'
..

etc.

Also try UNION ALL.

Herouth

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