Message-ID: <491BE279.50702@nsoft.lt>
Date: Thu, 13 Nov 2008 10:16:57 +0200
From: Julius Tuskenis <julius@nsoft.lt>
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
MIME-Version: 1.0
CC: pgsql-admin@postgresql.org
Subject: Re: function executes sql 100 times longer it should
References: <491AE3F2.601@nsoft.lt>
	<1226500247.2221.2.camel@lin-it1.eaglepress.local>
In-Reply-To: <1226500247.2221.2.camel@lin-it1.eaglepress.local>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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