Re: pl/pgsql functions outperforming sql ones?

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:

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?

2) Does that not bypass the benefits of IMMUTABLE?

-----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