Thread: SQL function inlining (was: View vs function)
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
>
"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
* 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 -- ---------------------------------------------------------------------
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)
* 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 -- ---------------------------------------------------------------------
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.
* 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 -- ---------------------------------------------------------------------