Date: Mon, 24 Sep 2001 15:57:02 +0000
From: Pedro Alves <pmalves@think.co.pt>
To: PostGreSQL <pgsql-general@postgresql.org>
Subject: Optimizing query
Message-ID: <20010924155702.A3255@cosmos.inesc.pt>
Mime-Version: 1.0
Content-Type: text/plain; charset=pt_PT
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
User-Agent: Mutt/1.2.5i


	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