Postgres Pro
Downloads
Home   >   mailing lists

Re: I can't wait too much: Total runtime 432478.44 msec - Mailing list pgsql-performance

From Fernando Papa
Subject Re: I can't wait too much: Total runtime 432478.44 msec
Date
Msg-id F1DC5B511E2D1C499E5E20FC6D74160D03A04E5F@exch2000.buehuergo.corp.claxson.com
Whole thread Raw
In response to I can't wait too much: Total runtime 432478.44 msec  ("Fernando Papa" <fpapa@claxson.com>)
Responses Re: I can't wait too much: Total runtime 432478.44 msec
List pgsql-performance
Tree view
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:vhelm@shcom.de]
Enviado el: lunes, 04 de agosto de 2003 11:45
Para: Fernando Papa
Asunto: 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 here
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 = a.max_pub                              --  change here     
 ORDER BY  cont_publicacion.fecha_publicacion desc
 
hope it helps,
 
Volker Helm
-----Ursprüngliche Nachricht-----
Von: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]Im Auftrag von Fernando Papa
Gesendet: Freitag, 1. August 2003 23:17
An: pgsql-performance@postgresql.org
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 msec
I made a explain analyze, but really I don't undertand why...
 
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 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 10
 OFFSET 0
esdc->;
                                                                       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) = 'S'::text) AND (fecha_publicacion = '17/01/2003 00:00:00'::timestamp without time zone))
               ->  Sort  (cost=8.69..8.70 rows=3 width=111) (never executed)
                     Sort Key: cont_contenido.id_instalacion, cont_contenido.id_contenido
                     ->  Seq Scan on cont_contenido  (cost=0.00..8.66 rows=3 width=111) (never executed)
                           Filter: ((id_instalacion = 2::numeric) AND (id_sbc = 619::numeric) AND (id_tipo = 2::numeric))
 Total runtime: 798.79 msec
 
run very smooth.
 
I have another query similar to this query (include more tables, but have the same subquery) but I don't have any problems.
 
Somebody can help me with this mess? Thanks in advance!!!
 
Fernando.-

pgsql-performance by date:

Previous
From: "Fernando Papa"
Date:
Subject: Re: I can't wait too much: Total runtime 432478.44 msec
Next
From: Manfred Koizar
Date:
Subject: Re: I can't wait too much: Total runtime 432478.44 msec