To: "Davor J." <DavorJ@live.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Slow function in queries SELECT clause. 
In-reply-to: <hvj6cg$2dna$1@news.hub.org> 
References: <hvj6cg$2dna$1@news.hub.org>
Comments: In-reply-to "Davor J." <DavorJ@live.com>
	message dated "Sat, 19 Jun 2010 21:38:14 +0200"
Date: Sun, 20 Jun 2010 11:21:07 -0400
Message-ID: <25116.1277047267@sss.pgh.pa.us>
From: Tom Lane <tgl@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