Thread: SQL function inlining (was: View vs function)

SQL function inlining (was: View vs function)

From
"Tambet Matiisen"
Date:
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:neilc@samurai.com]
> 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
>

Re: SQL function inlining (was: View vs function)

From
Tom Lane
Date:
"Tambet Matiisen" <t.matiisen@aprote.ee> writes:
> 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?

IIRC we will not inline a STRICT SQL function if the resulting
expression would not behave strict-ly.  This is clearly a necessary rule
because inlining would change the behavior otherwise.  But the test for
it is pretty simplistic: CASEs are considered not strict, period.  So I
think you are measuring the difference between inlined and not-inlined.

I'd suggest just leaving off the STRICT if you are writing a SQL
function you hope to have inlined.

            regards, tom lane

clear function cache (WAS: SQL function inlining)

From
Enrico Weigelt
Date:
* Tom Lane <tgl@sss.pgh.pa.us> wrote:

<big_snip>

BTW: is it possible to explicitly clear the cache for immutable
functions ?

I'd like to use immutable functions for really often lookups like
fetching a username by uid and vice versa. The queried tables
change very rarely, but when they change is quite unpredictable.


thx
--
---------------------------------------------------------------------
 Enrico Weigelt    ==   metux IT service

  phone:     +49 36207 519931         www:       http://www.metux.de/
  fax:       +49 36207 519932         email:     contact@metux.de
  cellphone: +49 174 7066481
---------------------------------------------------------------------
 -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------

Re: clear function cache (WAS: SQL function inlining)

From
Alvaro Herrera
Date:
On Thu, Mar 24, 2005 at 02:32:48PM +0100, Enrico Weigelt wrote:

> BTW: is it possible to explicitly clear the cache for immutable
> functions ?

What cache?  There is no caching of function results.

> I'd like to use immutable functions for really often lookups like
> fetching a username by uid and vice versa. The queried tables
> change very rarely, but when they change is quite unpredictable.

Maybe you should use a stable function if you fear we'll having function
result caching without you noticing.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Aprende a avergonzarte más ante ti que ante los demás" (Demócrito)

Re: clear function cache (WAS: SQL function inlining)

From
Enrico Weigelt
Date:
* Alvaro Herrera <alvherre@dcc.uchile.cl> wrote:
> On Thu, Mar 24, 2005 at 02:32:48PM +0100, Enrico Weigelt wrote:
>
> > BTW: is it possible to explicitly clear the cache for immutable
> > functions ?
>
> What cache?  There is no caching of function results.

Not ? So what's immutable for ?

<snip>
> > I'd like to use immutable functions for really often lookups like
> > fetching a username by uid and vice versa. The queried tables
> > change very rarely, but when they change is quite unpredictable.
>
> Maybe you should use a stable function if you fear we'll having function
> result caching without you noticing.

hmm, this makes more real evaluations necessary than w/ immuatable.
AFAIK stable functions have to be evaluated once per query, and the
results are not cached between several queries.


cu
--
---------------------------------------------------------------------
 Enrico Weigelt    ==   metux IT service

  phone:     +49 36207 519931         www:       http://www.metux.de/
  fax:       +49 36207 519932         email:     contact@metux.de
  cellphone: +49 174 7066481
---------------------------------------------------------------------
 -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------

Re: clear function cache (WAS: SQL function inlining)

From
Stephan Szabo
Date:
On Thu, 24 Mar 2005, Enrico Weigelt wrote:

> * Alvaro Herrera <alvherre@dcc.uchile.cl> wrote:
> > On Thu, Mar 24, 2005 at 02:32:48PM +0100, Enrico Weigelt wrote:
> >
> > > BTW: is it possible to explicitly clear the cache for immutable
> > > functions ?
> >
> > What cache?  There is no caching of function results.
>
> Not ? So what's immutable for ?

For knowing that you can do things like use it in a functional index and
I think for things like constant folding in a prepared plan.

Re: clear function cache (WAS: SQL function inlining)

From
Enrico Weigelt
Date:
* Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
>
> On Thu, 24 Mar 2005, Enrico Weigelt wrote:
>
> > * Alvaro Herrera <alvherre@dcc.uchile.cl> wrote:
> > > On Thu, Mar 24, 2005 at 02:32:48PM +0100, Enrico Weigelt wrote:
> > >
> > > > BTW: is it possible to explicitly clear the cache for immutable
> > > > functions ?
> > >
> > > What cache?  There is no caching of function results.
> >
> > Not ? So what's immutable for ?
>
> For knowing that you can do things like use it in a functional index and
> I think for things like constant folding in a prepared plan.

So when can I expect the function to be reevaluated ?
Next query ? Next session ? Random time ?

cu
--
---------------------------------------------------------------------
 Enrico Weigelt    ==   metux IT service

  phone:     +49 36207 519931         www:       http://www.metux.de/
  fax:       +49 36207 519932         email:     contact@metux.de
  cellphone: +49 174 7066481
---------------------------------------------------------------------
 -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------