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

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