Hi All,
I have a SQL function like :
CREATE OR REPLACE FUNCTION
fn_get_yetkili_inisyer_listesi(int4, int4)
RETURNS SETOF kod_adi_liste_type AS
$BODY$
SELECT Y.KOD,Y.ADI
FROM T_YER Y
WHERE EXISTS (SELECT 1
FROM T_GUZER G
WHERE (G.BIN_YER_KOD = $1 OR COALESCE($1,0)=0)
AND FN_FIRMA_ISVISIBLE(G.FIRMA_NO,$2) = 1
AND G.IN_YER_KOD = Y.KOD)
AND Y.IPTAL = 'H';
$BODY$
LANGUAGE 'sql' VOLATILE;
When i use like "SELECT * FROM
fn_get_yetkili_inisyer_listesi(1, 3474)" and
planner result is "Function Scan on
fn_get_yetkili_inisyer_listesi (cost=0.00..12.50 rows=1000
width=36) (1 row) " and it runs very slow.
But when i use like
"SELECT Y.KOD,Y.ADI
FROM T_YER Y
WHERE EXISTS (SELECT 1
FROM T_GUZER G
WHERE (G.BIN_YER_KOD
= 1 OR COALESCE(1,0)=0)
AND FN_FIRMA_ISVISIBLE(G.FIRMA_NO,3474) = 1
AND G.IN_YER_KOD = Y.KOD)
AND Y.IPTAL = 'H';"
planner result :
"
QUERY PLAN
--------------------------------------------------------------------------------
-----------------------------
Seq Scan on t_yer y (cost=0.00..3307.79 rows=58 width=14)
Filter: (((iptal)::text = 'H'::text) AND (subplan))
SubPlan
-> Index Scan using
t_guzer_ucret_giris_performans_idx on t_guzer g (cost
=0.00..28.73 rows=1 width=0)
Index Cond: ((bin_yer_kod = 1) AND (in_yer_kod =
$0))
Filter: (fn_firma_isvisible(firma_no, 3474) = 1)
(6 rows)
"
and it runs very fast.
Any idea ?
Adnan DURSUN
ASRIN Bilişim Hiz.Ltd.
