From: SZUCS Gábor <surrano@mailbox.hu>
To: pgsql-performance@postgresql.org
Subject: Re: Similar querys, better execution time on worst execution plan
Date: Thu, 26 Jun 2003 12:30:59 +0200
Message-ID: <001c01c33bce$090aa460$0403a8c0@fejleszt4> (raw)
References: <F1DC5B511E2D1C499E5E20FC6D74160D036421D6@exch2000.buehuergo.corp.claxson.com>
Fernando,
1. Try EXPLAIN ANALYZE. Cost alone isn't an absolute measure. I think it's
only to see which parts of the query are expected to be slowest. However,
EXP ANA will give you exact times in msec (which effectively means it
executes the query).
2. I think calling upper() for each row costs more than direct comparison,
but not sure
3. Notice that there are seq scans with filter conditions like
"id_instalacion = 2::numeric"
Do you have indices on id_instalacion, which seems to be a numeric field?
if so, try casting the constant expressions in the query to numeric so that
postgresql may find the index. If you don't have such indices, it may be
worth to create them. (I guess you only have it on the table aliased with c,
since it does an index scan there.
4. another guess may be indices on (id_instalacion, activo), or, if activo
has few possible values (for example, it may be only one of three letters,
say, 'S', 'A' or 'K'), partial indices like:
CREATE INDEX cont_sbc_id_ins_S ON cont_sbc (id_instalacion)
WHERE activo in ('S', 's');
CREATE INDEX cont_sbc_id_ins_A ON cont_sbc (id_instalacion)
WHERE activo in ('A', 'a');
CREATE INDEX cont_sbc_id_ins_K ON cont_sbc (id_instalacion)
WHERE activo in ('K', 'k');
G.
------------------------------- cut here -------------------------------
WHERE c.id_instalacion = 2
AND s.id_instalacion = 2
AND p.id_instalacion = 2
...
-> Seq Scan on cont_sbc s (cost=0.00..4.44 rows=1 width=35)
Filter: ((id_instalacion = 2::numeric)
AND (upper((activo)::text) = 'S'::text))
-> Index Scan using cont_cont_cont_sbc_fk_i on cont_contenido c
(cost=0.00..15.56 rows=1 width=43)
Index Cond: ((c.id_instalacion = 2::numeric)
AND (c.id_sbc = "outer".id_sbc))
Filter: (upper((activo)::text) = 'S'::text)
-> Seq Scan on cont_publicacion p (cost=0.00..98.54 rows=442 width=55)
Filter: (id_instalacion = 2::numeric)
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: surrano@mailbox.hu
Subject: Re: Similar querys, better execution time on worst execution plan
In-Reply-To: <001c01c33bce$090aa460$0403a8c0@fejleszt4>
References: <F1DC5B511E2D1C499E5E20FC6D74160D036421D6@exch2000.buehuergo.corp.claxson.com> <001c01c33bce$090aa460$0403a8c0@fejleszt4>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox