Обсуждение: explain analyze on a function
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"
Rikard Pavelic wrote:
> 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.
Try doing a PREPARE and then EXPLAIN EXECUTE, like
alvherre=# prepare foo as select generate_series(1, $1);
PREPARE
alvherre=# explain analyze execute foo(100);
QUERY PLAN
--------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.014..0.294 rows=100 loops=1)
Total runtime: 0.550 ms
(2 filas)
alvherre=# explain analyze execute foo(10000);
QUERY PLAN
-----------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..35.082 rows=10000 loops=1)
Total runtime: 59.077 ms
(2 filas)
--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
"La fuerza no está en los medios físicos
sino que reside en una voluntad indomable" (Gandhi)
Alvaro Herrera wrote: > Try doing a PREPARE and then EXPLAIN EXECUTE, like > > alvherre=# prepare foo as select generate_series(1, $1); > PREPARE > > alvherre=# explain analyze execute foo(100); > QUERY PLAN > -------------------------------------------------------------------------------------- > Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.014..0.294 rows=100 loops=1) > Total runtime: 0.550 ms > (2 filas) > > alvherre=# explain analyze execute foo(10000); > QUERY PLAN > ----------------------------------------------------------------------------------------- > Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..35.082 rows=10000 loops=1) > Total runtime: 59.077 ms > (2 filas) > > > Hm, no luck ;( prepare f1 as select * from raspored.dohvati_statistiku_rada_u_smjenama_radnika($1, $2); explain analyze execute f1(current_date, 4); "Function Scan on dohvati_statistiku_rada_u_smjenama_radnika (cost=0.00..12.50 rows=1000 width=188) (actual time=8013.779..8013.906 rows=75 loops=1)" "Total runtime: 8014.073 ms" Regards, Rikard
Rikard Pavelic wrote: > Alvaro Herrera wrote: > > Try doing a PREPARE and then EXPLAIN EXECUTE, like > > > > alvherre=# prepare foo as select generate_series(1, $1); > Hm, no luck ;( > > prepare f1 as select * from > raspored.dohvati_statistiku_rada_u_smjenama_radnika($1, $2); > explain analyze execute f1(current_date, 4); I meant the queries inside the function. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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