From: "Davor J." <DavorJ@live.com>
Subject: Re: Slow function in queries SELECT clause.
Date: Mon, 21 Jun 2010 08:57:41 +0200
Organization: Hub.Org Networking Services
Lines: 56
Message-ID: <hvn2ij$25ql$1@news.hub.org>
References: <hvj6cg$2dna$1@news.hub.org> <25116.1277047267@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org

Thanks Tom,

Your concepts of "inlining" and "black box" really cleared things up for me. 
With fnc_unit_convert() written in SQL and declared as STABLE I indeed have 
fast performance now.

I appreciate the note on the IMMUTABLE part. The table contents should not 
change in a way to affect the functions. So, as far as I understand the 
Postgres workings, this shouldn't pose a problem.

Regards,
Davor

"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message 
news:25116.1277047267@sss.pgh.pa.us...
> "Davor J." <DavorJ@live.com> writes:
>> Suppose 2 functions: factor(int,int) and offset(int, int).
>> Suppose a third function: convert(float,int,int) which simply returns
>> $1*factor($2,$3)+offset($2,$3)
>> All three functions are IMMUTABLE.
>
> You should write the third function as a SQL function, which'd allow it
> to be inlined.
>
>> VERY FAST (half a second):
>> ----------------
>> SELECT data*factor(1,2)+offset(1,2) FROM tbl_data;
>
> In this case both factor() calls are folded to constants, hence executed
> only once.
>
>> VERY SLOW (a minute):
>> ----------------
>> SELECT convert(data, 1, 2) FROM tbl_data;
>
> Without inlining, there's no hope of any constant-folding here.
> The optimizer just sees the plpgsql function as a black box and
> can't do anything with it.
>
> BTW, your later mail shows that the factor() functions are not really
> IMMUTABLE, since they select from tables that presumably are subject to
> change.  The "correct" declaration would be STABLE.  If you're relying
> on constant-folding to get reasonable application performance, you're
> going to have to continue to mislabel them as IMMUTABLE; but be aware
> that you're likely to have issues any time you do change the table
> contents.  The changes won't get reflected into existing query plans.
>
> regards, tom lane
>
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>