Re: function executes sql 100 times longer it should

Hello Sean,

> Explain plan might be a good start
Its a good start, but leads to nothing because Explain doesn't go into
the function.

EXPLAIN select * -- prek_id, prek_pavadinimas, ks_kaina, mvnt_trumpinys
from fnk_grupes_prekes(18,42,NULL);

"Function Scan on fnk_grupes_prekes (cost=0.00..25.10 rows=10 width=143)"

EXPLAIN SELECT DISTINCT prek_id, prek_pavadinimas, ks_kaina, mvnt_trumpinys
FROM filter_b_preke_matoma()
LEFT OUTER JOIN b_grupes_prekes ON (gp_preke=prek_id)
LEFT OUTER JOIN b_grupe ON (gp_grupe=grup_id)
JOIN b_mato_vienetai ON (mvnt_id=prek_matovnt)
JOIN b_kainorascio_sudetis ON (ks_preke = prek_id)
JOIN b_kainorascio_grafikas ON (ks_kainorastis=kag_kainorastis)
JOIN b_pardavimo_tasko_kainorastis ON (ks_kainorastis=ptk_kainorastis)
WHERE (grup_id=42 OR 40 is Null)
AND ptk_pardavimotaskas=18
AND ((prek_pavadinimas ILIKE ('%'||null||'%')) OR null is NULL)
AND fnk_grafikas_galioja(kag_grafikas) = true
ORDER BY prek_pavadinimas

"Unique (cost=281.89..281.91 rows=1 width=132)"
" -> Sort (cost=281.89..281.90 rows=1 width=132)"
" Sort Key: filter_b_preke_matoma.prek_pavadinimas,
filter_b_preke_matoma.prek_id, b_kainorascio_sudetis.ks_kaina,
b_mato_vienetai.mvnt_trumpinys"
" -> Nested Loop (cost=1.07..281.88 rows=1 width=132)"
" -> Nested Loop (cost=1.07..280.35 rows=1 width=136)"
" -> Nested Loop (cost=1.07..280.06 rows=1 width=144)"
" -> Nested Loop (cost=1.07..279.53 rows=1
width=140)"
" -> Nested Loop (cost=1.07..269.69
rows=1 width=133)"
" -> Hash Join (cost=1.07..264.97
rows=15 width=125)"
" Hash Cond:
(filter_b_preke_matoma.prek_matovnt = b_mato_vienetai.mvnt_id)"
" -> Function Scan on
filter_b_preke_matoma (cost=0.00..260.00 rows=1000 width=126)"
" -> Hash (cost=1.03..1.03
rows=3 width=7)"
" -> Seq Scan on
b_mato_vienetai (cost=0.00..1.03 rows=3 width=7)"
" -> Index Scan using idx_gp_preke
on b_grupes_prekes (cost=0.00..0.30 rows=1 width=8)"
" Index Cond:
(b_grupes_prekes.gp_preke = filter_b_preke_matoma.prek_id)"
" Filter:
(b_grupes_prekes.gp_grupe = 42)"
" -> Index Scan using idx_ks_preke on
b_kainorascio_sudetis (cost=0.00..9.80 rows=3 width=15)"
" Index Cond:
(b_kainorascio_sudetis.ks_preke = filter_b_preke_matoma.prek_id)"
" -> Index Scan using idx_kag_kainorastis on
b_kainorascio_grafikas (cost=0.00..0.52 rows=1 width=4)"
" Index Cond:
(b_kainorascio_grafikas.kag_kainorastis =
b_kainorascio_sudetis.ks_kainorastis)"
" Filter:
fnk_grafikas_galioja(b_kainorascio_grafikas.kag_grafikas)"
" -> Index Scan using
unq_kainorastis_pardavimo_taskui on b_pardavimo_tasko_kainorastis
(cost=0.00..0.28 rows=1 width=4)"
" Index Cond:
((b_pardavimo_tasko_kainorastis.ptk_pardavimotaskas = 18) AND
(b_pardavimo_tasko_kainorastis.ptk_kainorastis =
b_kainorascio_sudetis.ks_kainorastis))"
" -> Seq Scan on b_grupe (cost=0.00..1.52 rows=1 width=4)"
" Filter: (b_grupe.grup_id = 42)"

> First run probably put the plan and results into the query cache. The
> second run could just pull it from there instead of going to the tables
> on the disk.
>
I dont think its true, because even if I change parameter values in SQL
it still returns results fast. So my guess is that the problem is
somewhere else. Maybe theres a way to see Explain plan for function body??

--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050