explain analyze on a function - Mailing list pgsql-general

Is this possible?

I've been searching posts, but to no luck ;(

I have one SQL query inside function, but when
i do select from function it takes 8 sec.
If I execute just SQL query (with some parameters passed to it)
it takes 0.3 seconds.

What I'm trying to do is select part of the data from the table and
then do some aggregation on it.

Here is the function

CREATE OR REPLACE FUNCTION raspored.dohvati_statistiku_rada_u_smjenama_radnika(IN do_datuma date, IN idodj integer, OUT
radnik_idinteger, OUT smjena_id integer, OUT ukupno_sati numeric, OUT 
ukupno_dana integer, OUT radnih_dana integer, OUT vikenda integer, OUT nedjelja integer, OUT praznika integer, OUT
posto_radnih_dananumeric, OUT posto_vikenda numeric, OUT posto_nedjelja numeric, OUT 
posto_praznika numeric)
  RETURNS SETOF record AS
$BODY$
    select podaci.radnik_id, podaci.smjena_id,
        sum(podaci.broj_sati) as ukupno_sati, count(podaci.broj_dana)::int as ukupno_dana,
        count(radni_dani.broj_sati)::int  as broj_radnih_dana,
        count(vikendi.broj_sati)::int  as broj_vikenda,
        count(neradni_dani.broj_sati)::int  as broj_neradnih_dana,
        count(praznici.broj_sati)::int  as broj_praznika,
        count(radni_dani.broj_sati)/sum(podaci.broj_dana) as postotak_rd,
        count(vikendi.broj_sati)/sum(podaci.broj_dana) as postotak_vk,
        count(neradni_dani.broj_sati)/sum(podaci.broj_dana) as postotak_nrd,
        count(praznici.broj_sati)/sum(podaci.broj_dana) as postotak_prz

    from    (select rr.datum, radnik_id, smjena_id, vrijeme.broj_sati(sum(trajanje_rada))::numeric as broj_sati,
vrsta_dana_id,count(*) as broj_dana 
            from raspored.raspored_rada rr, kalendar.dani_kalendara k, raspored.smjene
            where rr.datum<$1 and rr.datum>=$1-120 and rr.datum=k.datum and id_smjena=smjena_id and odjel_id=$2
            group by 1,2,3,5) as podaci
    left join raspored.u_rasporedu_radni_dani_radnika radni_dani
            on     podaci.radnik_id=radni_dani.radnik_id
                and podaci.smjena_id=radni_dani.smjena_id
                and podaci.datum=radni_dani.datum
                and podaci.vrsta_dana_id=radni_dani.vrsta_dana_id
    left join raspored.u_rasporedu_vikendi_radnika vikendi
            on     podaci.radnik_id=vikendi.radnik_id
                and podaci.smjena_id=vikendi.smjena_id
                and podaci.datum=vikendi.datum
                and podaci.vrsta_dana_id=vikendi.vrsta_dana_id
    left join raspored.u_rasporedu_neradni_dani_radnika neradni_dani
            on     podaci.radnik_id=neradni_dani.radnik_id
                and podaci.smjena_id=neradni_dani.smjena_id
                and podaci.datum=neradni_dani.datum
                and podaci.vrsta_dana_id=neradni_dani.vrsta_dana_id
    left join raspored.u_rasporedu_praznici_radnika praznici
            on     podaci.radnik_id=praznici.radnik_id
                and podaci.smjena_id=praznici.smjena_id
                and podaci.datum=praznici.datum
                and podaci.vrsta_dana_id=praznici.vrsta_dana_id
    group by podaci.radnik_id, podaci.smjena_id
$BODY$
  LANGUAGE 'sql' STABLE SECURITY DEFINER;

"Function Scan on dohvati_statistiku_rada_u_smjenama_radnika  (cost=0.00..12.50 rows=1000 width=188) (actual
time=8192.281..8192.714rows=75 loops=1)" 
"Total runtime: 8192.888 ms"

And here is the explain analyze from SQL with two parameters
($1=current_date and $2=4)

"HashAggregate  (cost=1023.94..1043.44 rows=200 width=112) (actual time=309.535..310.083 rows=75 loops=1)"
"  ->  Hash Left Join  (cost=975.44..1015.42 rows=213 width=112) (actual time=163.295..246.655 rows=1164 loops=1)"
"        Hash Cond: ((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))" 
"        ->  Hash Left Join  (cost=773.34..804.79 rows=213 width=104) (actual time=135.081..213.059 rows=1164 loops=1)"
"              Hash Cond: ((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))" 
"              ->  Hash Left Join  (cost=571.25..594.17 rows=213 width=88) (actual time=109.248..182.146 rows=1164
loops=1)"
"                    Hash Cond: ((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))" 
"                    ->  Hash Left Join  (cost=369.15..383.54 rows=213 width=72) (actual time=64.537..129.266 rows=1164
loops=1)"
"                          Hash Cond: ((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=167.06..170.79 rows=213 width=32) (actual time=35.116..94.717
rows=1164loops=1)" 
"                                ->  Merge Join  (cost=122.97..163.86 rows=213 width=32) (actual time=24.071..31.495
rows=1164loops=1)" 
"                                      Merge Cond: (k.datum = rr.datum)"
"                                      ->  Index Scan using dani_kalendara_pkey on dani_kalendara k  (cost=0.00..257.20
rows=3872width=8) (actual time=0.016..1.093 rows=520 loops=1)" 
"                                      ->  Sort  (cost=122.97..123.50 rows=213 width=28) (actual time=22.465..24.610
rows=1164loops=1)" 
"                                            Sort Key: rr.datum"
"                                            ->  Hash Join  (cost=1.15..114.73 rows=213 width=28) (actual
time=0.165..19.771rows=1164 loops=1)" 
"                                                  Hash Cond: (rr.smjena_id = smjene.id_smjena)"
"                                                  ->  Seq Scan on raspored_rada rr  (cost=0.00..102.67 rows=2341
width=28)(actual time=0.118..12.342 rows=2306 loops=1)" 
"                                                        Filter: ((datum < ('now'::text)::date) AND (datum >=
(('now'::text)::date- 120)))" 
"                                                  ->  Hash  (cost=1.14..1.14 rows=1 width=4) (actual time=0.027..0.027
rows=3loops=1)" 
"                                                        ->  Seq Scan on smjene  (cost=0.00..1.14 rows=1 width=4)
(actualtime=0.006..0.013 rows=3 loops=1)" 
"                                                              Filter: (odjel_id = 4)"
"                          ->  Hash  (cost=191.50..191.50 rows=530 width=32) (actual time=29.395..29.395 rows=288
loops=1)"
"                                ->  Hash Join  (cost=106.73..191.50 rows=530 width=32) (actual time=17.754..28.734
rows=288loops=1)" 
"                                      Hash Cond: (rr.datum = k.datum)"
"                                      ->  Seq Scan on raspored_rada rr  (cost=0.00..69.52 rows=2652 width=28) (actual
time=0.011..5.260rows=2620 loops=1)" 
"                                      ->  Hash  (cost=97.06..97.06 rows=774 width=8) (actual time=17.716..17.716
rows=508loops=1)" 
"                                            ->  Hash Join  (cost=1.08..97.06 rows=774 width=8) (actual
time=11.217..16.698rows=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..8.766 rows=3652 loops=1)" 
"                                                  ->  Hash  (cost=1.06..1.06 rows=1 width=4) (actual time=0.022..0.022
rows=1loops=1)" 
"                                                        ->  Seq Scan on vrste_dana  (cost=0.00..1.06 rows=1 width=4)
(actualtime=0.008..0.013 rows=1 loops=1)" 
"                                                              Filter: ((naziv_vrste_dana)::text = 'vikend'::text)"
"                    ->  Hash  (cost=191.50..191.50 rows=530 width=32) (actual time=44.667..44.667 rows=2070 loops=1)"
"                          ->  Hash Join  (cost=106.73..191.50 rows=530 width=32) (actual time=24.050..39.770 rows=2070
loops=1)"
"                                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..6.001rows=2620 loops=1)" 
"                                ->  Hash  (cost=97.06..97.06 rows=774 width=8) (actual time=24.013..24.013 rows=2528
loops=1)"
"                                      ->  Hash Join  (cost=1.08..97.06 rows=774 width=8) (actual time=0.054..18.950
rows=2528loops=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)
(actualtime=0.008..7.151 rows=3652 loops=1)" 
"                                            ->  Hash  (cost=1.06..1.06 rows=1 width=4) (actual time=0.023..0.023
rows=1loops=1)" 
"                                                  ->  Seq Scan on vrste_dana  (cost=0.00..1.06 rows=1 width=4) (actual
time=0.013..0.015rows=1 loops=1)" 
"                                                        Filter: ((naziv_vrste_dana)::text = 'radni_dan'::text)"
"              ->  Hash  (cost=191.50..191.50 rows=530 width=32) (actual time=25.799..25.799 rows=54 loops=1)"
"                    ->  Hash Join  (cost=106.73..191.50 rows=530 width=32) (actual time=15.428..25.663 rows=54
loops=1)"
"                          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.786rows=2620 loops=1)" 
"                          ->  Hash  (cost=97.06..97.06 rows=774 width=8) (actual time=15.118..15.118 rows=120
loops=1)"
"                                ->  Hash Join  (cost=1.08..97.06 rows=774 width=8) (actual time=0.058..14.868 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.008..7.156rows=3652 loops=1)" 
"                                      ->  Hash  (cost=1.06..1.06 rows=1 width=4) (actual time=0.031..0.031 rows=3
loops=1)"
"                                            ->  Seq Scan on vrste_dana  (cost=0.00..1.06 rows=1 width=4) (actual
time=0.011..0.019rows=3 loops=1)" 
"                                                  Filter: ((naziv_vrste_dana)::text ~~ 'praznik%'::text)"
"        ->  Hash  (cost=191.50..191.50 rows=530 width=32) (actual time=28.160..28.160 rows=208 loops=1)"
"              ->  Hash Join  (cost=106.73..191.50 rows=530 width=32) (actual time=16.148..27.123 rows=208 loops=1)"
"                    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.893
rows=2620loops=1)" 
"                    ->  Hash  (cost=97.06..97.06 rows=774 width=8) (actual time=16.105..16.105 rows=496 loops=1)"
"                          ->  Hash Join  (cost=1.08..97.06 rows=774 width=8) (actual time=9.587..15.109 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.008..7.081rows=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.016rows=1 loops=1)" 
"                                            Filter: ((naziv_vrste_dana)::text = 'neradni_dan'::text)"
"Total runtime: 310.689 ms"