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
