Re: SQL Function performance

On Thu, Sep 29, 2005 at 10:54:58PM +0300, adnandursun@asrinbilisim.com.tr wrote:
> 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 ?

Need EXPLAIN ANALYZE.

I suspect this is due to a cached query plan. PostgreSQL will cache a
query plan for the SELECT the first time you run the function and that
plan will be re-used. Depending on what data you call the function with,
you could get a very different plan.

Also, you might do better with a JOIN instead of using EXISTS.  You can
also make this function STABLE instead of VOLATILE. Likewise, if
FN_FIRMA_ISVISIBLE can't change any data, you can also make it STABLE
which would likely improve the performance of the query. But neither of
these ideas would account for the difference between function
performance and raw query performance.

On a side note, if OR $1 IS NULL works that will be more readable (and
probably faster) than the OR COALESCE($1,0)=0.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461