From: "Carlo Stonebanks" <stonec.register@sympatico.ca>
To: "'Pavel Stehule'" <pavel.stehule@gmail.com>
Cc: "'Merlin Moncure'" <mmoncure@gmail.com>,
 <pgsql-performance@postgresql.org>
References: <CE4615C16F2F41CAB0E67D4099BF20D2@CAPRICA>
	<CAHyXU0wSjcc3vZ1__1wuUSmM1jrToMBAUj_5BErHda48oqZaoQ@mail.gmail.com>
	<90C49E334DFE48C582A3214BA56E1DD4@CAPRICA>
	<CAFj8pRCceGDEp1sxvMoe0oaio6P=O2nJPfs9MWgu4-+zCTdrVg@mail.gmail.com>
In-Reply-To: 
 <CAFj8pRCceGDEp1sxvMoe0oaio6P=O2nJPfs9MWgu4-+zCTdrVg@mail.gmail.com>
Subject: Re: pl/pgsql functions outperforming sql ones?
Date: Sun, 29 Jan 2012 18:04:53 -0500
Message-ID: <5EE15F7F99964B33A11D6029F39A4FE8@CAPRICA>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: Aczdh483IUOBsrbCSdGdWu6mwhjAKQBULP+A

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;=20
   e.g. would:
      SELECT myFunc(myVar) AS result=20
   - 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@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Pavel =
Stehule
Sent: January 28, 2012 1:38 AM
To: Carlo Stonebanks
Cc: Merlin Moncure; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones?

2012/1/27 Carlo Stonebanks <stonec.register@sympatico.ca>:
> Yes, I did test it =A0- 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

--=20
Sent via pgsql-performance mailing list =
(pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance