I can't wait too much: Total runtime 432478.44 msec - Mailing list pgsql-performance
From | Fernando Papa |
---|---|
Subject | I can't wait too much: Total runtime 432478.44 msec |
Date | |
Msg-id | F1DC5B511E2D1C499E5E20FC6D74160D03A04E56@exch2000.buehuergo.corp.claxson.com Whole thread Raw |
Responses |
Re: I can't wait too much: Total runtime 432478.44 msec
|
List | pgsql-performance |
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...
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)
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
----------------------------------------------------------------------------------------------------------------------------------------------------------------
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: