[PERFORM] SQL Function performance

[adnandursun]

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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly