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

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?