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_id integer, 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_dana numeric, 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.714 rows=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=1164 loops=1)"
" -> Merge Join (cost=122.97..163.86 rows=213
width=32) (actual time=24.071..31.495 rows=1164 loops=1)"
" Merge Cond: (k.datum = rr.datum)"
" -> Index Scan using dani_kalendara_pkey
on dani_kalendara k (cost=0.00..257.20 rows=3872 width=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=1164 loops=1)"
" Sort Key: rr.datum"
" -> Hash Join (cost=1.15..114.73
rows=213 width=28) (actual time=0.165..19.771 rows=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=3 loops=1)"
" -> Seq Scan on smjene
(cost=0.00..1.14 rows=1 width=4) (actual time=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=288 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.011..5.260 rows=2620
loops=1)"
" -> Hash (cost=97.06..97.06 rows=774
width=8) (actual time=17.716..17.716 rows=508 loops=1)"
" -> Hash Join (cost=1.08..97.06
rows=774 width=8) (actual time=11.217..16.698 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..8.766 rows=3652 loops=1)"
" -> Hash (cost=1.06..1.06
rows=1 width=4) (actual time=0.022..0.022 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 (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.001 rows=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=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..7.151 rows=3652
loops=1)"
" -> Hash (cost=1.06..1.06 rows=1
width=4) (actual time=0.023..0.023 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 (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.786 rows=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.156 rows=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.019 rows=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=2620 loops=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.081 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: 310.689 ms"
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
