pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL 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