Skip site navigation (1) Skip section navigation (2)

SQL function inlining (was: View vs function)

From: "Tambet Matiisen" <t(dot)matiisen(at)aprote(dot)ee>
To: "Neil Conway" <neilc(at)samurai(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: SQL function inlining (was: View vs function)
Date: 2005-03-23 10:03:26
Message-ID: A66A11DBF5525341AEF6B8DE39CDE770088060@black.aprote.com (view raw or flat)
Thread:
Lists: 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(at)samurai(dot)com] 
> Sent: Monday, March 21, 2005 7:13 AM
> To: Bruno Wolff III
> Cc: Keith Worthington; pgsql-performance(at)postgresql(dot)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
> 

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2005-03-23 11:31:28
Subject: Re: best practices with index on varchar column
Previous:From: Oleg BartunovDate: 2005-03-23 09:40:03
Subject: Re: Tsearch2 performance on big database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group