Re: function executes sql 100 times longer it should - Mailing list pgsql-admin

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