Re: explain analyze on a function - Mailing list pgsql-general

Alvaro Herrera wrote:
>
> I meant the queries inside the function.
>
>

Oh ;(

Here it is

"HashAggregate  (cost=825.10..825.19 rows=1 width=112) (actual
time=59175.752..59176.301 rows=75 loops=1)"
"  ->  Nested Loop Left Join  (cost=443.57..825.06 rows=1 width=112)
(actual time=148.338..58997.576 rows=1164 loops=1)"
"        Join Filter: ((podaci.radnik_id = rr.radnik_id) AND
(podaci.smjena_id = rr.smjena_id) AND (podaci.datum = rr.datum) AND
(podaci.vrsta_dana_id = k.vrsta_dana_id))"
"        ->  Nested Loop Left Join  (cost=336.84..622.96 rows=1
width=104) (actual time=124.497..46278.143 rows=1164 loops=1)"
"              Join Filter: ((podaci.radnik_id = rr.radnik_id) AND
(podaci.smjena_id = rr.smjena_id) AND (podaci.datum = rr.datum) AND
(podaci.vrsta_dana_id = k.vrsta_dana_id))"
"              ->  Nested Loop Left Join  (cost=230.11..420.87 rows=1
width=88) (actual time=100.447..34376.459 rows=1164 loops=1)"
"                    Join Filter: ((podaci.radnik_id = rr.radnik_id) AND
(podaci.smjena_id = rr.smjena_id) AND (podaci.datum = rr.datum) AND
(podaci.vrsta_dana_id = k.vrsta_dana_id))"
"                    ->  Nested Loop Left Join  (cost=123.38..218.77
rows=1 width=72) (actual time=57.764..13172.231 rows=1164 loops=1)"
"                          Join Filter: ((podaci.radnik_id =
rr.radnik_id) AND (podaci.smjena_id = rr.smjena_id) AND (podaci.datum =
rr.datum) AND (podaci.vrsta_dana_id = k.vrsta_dana_id))"
"                          ->  HashAggregate  (cost=16.65..16.67 rows=1
width=32) (actual time=31.240..117.905 rows=1164 loops=1)"
"                                ->  Nested Loop  (cost=0.00..16.63
rows=1 width=32) (actual time=0.087..27.530 rows=1164 loops=1)"
"                                      ->  Nested Loop  (cost=0.00..9.58
rows=1 width=28) (actual time=0.072..8.849 rows=1164 loops=1)"
"                                            ->  Seq Scan on smjene
(cost=0.00..1.14 rows=1 width=4) (actual time=0.012..0.026 rows=3 loops=1)"
"                                                  Filter: (odjel_id = $2)"
"                                            ->  Index Scan using
raspored_rada_pkey on raspored_rada rr  (cost=0.00..8.43 rows=1
width=28) (actual time=0.039..1.639 rows=388 loops=3)"
"                                                  Index Cond:
((rr.datum < $1) AND (rr.datum >= ($1 - 120)) AND (smjene.id_smjena =
rr.smjena_id))"
"                                      ->  Index Scan using
dani_kalendara_pkey on dani_kalendara k  (cost=0.00..7.04 rows=1
width=8) (actual time=0.004..0.006 rows=1 loops=1164)"
"                                            Index Cond: (rr.datum =
k.datum)"
"                          ->  Hash Join  (cost=106.73..191.50 rows=530
width=32) (actual time=0.036..10.679 rows=288 loops=1164)"
"                                Hash Cond: (rr.datum = k.datum)"
"                                ->  Seq Scan on raspored_rada rr
(cost=0.00..69.52 rows=2652 width=28) (actual time=0.008..5.424
rows=2620 loops=1164)"
"                                ->  Hash  (cost=97.06..97.06 rows=774
width=8) (actual time=15.164..15.164 rows=508 loops=1)"
"                                      ->  Hash Join  (cost=1.08..97.06
rows=774 width=8) (actual time=9.112..14.167 rows=508 loops=1)"
"                                            Hash Cond: (k.vrsta_dana_id
= postavke.vrste_dana.id_vrsta_dana)"
"                                            ->  Seq Scan on
dani_kalendara k  (cost=0.00..73.72 rows=3872 width=8) (actual
time=0.008..6.407 rows=3652 loops=1)"
"                                            ->  Hash  (cost=1.06..1.06
rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1)"
"                                                  ->  Seq Scan on
vrste_dana  (cost=0.00..1.06 rows=1 width=4) (actual time=0.008..0.013
rows=1 loops=1)"
"                                                        Filter:
((naziv_vrste_dana)::text = 'vikend'::text)"
"                    ->  Hash Join  (cost=106.73..191.50 rows=530
width=32) (actual time=0.034..14.539 rows=2070 loops=1164)"
"                          Hash Cond: (rr.datum = k.datum)"
"                          ->  Seq Scan on raspored_rada rr
(cost=0.00..69.52 rows=2652 width=28) (actual time=0.007..5.480
rows=2620 loops=1164)"
"                          ->  Hash  (cost=97.06..97.06 rows=774
width=8) (actual time=23.487..23.487 rows=2528 loops=1)"
"                                ->  Hash Join  (cost=1.08..97.06
rows=774 width=8) (actual time=0.054..18.583 rows=2528 loops=1)"
"                                      Hash Cond: (k.vrsta_dana_id =
postavke.vrste_dana.id_vrsta_dana)"
"                                      ->  Seq Scan on dani_kalendara k
(cost=0.00..73.72 rows=3872 width=8) (actual time=0.008..6.779 rows=3652
loops=1)"
"                                      ->  Hash  (cost=1.06..1.06 rows=1
width=4) (actual time=0.024..0.024 rows=1 loops=1)"
"                                            ->  Seq Scan on vrste_dana
(cost=0.00..1.06 rows=1 width=4) (actual time=0.013..0.015 rows=1 loops=1)"
"                                                  Filter:
((naziv_vrste_dana)::text = 'radni_dan'::text)"
"              ->  Hash Join  (cost=106.73..191.50 rows=530 width=32)
(actual time=0.294..10.110 rows=54 loops=1164)"
"                    Hash Cond: (rr.datum = k.datum)"
"                    ->  Seq Scan on raspored_rada rr  (cost=0.00..69.52
rows=2652 width=28) (actual time=0.007..5.345 rows=2620 loops=1164)"
"                    ->  Hash  (cost=97.06..97.06 rows=774 width=8)
(actual time=13.441..13.441 rows=120 loops=1)"
"                          ->  Hash Join  (cost=1.08..97.06 rows=774
width=8) (actual time=0.061..13.170 rows=120 loops=1)"
"                                Hash Cond: (k.vrsta_dana_id =
postavke.vrste_dana.id_vrsta_dana)"
"                                ->  Seq Scan on dani_kalendara k
(cost=0.00..73.72 rows=3872 width=8) (actual time=0.009..6.407 rows=3652
loops=1)"
"                                ->  Hash  (cost=1.06..1.06 rows=1
width=4) (actual time=0.035..0.035 rows=3 loops=1)"
"                                      ->  Seq Scan on vrste_dana
(cost=0.00..1.06 rows=1 width=4) (actual time=0.013..0.021 rows=3 loops=1)"
"                                            Filter:
((naziv_vrste_dana)::text ~~ 'praznik%'::text)"
"        ->  Hash Join  (cost=106.73..191.50 rows=530 width=32) (actual
time=0.038..10.540 rows=208 loops=1164)"
"              Hash Cond: (rr.datum = k.datum)"
"              ->  Seq Scan on raspored_rada rr  (cost=0.00..69.52
rows=2652 width=28) (actual time=0.007..5.362 rows=2620 loops=1164)"
"              ->  Hash  (cost=97.06..97.06 rows=774 width=8) (actual
time=15.180..15.180 rows=496 loops=1)"
"                    ->  Hash Join  (cost=1.08..97.06 rows=774 width=8)
(actual time=9.093..14.194 rows=496 loops=1)"
"                          Hash Cond: (k.vrsta_dana_id =
postavke.vrste_dana.id_vrsta_dana)"
"                          ->  Seq Scan on dani_kalendara k
(cost=0.00..73.72 rows=3872 width=8) (actual time=0.009..6.407 rows=3652
loops=1)"
"                          ->  Hash  (cost=1.06..1.06 rows=1 width=4)
(actual time=0.025..0.025 rows=1 loops=1)"
"                                ->  Seq Scan on vrste_dana
(cost=0.00..1.06 rows=1 width=4) (actual time=0.011..0.016 rows=1 loops=1)"
"                                      Filter: ((naziv_vrste_dana)::text
= 'neradni_dan'::text)"
"Total runtime: 59176.877 ms"

I'm not to good at analyzing this, so any help would be appriciated

Regards,
Rikard