pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SQL function inlining (was: View vs function)
2+ messages / 2 participants
[nested] [flat]

* SQL function inlining (was: View vs function)
@ 2005-03-23 10:03 Tambet Matiisen <t.matiisen@aprote.ee>
  2005-03-23 16:55 ` Re: SQL function inlining (was: View vs function) Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Tambet Matiisen @ 2005-03-23 10:03 UTC (permalink / raw)
  To: Neil Conway <neilc@samurai.com>; +Cc: pgsql-performance

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
> 



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: SQL function inlining (was: View vs function)
  2005-03-23 10:03 SQL function inlining (was: View vs function) Tambet Matiisen <t.matiisen@aprote.ee>
@ 2005-03-23 16:55 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2005-03-23 16:55 UTC (permalink / raw)
  To: Tambet Matiisen <t.matiisen@aprote.ee>; +Cc: Neil Conway <neilc@samurai.com>; pgsql-performance

"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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-03-23 16:55 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-23 10:03 SQL function inlining (was: View vs function) Tambet Matiisen <t.matiisen@aprote.ee>
2005-03-23 16:55 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox