subject:"\[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

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,

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

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

2003-08-02 Thread Mendola Gaetano

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 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[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] I can't wait too much: Total runtime 432478.44 msec

2003-08-01 Thread Christopher Browne


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.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)

That would get rid of the aggregate that's sitting deep in the query.
-- 
select 'cbbrowne' || '@' || 'libertyrms.info';
http://dev6.int.libertyrms.com/
Christopher Browne
(416) 646 3304 x124 (land)

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

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

2003-08-01 Thread Josh Berkus

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


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

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

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] I can't wait too much: Total runtime 432478.44 msec

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

9 matches

Site Navigation

Mail list logo

Footer information