From: adnandursun@asrinbilisim.com.tr
To: pgsql-performance@postgresql.org
Subject: SQL Function performance
Date: Thu, 29 Sep 2005 22:54:58 +0300
Message-ID: <web-145786064@mail3.doruk.net.tr> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adnandursun@asrinbilisim.com.tr
Subject: Re: SQL Function performance
In-Reply-To: <web-145786064@mail3.doruk.net.tr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox