Re: [PERFORM] Similar querys, better execution time on worst execution plan

[SZUCS Gábor]

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)



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Similar querys, better execution time on worst execution plan

[Fernando Papa]

 -Mensaje original-
 De: SZUCS Gábor [mailto:[EMAIL PROTECTED] 
 Enviado el: jueves, 26 de junio de 2003 7:31
 Para: [EMAIL PROTECTED]
 Asunto: Re: [PERFORM] Similar querys, better execution time 
 on worst execution plan
 
 
 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).

Ok, yes, I did only explay because I run several times the query and get avg. run 
time. but it's true, it's better to do EXP ANA.
 
 2. I think calling upper() for each row costs more than 
 direct comparison, but not sure

It's the only answer than I can found... maybe do a lot of uppers and then compare 
will be too much than compare with 2 conditions...
 
 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.

Yes, we have index on id_instalacion, but now we have only one instalation, so the 
content of these field, in the 99% of the rows, it's 2. I think in this case it's ok 
to choose seq scan.
 
 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');
 

I need to recheck about the quality of active field. Really I don't know if I 
found a lot of 'S', a lot of 'N', maybe we will have 50%/50% of 'S' or 'N'. This will 
be important to define index.

Thanks for your answer.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match