Optimizing query - Mailing list pgsql-general

    Hi! I'm having trouble in a simple query who is not being optimized
the way it should (apparently). The query is as follows:

SELECT DISTINCT e.e_tipo from  requisicaoanalise ra, exame e where
ra.ra_reqnum='20010901798' and e.e_req=ra.ra_id;

    table requisicaoanalise has +- 10000 rows, and exame hash +- 100000;

    This query takes a lot of time (+- 5 secs on my system: psql
(PostgreSQL) 7.1RC3). I have indexes in ra_reqnum as in e_req and ra_id.
What I think is happening here is that postgres 1st evaluates
"e.e_req=ra.ra_id" ans only then "ra.ra_reqnum='20010901798", which is
obviously more time-consuming. If I rewrite the query as

SELECT DISTINCT e_tipo from exame e where e_req in ( select ra_id from
requisicaoanalise where ra_reqnum='20010901798');

    the result comes instantly.

    Is there any way to specify the precedence of the conditions in the
prior query? I don't have much experienece with the EXPLAIN, but here it
goes:


    explain SELECT DISTINCT e.e_tipo from  requisicaoanalise ra, exame e
where ra.ra_reqnum='20010901798' and e.e_req=ra.ra_id;
NOTICE:  QUERY PLAN:

Unique  (cost=22662.94..22867.50 rows=8182 width=12)
  ->  Sort  (cost=22662.94..22662.94 rows=81821 width=12)
        ->  Merge Join  (cost=13756.59..15219.60 rows=81821 width=12)
              ->  Sort  (cost=64.94..64.94 rows=70 width=4)
                    ->  Index Scan using requisicaoanalise_reqnum_idx on
requisicaoanalise ra  (cost=0.00..62.79 rows=70 width=4)
              ->  Sort  (cost=13691.65..13691.65 rows=116971 width=8)
                    ->  Seq Scan on exame e  (cost=0.00..2835.71 rows=116971
width=8


    EXPLAIN SELECT DISTINCT e_tipo from exame e where e_req in ( select
ra_id from requisicaoanalise where ra_reqnum='20010901798');
NOTICE:  QUERY PLAN:

Unique  (cost=7358767.76..7359060.18 rows=11697 width=4)
  ->  Sort  (cost=7358767.76..7358767.76 rows=116971 width=4)
        ->  Seq Scan on exame e  (cost=0.00..7348057.26 rows=116971 width=4)
              SubPlan
                ->  Materialize  (cost=62.79..62.79 rows=70 width=4)
                      ->  Index Scan using requisicaoanalise_reqnum_idx on
requisicaoanalise  (cost=0.00..62.79 rows=70 width=4)



    Thanks!

--
Pedro Miguel G. Alves

THINK - Tecnologias de Informa��o
Av. Defensores de Chaves n� 15 4�D, 1000-109 Lisboa Portugal
Tel: +351 21 3590285   Fax: +351 21 3582729
HomePage: www.think.co.pt