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

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