agora inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedFrom: Pedro Alves <pmalves@think.co.pt>
To: PostGreSQL <pgsql-general@postgresql.org>
Subject: Optimizing query
Date: Mon, 24 Sep 2001 15:57:02 +0000
Message-ID: <20010924155702.A3255@cosmos.inesc.pt> (raw)
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
view thread (41+ messages) latest in thread
Message-ID: <20010924155702.A3255@cosmos.inesc.pt>
Permalink: ../20010924155702.A3255@cosmos.inesc.pt/
Also on: postgresql.org/message-id/20010924155702.A3255@cosmos.inesc.pt
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: pmalves@think.co.pt
Subject: Re: Optimizing query
In-Reply-To: <20010924155702.A3255@cosmos.inesc.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox