Re: pl/pgsql functions outperforming sql ones?

Pavel, thank you very much for your explanation.

Is it possible to define under what conditions that sql procs will
outperform plpgsql ones, and vice-versa?

-----Original Message-----
From: Pavel Stehule [mailto:pavel(dot)stehule(at)gmail(dot)com]
Sent: January 30, 2012 2:57 AM
To: Carlo Stonebanks
Cc: Merlin Moncure; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?

Hello

2012/1/30 Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)ca>:
> Pavel, are you saying that the code of the stored function is actually
being
> added to the SQL query, instead of a call to it? For example, I have seen
> this:
>
> SELECT myVar
> FROM myTable
> WHERE myVar > 0 AND myFunc(myVar)
>
> And seen the SQL body of myVar appended to the outer query:
>
> ... Filter: SELECT CASE WHERE myVar < 10 THEN true ELSE false END
>
> Is this what we are talking about? Two questions:

yes - it is SQL function "inlining"

>
> 1) Is this also done when the function is called as a SELECT column;
>   e.g. would:
>      SELECT myFunc(myVar) AS result
>   - become:
>      SELECT (
>         SELECT CASE WHERE myVar < 10 THEN true ELSE false END
>      ) AS result?
>

yes

CREATE OR REPLACE FUNCTION public.fx(integer, integer)
RETURNS integer
LANGUAGE sql
AS $function$
select coalesce($1, $2)
$function$

postgres=# explain verbose select fx(random()::int, random()::int);
QUERY PLAN
--------------------------------------------------------------
Result (cost=0.00..0.02 rows=1 width=0)
Output: COALESCE((random())::integer, (random())::integer)
(2 rows)

> 2) Does that not bypass the benefits of IMMUTABLE?
>

no - optimizator works with expanded query - usually is preferred
style a writing SQL functions without flags, because optimizer can
work with definition of SQL function and can set well flags. SQL
function is not black box for optimizer like plpgsql does. And SQL
optimizer chooses a inlining or some other optimizations. Sometimes
explicit flags are necessary, but usually not for scalar SQL
functions.

postgres=# create or replace function public.fxs(int)
postgres-# returns setof int as $$
postgres$# select * from generate_series(1,$1)
postgres$# $$ language sql;
CREATE FUNCTION
postgres=# explain verbose select * from fxs(10);
QUERY PLAN
-------------------------------------------------------------------
Function Scan on public.fxs (cost=0.25..10.25 rows=1000 width=4)
Output: fxs
Function Call: fxs(10)
(3 rows)

postgres=# create or replace function public.fxs(int)
returns setof int as $$
select * from generate_series(1,$1)
$$ language sql IMMUTABLE;
CREATE FUNCTION
postgres=# explain verbose select * from fxs(10);
QUERY PLAN
----------------------------------------------------------------------------
-------
Function Scan on pg_catalog.generate_series (cost=0.00..10.00
rows=1000 width=4)
Output: generate_series.generate_series
Function Call: generate_series(1, 10) --<<<< inlined query
(3 rows)

Regards

Pavel Stehule

>
>
> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Pavel Stehule
> Sent: January 28, 2012 1:38 AM
> To: Carlo Stonebanks
> Cc: Merlin Moncure; pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?
>
> 2012/1/27 Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)ca>:
>> Yes, I did test it  - i.e. I ran the functions on their own as I had
> always
>> noticed a minor difference between EXPLAIN ANALYZE results and direct
> query
>> calls.
>>
>> Interesting, so sql functions DON'T cache plans? Will plan-caching be of
> any
>> benefit to SQL that makes no reference to any tables? The SQL is
emulating
>> the straight non-set-oriented procedural logic of the original plpgsql.
>>
>
> It is not necessary usually - simple SQL functions are merged to outer
> query - there are e few cases where this optimization cannot be
> processed and then there are performance lost.
>
> For example this optimization is not possible (sometimes) when some
> parameter is volatile
>
> Regards
>
> Pavel Stehule
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>