Обсуждение: [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@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

At 16:00 +0200 on 08/09/1999, Natalya S. Makushina wrote:


> i tried your query.
> This is query plan for select with union clause
> NOTICE:  QUERY PLAN:
>...
> 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.

Yes, it's definitely supposed to be better.

By the way, if there are not supposed to be overlaps in the queries (that
is, a query about one email will never return the same tuple that was
returned by a query about another email), you should try UNION ALL. I think
it should remove the unique phases.

By the way, did you try the suggestion with the regular expression? What
was the cost of that?

Herouth

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