Обсуждение: Slow function in queries SELECT clause.

I think I have read what is to be read about queries being prepared in
plpgsql functions, but I still can not explain the following, so I thought
to post it here:

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.

Very simple, right? Now I have very fast AND very slow executing queries on
some 150k records:

VERY FAST (half a second):
----------------
SELECT data*factor(1,2)+offset(1,2) FROM tbl_data;

VERY SLOW (a minute):
----------------
SELECT convert(data, 1, 2) FROM tbl_data;

The slowness cannot be due to calling a function 150k times. If I define
convert2(float,int,int) to return a constant value, then it executes in
about a second. (still half as slow as the VERY FAST query).

I assume that factor and offset are cached in the VERY FAST query, and not
in the slow one? If so, why not and how can I "force" it? Currently I need
only one function for conversions.

Regards,
Davor

2010/6/19 Davor J. <DavorJ@live.com>

I think I have read what is to be read about queries being prepared in
plpgsql functions, but I still can not explain the following, so I thought
to post it here:

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.

Very simple, right? Now I have very fast AND very slow executing queries on
some 150k records:

VERY FAST (half a second):
----------------
SELECT data*factor(1,2)+offset(1,2) FROM tbl_data;

VERY SLOW (a minute):
----------------
SELECT convert(data, 1, 2) FROM tbl_data;

The slowness cannot be due to calling a function 150k times. If I define
convert2(float,int,int) to return a constant value, then it executes in
about a second. (still half as slow as the VERY FAST query).

I assume that factor and offset are cached in the VERY FAST query, and not
in the slow one? If so, why not and how can I "force" it? Currently I need
only one function for conversions.

Regards,
Davor

Hi,

show us the code of those two functions and explain analyze of those queries.

regards

Szymon Guz

"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

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
>

> "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.
>>
> 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.

A note on performance here: If I declare the fast SQL function
fnc_unit_convert() as STRICT or as SECURITY DEFINER, then I suddenly get
slow performance again (i.e. no apparent inlining).