From: <adnandursun@asrinbilisim.com.tr>
Subject: SQL Function performance
To: pgsql-performance@postgresql.org
Date: Thu, 29 Sep 2005 22:54:58 +0300
Message-ID: <web-145786064@mail3.doruk.net.tr>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-9"
Content-Transfer-Encoding: 8bit

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.