from:"Fernando Papa"

Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec

2003-08-04 Thread Fernando Papa


Sorry Chris... a little slower...

esdc= EXPLAIN ANALYZE 
SELECT
  cont_contenido.id_contenido
 ,cont_contenido.pertenece_premium
 ,cont_contenido.Titulo_esp as v_sufix 
 ,cont_contenido.url_contenido
 ,cont_contenido.tipo_acceso
 ,cont_contenido.id_sbc
 ,cont_contenido.cant_vistos
 ,cont_contenido.cant_votos 
 ,cont_contenido.puntaje_total 
 ,cont_contenido.id_contenido_padre 
 ,juegos_config.imagen_tapa_especial 
 ,juegos_config.info_general_esp as info_general 
 ,juegos_config.ayuda 
 ,juegos_config.tips_tricks_esp as tips_tricks 
 ,juegos_config.mod_imagen_tapa_especial 
 ,cont_publicacion.fecha_publicacion as fecha_publicacion 
 ,cont_publicacion.generar_Vainilla 
FROM 
 cont_contenido   
 ,juegos_config
 ,cont_publicacion 
WHERE 
 cont_contenido.id_instalacion= 2
 AND cont_contenido.id_sbc   = 619
 AND cont_contenido.id_tipo   = 2
 AND cont_contenido.id_instalacion  = juegos_config.id_instalacion 
 AND cont_contenido.id_contenido   = juegos_config.id_contenido   
 AND upper(cont_publicacion.generar_Vainilla) = 'S'
 AND cont_publicacion.id_instalacion = cont_contenido.id_instalacion 
 AND cont_publicacion.id_contenido = cont_contenido.id_contenido 
 AND cont_publicacion.fecha_publicacion = (SELECT cp1.fecha_publicacion
FROM cont_publicacion cp1
 WHERE cp1.id_instalacion = cont_publicacion.id_instalacion
 AND cp1.id_contenido = cont_publicacion.id_contenido
 AND cp1.generar_vainilla = cont_publicacion.generar_vainilla
 ORDER BY fecha_publicacion LIMIT 1)
ORDER BY  cont_publicacion.fecha_publicacion desc 
 LIMIT 10
 OFFSET 0
 ;

  QUERY PLAN   

---
 Limit  (cost=9.75..9.76 rows=1 width=479) (actual time=465085.25..465085.27 rows=8 
loops=1)
   -  Sort  (cost=9.75..9.76 rows=1 width=479) (actual time=465085.23..465085.24 
rows=8 loops=1)
 Sort Key: cont_publicacion.fecha_publicacion
 -  Merge Join  (cost=9.73..9.74 rows=1 width=479) (actual 
time=210743.83..465083.31 rows=8 loops=1)
   Merge Cond: ((outer.id_instalacion = inner.id_instalacion) AND 
(outer.id_contenido = inner.id_contenido))
   -  Nested Loop  (cost=0.00..284756.79 rows=1 width=367) (actual 
time=8319.87..464981.68 rows=40 loops=1)
 Join Filter: ((inner.id_contenido = outer.id_contenido) AND 
(inner.id_instalacion = outer.id_instalacion))
 -  Index Scan using jue_conf_pk on juegos_config  
(cost=0.00..12.19 rows=40 width=332) (actual time=52.93..142.31 rows=40 loops=1)
 -  Seq Scan on cont_publicacion  (cost=0.00..7118.60 rows=1 
width=35) (actual time=51.79..11617.12 rows=97 loops=40)
   Filter: ((upper((generar_vainilla)::text) = 'S'::text) AND 
(fecha_publicacion = (subplan)))
   SubPlan
 -  Limit  (cost=15.85..15.85 rows=1 width=8) (actual 
time=25.86..25.86 rows=1 loops=17880)
   -  Sort  (cost=15.85..15.86 rows=1 width=8) 
(actual time=25.82..25.82 rows=2 loops=17880)
 Sort Key: fecha_publicacion
 -  Seq Scan on cont_publicacion cp1  
(cost=0.00..15.84 rows=1 width=8) (actual time=10.68..25.32 rows=7 loops=17880)
   Filter: ((id_instalacion = $0) AND 
(id_contenido = $1) AND (generar_vainilla = $2))
   -  Sort  (cost=9.73..9.74 rows=3 width=112) (actual time=94.91..94.93 
rows=8 loops=1)
 Sort Key: cont_contenido.id_instalacion, 
cont_contenido.id_contenido
 -  Seq Scan on cont_contenido  (cost=0.00..9.70 rows=3 
width=112) (actual time=21.70..92.96 rows=8 loops=1)
   Filter: ((id_instalacion = 2::numeric) AND (id_sbc = 
619::numeric) AND (id_tipo = 2::numeric))
 Total runtime: 465088.66 msec
(21 rows)



-Mensaje original-
De: Christopher Browne [mailto:[EMAIL PROTECTED] 
Enviado el: viernes, 01 de agosto de 2003 18:27
Para: Fernando Papa
CC: [EMAIL PROTECTED]
Asunto: Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec



I'd point at the following as being a sterling candidate for being a cause of this 
being slow...

 AND cont_publicacion.fecha_publicacion = (SELECT max(cp1.fecha_publicacion) 
  FROM cont_publicacion cp1  
  WHERE cp1.id_instalacion = cont_publicacion.id_instalacion 
    AND cp1.id_contenido = cont_publicacion.id_contenido  
    AND cp1.generar_vainilla = cont_publicacion.generar_vainilla) 

May I suggest changing it to:

 AND cont_publicacion.fecha_publicacion = (SELECT cp1

Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec

2003-08-04 Thread Fernando Papa


Hi Josh... a little worse time:

EXPLAIN ANALYZE 
SELECT
 cont_contenido.id_contenido
 ,cont_contenido.pertenece_premium
 ,cont_contenido.Titulo_esp as v_sufix 
 ,cont_contenido.url_contenido
 ,cont_contenido.tipo_acceso
 ,cont_contenido.id_sbc
 ,cont_contenido.cant_vistos
 ,cont_contenido.cant_votos 
 ,cont_contenido.puntaje_total 
 ,cont_contenido.id_contenido_padre 
 ,juegos_config.imagen_tapa_especial 
 ,juegos_config.info_general_esp as info_general 
 ,juegos_config.ayuda 
 ,juegos_config.tips_tricks_esp as tips_tricks 
 ,juegos_config.mod_imagen_tapa_especial 
 ,cont_publicacion.fecha_publicacion as fecha_publicacion 
 ,cont_publicacion.generar_Vainilla 
 FROM 
 cont_contenido   
 ,juegos_config
,cont_publicacion 
 WHERE 
 cont_contenido.id_instalacion= 2
 AND cont_contenido.id_sbc   = 619
 AND cont_contenido.id_tipo   = 2
 AND cont_contenido.id_instalacion  = juegos_config.id_instalacion 
 AND cont_contenido.id_contenido   = juegos_config.id_contenido 
 AND upper(cont_publicacion.generar_Vainilla) = 'S'
 AND cont_publicacion.id_instalacion = cont_contenido.id_instalacion 
 AND cont_publicacion.id_contenido = cont_contenido.id_contenido 
 AND EXISTS (SELECT max(cp1.fecha_publicacion)
 FROM cont_publicacion cp1
WHERE cp1.id_instalacion = cont_publicacion.id_instalacion
AND cp1.id_contenido = cont_publicacion.id_contenido
AND cp1.generar_vainilla = cont_publicacion.generar_vainilla
   HAVING max(cp1.fecha_publicacion) =
cont_publicacion.fecha_publicacion)
ORDER BY  cont_publicacion.fecha_publicacion desc 
 LIMIT 10
OFFSET 0
;


 
QUERY PLAN



-
 Limit  (cost=9.75..9.76 rows=1 width=479) (actual
time=449760.88..449760.91 rows=8 loops=1)
   -  Sort  (cost=9.75..9.76 rows=1 width=479) (actual
time=449760.87..449760.88 rows=8 loops=1)
 Sort Key: cont_publicacion.fecha_publicacion
 -  Merge Join  (cost=9.73..9.74 rows=1 width=479) (actual
time=202257.20..449759.00 rows=8 loops=1)
   Merge Cond: ((outer.id_instalacion =
inner.id_instalacion) AND (outer.id_contenido =
inner.id_contenido))
   -  Nested Loop  (cost=0.00..284556.86 rows=1 width=367)
(actual time=7794.28..449741.85 rows=40 loops=1)
 Join Filter: ((inner.id_contenido =
outer.id_contenido) AND (inner.id_instalacion =
outer.id_instalacion))
 -  Index Scan using jue_conf_pk on juegos_config
(cost=0.00..12.19 rows=40 width=332) (actual time=0.43..8.12 rows=40
loops=1)
 -  Seq Scan on cont_publicacion
(cost=0.00..7113.60 rows=1 width=35) (actual time=24.10..11239.67
rows=97 loops=40)
   Filter: ((upper((generar_vainilla)::text) =
'S'::text) AND (subplan))
   SubPlan
 -  Aggregate  (cost=15.85..15.85 rows=1
width=8) (actual time=25.03..25.03 rows=0 loops=17880)
   Filter: (max(fecha_publicacion) = $3)
   -  Seq Scan on cont_publicacion cp1
(cost=0.00..15.84 rows=1 width=8) (actual time=10.51..24.85 rows=7
loops=17880)
 Filter: ((id_instalacion = $0)
AND (id_contenido = $1) AND (generar_vainilla = $2))
   -  Sort  (cost=9.73..9.74 rows=3 width=112) (actual
time=10.49..10.52 rows=8 loops=1)
 Sort Key: cont_contenido.id_instalacion,
cont_contenido.id_contenido
 -  Seq Scan on cont_contenido  (cost=0.00..9.70
rows=3 width=112) (actual time=0.59..8.07 rows=8 loops=1)
   Filter: ((id_instalacion = 2::numeric) AND
(id_sbc = 619::numeric) AND (id_tipo = 2::numeric))
 Total runtime: 449765.69 msec
(20 rows)



-Mensaje original-
De: Josh Berkus [mailto:[EMAIL PROTECTED] 
Enviado el: viernes, 01 de agosto de 2003 18:32
Para: Christopher Browne; Fernando Papa
CC: [EMAIL PROTECTED]
Asunto: Re: [PERFORM] I can't wait too much: Total runtime 432478.44
msec


Fernando,

  AND cont_publicacion.fecha_publicacion = (SELECT
max(cp1.fecha_publicacion) 
   FROM cont_publicacion cp1  
   WHERE cp1.id_instalacion =
cont_publicacion.id_instalacion 
 AND cp1.id_contenido =
cont_publicacion.id_contenido  
 AND cp1.generar_vainilla =
cont_publicacion.generar_vainilla) 

Or event changing it to:

AND EXISTS (SELECT max(cp1.fecha_publicacion)
FROM cont_publicacion cp1
WHERE cp1.id_instalacion = cont_publicacion.id_instalacion 
AND cp1.id_contenido = cont_publicacion.id_contenido  
AND cp1.generar_vainilla = cont_publicacion.generar_vainilla
HAVING max(cp1.fecha_publicacion) =
cont_publicacion.fecha_publicacion)
-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec

2003-08-04 Thread Fernando Papa


I create the index, but doesn't help too much:

   QUERY 
PLAN
-
 Limit  (cost=9.75..9.76 rows=1 width=479) (actual time=486421.35..486421.38 rows=8 
loops=1)
   -  Sort  (cost=9.75..9.76 rows=1 width=479) (actual time=486421.33..486421.34 
rows=8 loops=1)
 Sort Key: cont_publicacion.fecha_publicacion
 -  Merge Join  (cost=9.73..9.74 rows=1 width=479) (actual 
time=220253.76..486420.35 rows=8 loops=1)
   Merge Cond: ((outer.id_instalacion = inner.id_instalacion) AND 
(outer.id_contenido = inner.id_contenido))
   -  Nested Loop  (cost=0.00..1828.35 rows=1 width=367) (actual 
time=8347.78..486405.02 rows=40 loops=1)
 Join Filter: ((inner.id_contenido = outer.id_contenido) AND 
(inner.id_instalacion = outer.id_instalacion))
 -  Index Scan using jue_conf_pk on juegos_config  
(cost=0.00..12.19 rows=40 width=332) (actual time=0.23..6.73 rows=40 loops=1)
 -  Index Scan using idx_generar_vainilla_ci on cont_publicacion  
(cost=0.00..45.39 rows=1 width=35) (actual time=56.01..12156.48 rows=97 loops=40)
   Index Cond: (upper((generar_vainilla)::text) = 'S'::text)
   Filter: (fecha_publicacion = (subplan))
   SubPlan
 -  Aggregate  (cost=15.84..15.84 rows=1 width=8) (actual 
time=27.03..27.03 rows=1 loops=17880)
   -  Seq Scan on cont_publicacion cp1  
(cost=0.00..15.84 rows=1 width=8) (actual time=11.21..26.86 rows=7 loops=17880)
 Filter: ((id_instalacion = $0) AND 
(id_contenido = $1) AND (generar_vainilla = $2))
   -  Sort  (cost=9.73..9.74 rows=3 width=112) (actual time=9.28..9.32 
rows=8 loops=1)
 Sort Key: cont_contenido.id_instalacion, 
cont_contenido.id_contenido
 -  Seq Scan on cont_contenido  (cost=0.00..9.70 rows=3 
width=112) (actual time=0.47..7.48 rows=8 loops=1)
   Filter: ((id_instalacion = 2::numeric) AND (id_sbc = 
619::numeric) AND (id_tipo = 2::numeric))
 Total runtime: 486445.19 msec
(20 rows)


-Mensaje original-
De: Mendola Gaetano [mailto:[EMAIL PROTECTED] 
Enviado el: sábado, 02 de agosto de 2003 7:36
Para: [EMAIL PROTECTED]
CC: Fernando Papa
Asunto: Re: I can't wait too much: Total runtime 432478.44 msec


From: Fernando Papa [EMAIL PROTECTED]

 AND upper(cont_publicacion.generar_Vainilla) = 'S'


  Filter: ((upper((generar_vainilla)::text) = 'S'::text) AND
(fecha_publicacion = (subplan)))

using a functional index on this field should help

create index idx_generar_vainilla_ci on cont_publicacion (
upper(generar_Vainilla) )



Regards
Gaetano Mendola



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec

2003-08-04 Thread Fernando Papa

Title: Mensaje



Hi 
Volker!!! I think you're right. Look at times:
 
QUERY 
PLAN 
---Limit 
(cost=23.37..23.37 rows=1 width=487) (actual time=2245.61..2245.61 rows=0 
loops=1) - Sort (cost=23.37..23.37 rows=1 
width=487) (actual time=2245.60..2245.60 rows=0 
loops=1) Sort Key: 
cont_publicacion.fecha_publicacion 
- Nested Loop (cost=23.33..23.36 rows=1 width=487) (actual 
time=2244.10..2244.10 rows=0 
loops=1) 
Join Filter: ("outer".fecha_publicacion = 
"inner".max_pub) 
- Merge Join (cost=9.73..9.74 rows=1 width=479) (actual 
time=918.73..1988.43 rows=16 
loops=1) 
Merge Cond: (("outer".id_instalacion = "inner".id_instalacion) AND 
("outer".id_contenido = 
"inner".id_contenido)) 
- Nested Loop (cost=0.00..409.35 rows=1 width=367) (actual 
time=35.44..1967.20 rows=82 
loops=1) 
Join Filter: (("inner".id_contenido = "outer".id_contenido) AND 
("inner".id_instalacion = 
"outer".id_instalacion)) 
- Index Scan using jue_conf_pk on juegos_config 
(cost=0.00..12.19 rows=40 width=332) (actual time=0.42..6.73 rows=40 
loops=1) 
- Index Scan using idx_generar_vainilla_ci on cont_publicacion 
(cost=0.00..9.90 rows=2 width=35) (actual time=0.20..35.19 rows=447 
loops=40) 
Index Cond: (upper((generar_vainilla)::text) = 
'S'::text) 
- Sort (cost=9.73..9.74 rows=3 width=112) (actual 
time=10.42..10.48 rows=15 
loops=1) 
Sort Key: cont_contenido.id_instalacion, 
cont_contenido.id_contenido 
- Seq Scan on cont_contenido (cost=0.00..9.70 rows=3 width=112) 
(actual time=0.57..8.11 rows=8 
loops=1) 
Filter: ((id_instalacion = 2::numeric) AND (id_sbc = 619::numeric) AND (id_tipo 
= 
2::numeric)) 
- Subquery Scan a (cost=13.60..13.60 rows=1 width=8) (actual 
time=15.89..15.90 rows=1 
loops=16) 
- Aggregate (cost=13.60..13.60 rows=1 width=8) (actual 
time=15.87..15.88 rows=1 
loops=16) 
- Seq Scan on cont_publicacion cp1 (cost=0.00..12.48 rows=448 
width=8) (actual time=0.05..11.62 rows=448 loops=16)Total runtime: 
2250.92 msec(20 rows)
The 
problem was the subquery, no doubt.


  
  -Mensaje original-De: Volker Helm 
  [mailto:[EMAIL PROTECTED] Enviado el: lunes, 04 de agosto de 2003 
  11:45Para: Fernando PapaAsunto: AW: [PERFORM] I can't 
  wait too much: Total runtime 432478.44 msec
  Hi,
  
  just use the subquery as inline-View an join the 
  tables:
  
  SELECT 
  cont_contenido.id_contenido,cont_contenido.pertenece_premium,cont_contenido.Titulo_esp 
  as v_sufix 
  ,cont_contenido.url_contenido,cont_contenido.tipo_acceso,cont_contenido.id_sbc,cont_contenido.cant_vistos,cont_contenido.cant_votos 
  ,cont_contenido.puntaje_total 
  ,cont_contenido.id_contenido_padre 
  ,juegos_config.imagen_tapa_especial 
  ,juegos_config.info_general_esp as info_general 
  ,juegos_config.ayuda ,juegos_config.tips_tricks_esp as 
  tips_tricks ,juegos_config.mod_imagen_tapa_especial 
  ,cont_publicacion.fecha_publicacion as fecha_publicacion 
  ,cont_publicacion.generar_Vainilla FROM 
  cont_contenido 
  ,juegos_config ,cont_publicacion 
  
  ,(SELECT max(cp1.fecha_publicacion) as 
  max_pub 
  --change 
  here 
  FROM cont_publicacion cp1) a--change 
  hereWHERE 
  cont_contenido.id_instalacion 
  = 2AND 
  cont_contenido.id_sbc 
  = 619AND 
  cont_contenido.id_tipo 
  = 2AND cont_contenido.id_instalacion = 
  juegos_config.id_instalacion AND 
  cont_contenido.id_contenido = 
  juegos_config.id_contenido 
  AND upper(cont_publicacion.generar_Vainilla) = 'S'AND 
  cont_publicacion.id_instalacion = cont_contenido.id_instalacion AND 
  cont_publicacion.id_contenido = cont_contenido.id_contenido AND 
  cont_publicacion.fecha_publicacion 
  =a.max_pub 
  -- change hereORDER BY 
  cont_publicacion.fecha_publicacion desc
  
  hope it helps,
  
  Volker Helm
  
-Ursprüngliche Nachricht-Von: 
[EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]Im Auftrag von 
Fernando PapaGesendet: Freitag, 1. August 2003 
23:17An: [EMAIL PROTECTED]Betreff: 
[PERFORM] I can't wait too much: Total runtime 432478.44 
msec

Hi 
all!
Really I don't 
know what happened with this query. I'm running PG 7.3.1 on solaris, 
vaccumed (full) every nigth.
The cardinality 
of each table was:

cont_contenido: 97 rows
juegos_config: 40 rows
cont_publicacion: 446 rows
not huge 
tables...

however, this 
query took a lot of time to run:Total runtime: 432478.44 
msecI made a explain analyze, but really I don't undertand 
why...

esdc= explain analyzeSELECT 
cont_contenido.id_contenido,cont_contenido.pertenece_premium,cont_contenido.Titulo_esp 
as v_sufix 
,cont_contenido.url_contenido,cont_contenido.tipo_acceso,cont_contenido.id_sbc,cont_contenido.cant_vistos,cont_contenido.cant_votos 
,cont_contenido.puntaje_total 
,cont_contenido.id_contenido_padre

Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec

2003-08-04 Thread Fernando Papa

Err... you're right... one of us say the same thing when I show the
Volker mail...

-Mensaje original-
De: Manfred Koizar [mailto:[EMAIL PROTECTED] 
Enviado el: lunes, 04 de agosto de 2003 12:17
Para: Fernando Papa
CC: Volker Helm; [EMAIL PROTECTED]
Asunto: Re: [PERFORM] I can't wait too much: Total runtime 432478.44
msec


On Mon, 4 Aug 2003 12:02:46 -0300, Fernando Papa [EMAIL PROTECTED]
wrote:
FROM 
cont_contenido   
,juegos_config
,cont_publicacion 
,(SELECT max(cp1.fecha_publicacion) as max_pub
--change here
 FROM cont_publicacion cp1) a --change here

But this calculates the global maximum, not per id_instalacion,
id_contenido, and generar_vainilla as in

AND cont_publicacion.fecha_publicacion = (SELECT
max(cp1.fecha_publicacion) 
 FROM cont_publicacion cp1  
 WHERE cp1.id_instalacion =
cont_publicacion.id_instalacion 
   AND cp1.id_contenido =
cont_publicacion.id_contenido  
   AND cp1.generar_vainilla =
cont_publicacion.generar_vainilla) 

Servus
 Manfred

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

[PERFORM] I can't wait too much: Total runtime 432478.44 msec

2003-08-01 Thread Fernando Papa

Title: Mensaje




Hi 
all!
Really I don't know 
what happened with this query. I'm running PG 7.3.1 on solaris, vaccumed (full) 
every nigth.
The cardinality of 
each table was:

cont_contenido: 97 rows
juegos_config: 40 rows
cont_publicacion: 446 rows
not huge 
tables...

however, this query 
took a lot of time to run:Total runtime: 432478.44 msecI made a 
explain analyze, but really I don't undertand 
why...

esdc= explain analyzeSELECT 
cont_contenido.id_contenido,cont_contenido.pertenece_premium,cont_contenido.Titulo_esp 
as v_sufix 
,cont_contenido.url_contenido,cont_contenido.tipo_acceso,cont_contenido.id_sbc,cont_contenido.cant_vistos,cont_contenido.cant_votos 
,cont_contenido.puntaje_total 
,cont_contenido.id_contenido_padre 
,juegos_config.imagen_tapa_especial 
,juegos_config.info_general_esp as info_general 
,juegos_config.ayuda ,juegos_config.tips_tricks_esp as 
tips_tricks ,juegos_config.mod_imagen_tapa_especial 
,cont_publicacion.fecha_publicacion as fecha_publicacion 
,cont_publicacion.generar_Vainilla FROM 
cont_contenido ,juegos_config 
,cont_publicacion WHERE 
cont_contenido.id_instalacion 
= 2AND 
cont_contenido.id_sbc 
= 619AND 
cont_contenido.id_tipo 
= 2AND cont_contenido.id_instalacion = 
juegos_config.id_instalacion AND 
cont_contenido.id_contenido = 
juegos_config.id_contenido 
AND upper(cont_publicacion.generar_Vainilla) = 'S'AND 
cont_publicacion.id_instalacion = cont_contenido.id_instalacion AND 
cont_publicacion.id_contenido = cont_contenido.id_contenido AND 
cont_publicacion.fecha_publicacion = (SELECT max(cp1.fecha_publicacion) 
 
FROM cont_publicacion cp1 
 
WHERE cp1.id_instalacion = cont_publicacion.id_instalacion 
 
AND cp1.id_contenido = cont_publicacion.id_contenido 
 
AND cp1.generar_vainilla = 
cont_publicacion.generar_vainilla) ORDER 
BY cont_publicacion.fecha_publicacion desc LIMIT 
10OFFSET 
0esdc-; 
QUERY 
PLAN 
-Limit 
(cost=8.72..8.73 rows=1 width=478) (actual time=432473.69..432473.72 rows=8 
loops=1) - Sort (cost=8.72..8.73 rows=1 
width=478) (actual time=432473.67..432473.68 rows=8 
loops=1) Sort Key: 
cont_publicacion.fecha_publicacion 
- Merge Join (cost=8.69..8.71 rows=1 width=478) (actual 
time=197393.80..432471.92 rows=8 
loops=1) 
Merge Cond: (("outer".id_instalacion = "inner".id_instalacion) AND 
("outer".id_contenido = 
"inner".id_contenido)) 
- Nested Loop (cost=0.00..281713.36 rows=1 width=367) (actual 
time=7524.66..432454.11 rows=40 
loops=1) 
Join Filter: (("inner".id_contenido = "outer".id_contenido) AND 
("inner".id_instalacion = 
"outer".id_instalacion)) 
- Index Scan using jue_conf_pk on juegos_config 
(cost=0.00..12.19 rows=40 width=332) (actual time=0.39..7.81 rows=40 
loops=1) 
- Seq Scan on cont_publicacion (cost=0.00..7042.51 rows=1 
width=35) (actual time=23.64..10807.83 rows=96 
loops=40) 
Filter: ((upper((generar_vainilla)::text) = 'S'::text) AND (fecha_publicacion = 
(subplan))) 
SubPlan 
- Aggregate (cost=15.79..15.79 rows=1 width=8) (actual 
time=24.16..24.16 rows=1 
loops=17800) 
- Seq Scan on cont_publicacion cp1 (cost=0.00..15.79 rows=1 
width=8) (actual time=10.14..24.01 rows=7 
loops=17800) 
Filter: ((id_instalacion = $0) AND (id_contenido = $1) AND (generar_vainilla = 
$2)) 
- Sort (cost=8.69..8.70 rows=3 width=111) (actual 
time=11.14..11.18 rows=8 
loops=1) 
Sort Key: cont_contenido.id_instalacion, 
cont_contenido.id_contenido 
- Seq Scan on cont_contenido (cost=0.00..8.66 rows=3 width=111) 
(actual time=0.57..8.62 rows=8 
loops=1) 
Filter: ((id_instalacion = 2::numeric) AND (id_sbc = 619::numeric) AND (id_tipo 
= 2::numeric))Total runtime: 432478.44 msec(19 rows)

esdc= 


If I replace the subquery with a fixed 
date

"AND 
cont_publicacion.fecha_publicacion = 
'17/01/2003'::timestamp"
 
QUERY 
PLAN 
Limit 
(cost=8.72..8.73 rows=1 width=478) (actual time=797.26..797.26 rows=0 
loops=1) - Sort (cost=8.72..8.73 rows=1 
width=478) (actual time=797.25..797.25 rows=0 
loops=1) Sort Key: 
cont_publicacion.fecha_publicacion 
- Merge Join (cost=8.69..8.71 rows=1 width=478) (actual 
time=796.45..796.45 rows=0 
loops=1) 
Merge Cond: (("outer".id_instalacion = "inner".id_instalacion) AND 
("outer".id_contenido = 
"inner".id_contenido)) 
- Nested Loop (cost=0.00..644.29 rows=1 width=367) (actual 
time=796.44..796.44 rows=0 
loops=1) 
Join Filter: (("inner".id_contenido = "outer".id_contenido) AND 
("inner".id_instalacion = 
"outer".id_instalacion)) 
- Index Scan using jue_conf_pk on juegos_config 
(cost=0.00..12.19 rows=40 width=332) (actual time=0.23..6.71 rows=40 
loops=1) 
- Seq Scan on cont_publicacion (cost=0.00..15.79 rows=1 
width=35) (actual time=19.70..19.70 rows=0 
loops=40) 
Filter: ((upper((generar_vainilla)::text)

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

2003-06-26 Thread 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

Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec

Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec

Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec

Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec

Re: [PERFORM] I can't wait too much: Total runtime 432478.44 msec

[PERFORM] I can't wait too much: Total runtime 432478.44 msec

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

7 matches

Site Navigation

Mail list logo

Footer information