I observed slowdowns when I declared SQL function as strict. There were no slowdowns, when I implmented the same function in plpgsql, in fact it got faster with strict, if parameters where NULL. Could it be side-effect of SQL function inlining? Is there CASE added around the function to not calculate it, when one of the parameters is NULL?
The functions: create or replace function keskmine_omahind(kogus, raha) returns raha language sql immutable strict as ' SELECT CASE WHEN $1 > 0 THEN $2 / $1 ELSE NULL END::raha; '; create or replace function keskmine_omahind2(kogus, raha) returns raha language plpgsql immutable strict as ' BEGIN RETURN CASE WHEN $1 > 0 THEN $2 / $1 ELSE NULL END::raha; END; '; With strict: epos=# select count(keskmine_omahind(laokogus, laosumma)) from kaubad; count ------- 9866 (1 row) Time: 860,495 ms epos=# select count(keskmine_omahind2(laokogus, laosumma)) from kaubad; count ------- 9866 (1 row) Time: 178,922 ms Without strict: epos=# select count(keskmine_omahind(laokogus, laosumma)) from kaubad; count ------- 9866 (1 row) Time: 88,151 ms epos=# select count(keskmine_omahind2(laokogus, laosumma)) from kaubad; count ------- 9866 (1 row) Time: 178,383 ms epos=# select version(); version ------------------------------------------------------------------------ ------------------------------ PostgreSQL 7.4.5 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.4 (Debian 1:3.3.4-9) Tambet > -----Original Message----- > From: Neil Conway [mailto:[EMAIL PROTECTED] > Sent: Monday, March 21, 2005 7:13 AM > To: Bruno Wolff III > Cc: Keith Worthington; pgsql-performance@postgresql.org > Subject: Re: View vs function > > > Bruno Wolff III wrote: > > Functions are just black boxes to the planner. > > ... unless the function is a SQL function that is trivial > enough for the > planner to inline it into the plan of the invoking query. > Currently, we > won't inline set-returning SQL functions that are used in the query's > rangetable, though. This would be worth doing, I think -- I'm > not sure > how much work it would be, though. > > -Neil > ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match