Обсуждение: 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.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)

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

From: ""Fernando Papa"" <fpapa@claxson.com>

> 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

On Fri, 1 Aug 2003 18:17:17 -0300, "Fernando Papa" <fpapa@claxson.com>
wrote:
> 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)

If certain uniqueness conditions are met, the Postgres specific
DISTINCT ON clause could help totally eliminating the subselect:

SELECT DISTINCT ON (
                    cp.id_instalacion,
                    cp.id_contenido,
                    cp.generar_vainilla,
                    cp.fecha_publicacion
                   )
  cc.id_contenido
 ,cc.pertenece_premium
 ,cc.Titulo_esp as v_sufix
 ,cc.url_contenido
 ,cc.tipo_acceso
 ,cc.id_sbc
 ,cc.cant_vistos
 ,cc.cant_votos
 ,cc.puntaje_total
 ,cc.id_contenido_padre
 ,jc.imagen_tapa_especial
 ,jc.info_general_esp as info_general
 ,jc.ayuda
 ,jc.tips_tricks_esp as tips_tricks
 ,jc.mod_imagen_tapa_especial
 ,cp.fecha_publicacion as fecha_publicacion
 ,cp.generar_Vainilla
 FROM
 cont_contenido cc
 ,juegos_config jc
 ,cont_publicacion cp
WHERE
 cc.id_instalacion        = 2
 AND cc.id_sbc           = 619
 AND cc.id_tipo           = 2
 AND cc.id_instalacion  = jc.id_instalacion
 AND cc.id_contenido   = jc.id_contenido
 AND upper(cp.generar_Vainilla) = 'S'
 AND cp.id_instalacion = cc.id_instalacion
 AND cp.id_contenido = cc.id_contenido
 ORDER BY
           cp.id_instalacion,
           cp.id_contenido,
           cp.generar_vainilla,
           cp.fecha_publicacion desc

However, this doesn't get the result in the original order, so you
have to wrap another SELECT ... ORDER BY ... LIMIT around it.  Or try
to move the subselect into the FROM clause:

SELECT
  cc.id_contenido
 ,cc.pertenece_premium
 ,cc.Titulo_esp as v_sufix
 ,cc.url_contenido
 ,cc.tipo_acceso
 ,cc.id_sbc
 ,cc.cant_vistos
 ,cc.cant_votos
 ,cc.puntaje_total
 ,cc.id_contenido_padre
 ,jc.imagen_tapa_especial
 ,jc.info_general_esp as info_general
 ,jc.ayuda
 ,jc.tips_tricks_esp as tips_tricks
 ,jc.mod_imagen_tapa_especial
 ,cp.fecha_publicacion as fecha_publicacion
 ,cp.generar_Vainilla
 FROM
 cont_contenido cc
 ,juegos_config jc
 ,(SELECT DISTINCT ON (
                    id_instalacion,
                    id_contenido,
                    generar_vainilla,
                    fecha_publicacion
                   )
          *
     FROM cont_publicacion
    ORDER BY
           id_instalacion,
           id_contenido,
           generar_vainilla,
           fecha_publicacion desc
  ) AS cp
WHERE
 cc.id_instalacion        = 2
 AND cc.id_sbc           = 619
 AND cc.id_tipo           = 2
 AND cc.id_instalacion  = jc.id_instalacion
 AND cc.id_contenido   = jc.id_contenido
 AND upper(cp.generar_Vainilla) = 'S'
 AND cp.id_instalacion = cc.id_instalacion
 AND cp.id_contenido = cc.id_contenido
 ORDER BY
           cp.fecha_publicacion desc
 LIMIT 10
 OFFSET 0

[completely untested]

Servus
 Manfred

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.31rows=40 loops=1) 
                     ->  Seq Scan on cont_publicacion  (cost=0.00..7118.60 rows=1 width=35) (actual
time=51.79..11617.12rows=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)
(actualtime=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=8loops=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:cbbrowne@libertyrms.info]
Enviado el: viernes, 01 de agosto de 2003 18:27
Para: Fernando Papa
CC: pgsql-performance@postgresql.org
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.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)

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:josh@agliodbs.com]
Enviado el: viernes, 01 de agosto de 2003 18:32
Para: Christopher Browne; Fernando Papa
CC: pgsql-performance@postgresql.org
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

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.73rows=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.86rows=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:mendola@bigfoot.com]
Enviado el: sábado, 02 de agosto de 2003 7:36
Para: pgsql-performance@postgresql.org
CC: Fernando Papa
Asunto: Re: I can't wait too much: Total runtime 432478.44 msec


From: ""Fernando Papa"" <fpapa@claxson.com>

> 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

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

On Mon, 4 Aug 2003 12:02:46 -0300, "Fernando Papa" <fpapa@claxson.com>
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

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

-----Mensaje original-----
De: Manfred Koizar [mailto:mkoi-pg@aon.at]
Enviado el: lunes, 04 de agosto de 2003 12:17
Para: Fernando Papa
CC: Volker Helm; pgsql-performance@postgresql.org
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" <fpapa@claxson.com>
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

I was play with nested loops, and I found this:


Original explain:

 Limit  (cost=9.75..9.76 rows=1 width=479) (actual
time=436858.90..436858.93 rows=8 loops=1)
   ->  Sort  (cost=9.75..9.76 rows=1 width=479) (actual
time=436858.88..436858.89 rows=8 loops=1)
         Sort Key: cont_publicacion.fecha_publicacion
         ->  Merge Join  (cost=9.73..9.74 rows=1 width=479) (actual
time=196970.93..436858.04 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.46 rows=1 width=367)
(actual time=7525.51..436843.27 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.38..6.63 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=48.81..10917.53 rows=97 loops=40)
                           Index Cond: (upper((generar_vainilla)::text)
= 'S'::text)
                           Filter: (subplan)
                           SubPlan
                             ->  Aggregate  (cost=15.85..15.85 rows=1
width=8) (actual time=24.30..24.30 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.17..24.12 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=8.91..8.95 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.45..7.59 rows=8 loops=1)
                           Filter: ((id_instalacion = 2::numeric) AND
(id_sbc = 619::numeric) AND (id_tipo = 2::numeric))
 Total runtime: 436860.84 msec
(21 rows)

With set enable_nestloop to off :

------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------
 Limit  (cost=55.15..55.16 rows=1 width=479) (actual
time=11394.79..11394.82 rows=8 loops=1)
   ->  Sort  (cost=55.15..55.16 rows=1 width=479) (actual
time=11394.77..11394.79 rows=8 loops=1)
         Sort Key: cont_publicacion.fecha_publicacion
         ->  Merge Join  (cost=55.13..55.14 rows=1 width=479) (actual
time=11380.12..11394.01 rows=8 loops=1)
               Merge Cond: (("outer".id_instalacion =
"inner".id_instalacion) AND ("outer".id_contenido =
"inner".id_contenido))
               ->  Merge Join  (cost=45.40..45.41 rows=1 width=367)
(actual time=11358.48..11380.18 rows=40 loops=1)
                     Merge Cond: (("outer".id_instalacion =
"inner".id_instalacion) AND ("outer".id_contenido =
"inner".id_contenido))
                     ->  Index Scan using jue_conf_pk on juegos_config
(cost=0.00..12.19 rows=40 width=332) (actual time=0.23..5.62 rows=40
loops=1)
                     ->  Sort  (cost=45.40..45.40 rows=1 width=35)
(actual time=11357.48..11357.68 rows=97 loops=1)
                           Sort Key: cont_publicacion.id_instalacion,
cont_publicacion.id_contenido
                           ->  Index Scan using idx_generar_vainilla_ci
on cont_publicacion  (cost=0.00..45.39 rows=1 width=35) (actual
time=48.81..11339.80 rows=97 loops=1)
                                 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=25.21..25.22 rows=1 loops=447)
                                         ->  Seq Scan on
cont_publicacion cp1  (cost=0.00..15.84 rows=1 width=8) (actual
time=10.21..25.07 rows=7 loops=447)
                                               Filter: ((id_instalacion
= $0) AND (id_contenido = $1) AND (generar_vainilla = $2))
               ->  Sort  (cost=9.73..9.74 rows=3 width=112) (actual
time=8.77..8.79 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.45..7.41 rows=8 loops=1)
                           Filter: ((id_instalacion = 2::numeric) AND
(id_sbc = 619::numeric) AND (id_tipo = 2::numeric))
 Total runtime: 11397.66 msec
(22 rows)



Why postgresql don't choose not to use nested loop? Why is more cheap to
use nested loops but It's take a lot of time?

"Fernando Papa" <fpapa@claxson.com> writes:
>                ->  Nested Loop  (cost=0.00..1828.46 rows=1 width=367)
> (actual time=7525.51..436843.27 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.38..6.63 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=48.81..10917.53 rows=97 loops=40)
>                            Index Cond: (upper((generar_vainilla)::text)
> = 'S'::text)
>                            Filter: (subplan)
>                            SubPlan
>                              ->  Aggregate  (cost=15.85..15.85 rows=1
> width=8) (actual time=24.30..24.30 rows=0 loops=17880)

As best I can tell, the problem here is coming from a drastic
underestimate of the number of rows selected by
"upper(generar_vainilla) = 'S'".  Evidently there are about 450 such
rows (since in 40 repetitions of the inner index scan, the aggregate
subplan gets evaluated 17880 times), but the planner seems to think
there will be only about two such rows.  Had it made a more correct
estimate, it would never have picked a plan that required multiple
repetitions of the indexscan.

One thing I'm wondering is if you've VACUUM ANALYZEd cont_publicacion
lately --- the cost estimate seems on the small side, and I'm wondering
if the planner thinks the table is much smaller than it really is.  But
assuming you didn't make that mistake, the only solution I can see is to
not use a functional index.  The planner is not good about making row
count estimates for functional indexes.  You could replace the index on
upper(generar_vainilla) with a plain index on generar_vainilla, and
change the query condition from "upper(generar_vainilla) = 'S'" to
"generar_vainilla IN ('S', 's')".  I think the planner would have a lot
better chance at understanding the statistics that way.

            regards, tom lane

Thanks Tom. I vaccumed full every night. Now I drop function index and
change the upper. Nothing change (I know, total time rise because we are
doing other things on database now). But you can see, if was any
performace gain i didn't see. Actually I get better results when I
disable nested loops or disable merge joins, as I write in a older post.

Thanks!

 Limit  (cost=9.76..9.76 rows=1 width=479) (actual
time=720480.00..720480.03 rows=8 loops=1)
   ->  Sort  (cost=9.76..9.76 rows=1 width=479) (actual
time=720479.99..720480.00 rows=8 loops=1)
         Sort Key: cont_publicacion.fecha_publicacion
         ->  Merge Join  (cost=9.73..9.75 rows=1 width=479) (actual
time=323197.81..720477.96 rows=8 loops=1)
               Merge Cond: (("outer".id_instalacion =
"inner".id_instalacion) AND ("outer".id_contenido =
"inner".id_contenido))
               ->  Nested Loop  (cost=0.00..213197.04 rows=4 width=367)
(actual time=12136.55..720425.66 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=34.13..92.02 rows=40
loops=1)
                     ->  Seq Scan on cont_publicacion
(cost=0.00..5329.47 rows=10 width=35) (actual time=41.74..18004.75
rows=97 loops=40)
                           Filter: (((generar_vainilla = 'S'::character
varying) OR (generar_vainilla = 's'::character varying)) AND
(fecha_publicacion = (subplan)))
                           SubPlan
                             ->  Aggregate  (cost=11.86..11.86 rows=1
width=8) (actual time=40.15..40.15 rows=1 loops=17880)
                                   ->  Index Scan using
cont_pub_gen_vainilla on cont_publicacion cp1  (cost=0.00..11.86 rows=1
width=8) (actual time=16.89..40.01 rows=7 loops=17880)
                                         Index Cond: (generar_vainilla =
$2)
                                         Filter: ((id_instalacion = $0)
AND (id_contenido = $1))
               ->  Sort  (cost=9.73..9.74 rows=3 width=112) (actual
time=30.96..31.00 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.65..28.98 rows=8 loops=1)
                           Filter: ((id_instalacion = 2::numeric) AND
(id_sbc = 619::numeric) AND (id_tipo = 2::numeric))
 Total runtime: 720595.77 msec
(20 rows)

-----Mensaje original-----
De: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Enviado el: lunes, 04 de agosto de 2003 18:28
Para: Fernando Papa
CC: pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] I can't wait too much: Total runtime 432478.44
msec


"Fernando Papa" <fpapa@claxson.com> writes:
>                ->  Nested Loop  (cost=0.00..1828.46 rows=1 width=367)
> (actual time=7525.51..436843.27 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.38..6.63 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=48.81..10917.53 rows=97 loops=40)
>                            Index Cond:
> (upper((generar_vainilla)::text) = 'S'::text)
>                            Filter: (subplan)
>                            SubPlan
>                              ->  Aggregate  (cost=15.85..15.85 rows=1
> width=8) (actual time=24.30..24.30 rows=0 loops=17880)

As best I can tell, the problem here is coming from a drastic
underestimate of the number of rows selected by
"upper(generar_vainilla) = 'S'".  Evidently there are about 450 such
rows (since in 40 repetitions of the inner index scan, the aggregate
subplan gets evaluated 17880 times), but the planner seems to think
there will be only about two such rows.  Had it made a more correct
estimate, it would never have picked a plan that required multiple
repetitions of the indexscan.

One thing I'm wondering is if you've VACUUM ANALYZEd cont_publicacion
lately --- the cost estimate seems on the small side, and I'm wondering
if the planner thinks the table is much smaller than it really is.  But
assuming you didn't make that mistake, the only solution I can see is to
not use a functional index.  The planner is not good about making row
count estimates for functional indexes.  You could replace the index on
upper(generar_vainilla) with a plain index on generar_vainilla, and
change the query condition from "upper(generar_vainilla) = 'S'" to
"generar_vainilla IN ('S', 's')".  I think the planner would have a lot
better chance at understanding the statistics that way.

            regards, tom lane

"Fernando Papa" <fpapa@claxson.com> writes:
> Thanks Tom. I vaccumed full every night. Now I drop function index and
> change the upper. Nothing change (I know, total time rise because we are
> doing other things on database now).

>                      ->  Seq Scan on cont_publicacion
> (cost=0.00..5329.47 rows=10 width=35) (actual time=41.74..18004.75
> rows=97 loops=40)
>                            Filter: (((generar_vainilla = 'S'::character
> varying) OR (generar_vainilla = 's'::character varying)) AND
> (fecha_publicacion = (subplan)))
>                            SubPlan
>                              ->  Aggregate  (cost=11.86..11.86 rows=1
> width=8) (actual time=40.15..40.15 rows=1 loops=17880)

Something fishy going on here.  Why did it switch to a seqscan,
considering it still (mistakenly) thinks there are only going to be 10
or 20 rows matching the generar_vainilla condition?  How many rows have
generar_vainilla equal to 's' or 'S', anyway?

In any case, the real problem is to get rid of the subselect at the
Now that I look at your original query, I see that what you really seem
to be after is the publications with latest pub date among each group with
identical id_instalacion, id_contenido, and generar_vainilla.  You would
probably do well to reorganize the query using SELECT DISTINCT ON, viz

SELECT * FROM
(SELECT DISTINCT ON (id_instalacion, id_contenido, generar_vainilla)
  ...
 FROM ...
 WHERE ...
 ORDER BY
  id_instalacion, id_contenido, generar_vainilla, fecha_publicacion DESC)
AS ss
ORDER BY fecha_publicacion desc
LIMIT 10
OFFSET 0

See the "weather reports" example in the SELECT reference page for
motivation.

            regards, tom lane

On Mon, 04 Aug 2003 16:10:18 +0200, I wrote:
>SELECT DISTINCT ON (
>                    cp.id_instalacion,
>                    cp.id_contenido,
>                    cp.generar_vainilla,
>                    cp.fecha_publicacion
>                   )

Cut'n'paste error!  fecha_publicacion should not be in the DISTINCT ON
list.  The same error is in my second suggestion (FROM (subselect)).

Servus
 Manfred

Sorry Guy,

was just a little tired yesterday.

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

Try to make a group by in the inline-view, so you will get something
like this:

>
> On Mon, 4 Aug 2003 12:02:46 -0300, "Fernando Papa" <fpapa@claxson.com>
> wrote:
> >     FROM
> >     cont_contenido
> >     ,juegos_config
> >     ,cont_publicacion
> >     ,(SELECT id_instalacion,
                id_contenido,
                generar_vainilla,
                max(cp1.fecha_publicacion) as max_pub
         FROM cont_publicacion cp1
         GROUP BY id_instalacion,id_contenido,generar_vainilla) a
where
  ...
  AND a.id_instalacion = cont_publicacion.id_instalacion
  AND a.id_contenido = cont_publicacion.id_contenido
  AND a.generar_vainilla = cont_publicacion.generar_vainilla
  AND a.max_pub = cont_publicacion.fecha_publicacion


Sorry for this missing group.

Bye,

Volker