pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes IMMUTABLE property propagate?
12+ messages / 7 participants
[nested] [flat]
* Does IMMUTABLE property propagate?
@ 2010-03-06 02:44 Petru Ghita <petrutz@venaver.info>
2010-03-06 08:02 ` Re: Does IMMUTABLE property propagate? Jasen Betts <jasen@xnet.co.nz>
2010-03-06 14:45 ` Re: Does IMMUTABLE property propagate? Greg Stark <stark@mit.edu>
0 siblings, 2 replies; 12+ messages in thread
From: Petru Ghita @ 2010-03-06 02:44 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Given f1(x) as IMMUTABLE and f2(x) as IMMUTABLE, and f3(f1,f2) as
IMMUTABLE, does the query planner cache the result of f3 and reuse it
or if you want to get a little more speed you better explicitly define
yourself f3 as IMMUTABLE?
I had an aggregate query like:
select id,
sum(p1*f1(a)/f2(b) as r1,
sum(p2*f1(a)/f2(b) as r2,
...
sum(pn*f1(a)/f2(b) as rn
...
group by id;
Where f1(x) and f2(x) were defined as IMMUTABLE.
By the experiments I ran looks like after defining a new function
f3(a,b):= f1(a)/f2(b) and rewriting the query as:
select id,
sum(p1*f3(a,b) as r1,
sum(p2*f3(a,b) as r2,
...
sum(pn*f3(a,b) as rn
...
group by id;
*Looks like* I got a little (5%) improvement in performance of the
query. Is there a way to find out if the function is re-evaluated each
time?
Is this the recommended way to proceed?
Thank you!
Petru Ghita
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkuRwYQACgkQt6IL6XzynQTHEgCffi2QMWkkvTIsuglsanvcUyRB
I+wAoKr22B7FJJVDCssGKGwB8zr4NjQG
=V/BS
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Does IMMUTABLE property propagate?
2010-03-06 02:44 Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
@ 2010-03-06 08:02 ` Jasen Betts <jasen@xnet.co.nz>
1 sibling, 0 replies; 12+ messages in thread
From: Jasen Betts @ 2010-03-06 08:02 UTC (permalink / raw)
To: pgsql-sql
On 2010-03-06, Petru Ghita <petrutz@venaver.info> wrote:
>
> Given f1(x) as IMMUTABLE and f2(x) as IMMUTABLE, and f3(f1,f2) as
> IMMUTABLE, does the query planner cache the result of f3 and reuse it
> or if you want to get a little more speed you better explicitly define
> yourself f3 as IMMUTABLE?
>
> I had an aggregate query like:
>
> select id,
> sum(p1*f1(a)/f2(b) as r1,
> sum(p2*f1(a)/f2(b) as r2,
> ...
> sum(pn*f1(a)/f2(b) as rn
>
> ...
> group by id;
should be smart enough to know that.
> Where f1(x) and f2(x) were defined as IMMUTABLE.
> By the experiments I ran looks like after defining a new function
> f3(a,b):= f1(a)/f2(b) and rewriting the query as:
>
> select id,
> sum(p1*f3(a,b) as r1,
> sum(p2*f3(a,b) as r2,
> ...
> sum(pn*f3(a,b) as rn
>
> ...
> group by id;
>
> *Looks like* I got a little (5%) improvement in performance of the
> query. Is there a way to find out if the function is re-evaluated each
> time?
add a " raise notce 'here'; " to it (if plpgsql)
more likely 5% is the function call overhead.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Does IMMUTABLE property propagate?
2010-03-06 02:44 Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
@ 2010-03-06 14:45 ` Greg Stark <stark@mit.edu>
2010-03-06 16:16 ` Re: Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
1 sibling, 1 reply; 12+ messages in thread
From: Greg Stark @ 2010-03-06 14:45 UTC (permalink / raw)
To: Petru Ghita <petrutz@venaver.info>; +Cc: pgsql-sql
The immutable property had nothing to do with caching results. Postgres
never caches the results of functions. The immutable property is used top
determine if it's safe to use indexes or other plans that avoid evaluating
an expression repeatedly.
On 6 Mar 2010 02:45, "Petru Ghita" <petrutz@venaver.info> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Given f1(x) as IMMUTABLE and f2(x) as IMMUTABLE, and f3(f1,f2) as
IMMUTABLE, does the query planner cache the result of f3 and reuse it
or if you want to get a little more speed you better explicitly define
yourself f3 as IMMUTABLE?
I had an aggregate query like:
select id,
sum(p1*f1(a)/f2(b) as r1,
sum(p2*f1(a)/f2(b) as r2,
...
sum(pn*f1(a)/f2(b) as rn
...
group by id;
Where f1(x) and f2(x) were defined as IMMUTABLE.
By the experiments I ran looks like after defining a new function
f3(a,b):= f1(a)/f2(b) and rewriting the query as:
select id,
sum(p1*f3(a,b) as r1,
sum(p2*f3(a,b) as r2,
...
sum(pn*f3(a,b) as rn
...
group by id;
*Looks like* I got a little (5%) improvement in performance of the
query. Is there a way to find out if the function is re-evaluated each
time?
Is this the recommended way to proceed?
Thank you!
Petru Ghita
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkuRwYQACgkQt6IL6XzynQTHEgCffi2QMWkkvTIsuglsanvcUyRB
I+wAoKr22B7FJJVDCssGKGwB8zr4NjQG
=V/BS
-----END PGP SIGNATURE-----
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Does IMMUTABLE property propagate?
2010-03-06 02:44 Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 14:45 ` Re: Does IMMUTABLE property propagate? Greg Stark <stark@mit.edu>
@ 2010-03-06 16:16 ` Petru Ghita <petrutz@venaver.info>
2010-03-06 21:31 ` Re: Does IMMUTABLE property propagate? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Petru Ghita @ 2010-03-06 16:16 UTC (permalink / raw)
To: Greg Stark <stark@mit.edu>; +Cc: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Documentation states:
IMMUTABLE indicates that the function cannot modify the database and
always returns the same result when given the same argument values;
that is, it does not do database lookups or otherwise use information
not directly present in its argument list. If this option is given,
any call of the function with all-constant arguments can be
immediately replaced with the function value.
"..immediately replaced with the function value" doesn't mean that the
results of a previously evaluated function for the same parameters are
stored and reused?
The problem here is exactly about evaluating the expression several
times as the result is exactly the same for all the columns in the query.
Greg Stark wrote:
>
> The immutable property had nothing to do with caching results.
> Postgres never caches the results of functions. The immutable
> property is used top determine if it's safe to use indexes or other
> plans that avoid evaluating an expression repeatedly.
>
>> On 6 Mar 2010 02:45, "Petru Ghita" <petrutz@venaver.info
>> <mailto:petrutz@venaver.info>> wrote:
>>
> Given f1(x) as IMMUTABLE and f2(x) as IMMUTABLE, and f3(f1,f2) as
> IMMUTABLE, does the query planner cache the result of f3 and reuse
> it or if you want to get a little more speed you better explicitly
> define yourself f3 as IMMUTABLE?
>
> I had an aggregate query like:
>
> select id, sum(p1*f1(a)/f2(b) as r1, sum(p2*f1(a)/f2(b) as r2, ...
> sum(pn*f1(a)/f2(b) as rn
>
> ... group by id;
>
> Where f1(x) and f2(x) were defined as IMMUTABLE.
>
> By the experiments I ran looks like after defining a new function
> f3(a,b):= f1(a)/f2(b) and rewriting the query as:
>
> select id, sum(p1*f3(a,b) as r1, sum(p2*f3(a,b) as r2, ...
> sum(pn*f3(a,b) as rn
>
> ... group by id;
>
> *Looks like* I got a little (5%) improvement in performance of the
> query. Is there a way to find out if the function is re-evaluated
> each time? Is this the recommended way to proceed?
>
> Thank you!
>
> Petru Ghita
>>
>>
- --
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org
<mailto:pgsql-sql@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkuSf+0ACgkQt6IL6XzynQSREQCfQsZpH/cWzMTqVBv4/2D4X+Ib
uBYAniJwbox3bPA4dG/x4vmr0FY+icO9
=8Rvn
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Does IMMUTABLE property propagate?
2010-03-06 02:44 Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 14:45 ` Re: Does IMMUTABLE property propagate? Greg Stark <stark@mit.edu>
2010-03-06 16:16 ` Re: Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
@ 2010-03-06 21:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-25 06:16 ` Re: Does IMMUTABLE property propagate? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2010-03-06 21:31 UTC (permalink / raw)
To: Petru Ghita <petrutz@venaver.info>; +Cc: Greg Stark <stark@mit.edu>; pgsql-sql
Petru Ghita <petrutz@venaver.info> writes:
> "..immediately replaced with the function value" doesn't mean that the
> results of a previously evaluated function for the same parameters are
> stored and reused?
No, it means what it says: the function is executed once and replaced
with a constant representing the result value.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Does IMMUTABLE property propagate?
2010-03-06 02:44 Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 14:45 ` Re: Does IMMUTABLE property propagate? Greg Stark <stark@mit.edu>
2010-03-06 16:16 ` Re: Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 21:31 ` Re: Does IMMUTABLE property propagate? Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-03-25 06:16 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-25 07:27 ` Re: Does IMMUTABLE property propagate? Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Louis-David Mitterrand @ 2010-03-25 06:16 UTC (permalink / raw)
To: pgsql-sql
On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote:
> Petru Ghita <petrutz@venaver.info> writes:
> > "..immediately replaced with the function value" doesn't mean that the
> > results of a previously evaluated function for the same parameters are
> > stored and reused?
>
> No, it means what it says: the function is executed once and replaced
> with a constant representing the result value.
So for example a function like:
CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS text
AS $$
declare
outtext text;
begin
outtext = trim(regexp_replace(intext, E'\\s*Short( Break)?', '', 'i'));
return outtext;
end;
$$
LANGUAGE plpgsql;
could/should be declared immutable?
Thanks,
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Does IMMUTABLE property propagate?
2010-03-06 02:44 Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 14:45 ` Re: Does IMMUTABLE property propagate? Greg Stark <stark@mit.edu>
2010-03-06 16:16 ` Re: Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 21:31 ` Re: Does IMMUTABLE property propagate? Tom Lane <tgl@sss.pgh.pa.us>
2010-03-25 06:16 ` Re: Does IMMUTABLE property propagate? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-03-25 07:27 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-03-25 07:54 ` Re: Does IMMUTABLE property propagate? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 1 reply; 12+ messages in thread
From: Pavel Stehule @ 2010-03-25 07:27 UTC (permalink / raw)
To: pgsql-sql
2010/3/25 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>:
> On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote:
>> Petru Ghita <petrutz@venaver.info> writes:
>> > "..immediately replaced with the function value" doesn't mean that the
>> > results of a previously evaluated function for the same parameters are
>> > stored and reused?
>>
>> No, it means what it says: the function is executed once and replaced
>> with a constant representing the result value.
>
> So for example a function like:
>
>
> CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS text
> AS $$
> declare
> outtext text;
> begin
> outtext = trim(regexp_replace(intext, E'\\s*Short( Break)?', '', 'i'));
> return outtext;
> end;
> $$
> LANGUAGE plpgsql;
>
yes it should be declared as immutable. plpgsql function is black box
for executor, so you have to use some flag. language sql is different,
executor see inside, so there you can not do it.
Regards
Pavel Stehule
> could/should be declared immutable?
>
> Thanks,
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Does IMMUTABLE property propagate?
2010-03-06 02:44 Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 14:45 ` Re: Does IMMUTABLE property propagate? Greg Stark <stark@mit.edu>
2010-03-06 16:16 ` Re: Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 21:31 ` Re: Does IMMUTABLE property propagate? Tom Lane <tgl@sss.pgh.pa.us>
2010-03-25 06:16 ` Re: Does IMMUTABLE property propagate? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-25 07:27 ` Re: Does IMMUTABLE property propagate? Pavel Stehule <pavel.stehule@gmail.com>
@ 2010-03-25 07:54 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-25 14:35 ` Re: Does IMMUTABLE property propagate? Tom Lane <tgl@sss.pgh.pa.us>
2010-03-25 16:26 ` Re: Does IMMUTABLE property propagate? Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Louis-David Mitterrand @ 2010-03-25 07:54 UTC (permalink / raw)
To: pgsql-sql
On Thu, Mar 25, 2010 at 08:27:27AM +0100, Pavel Stehule wrote:
> 2010/3/25 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>:
> > On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote:
> >> Petru Ghita <petrutz@venaver.info> writes:
> >> > "..immediately replaced with the function value" doesn't mean that the
> >> > results of a previously evaluated function for the same parameters are
> >> > stored and reused?
> >>
> >> No, it means what it says: the function is executed once and replaced
> >> with a constant representing the result value.
> >
> > So for example a function like:
> >
> >
> > CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS text
> > AS $$
> > declare
> > outtext text;
> > begin
> > outtext = trim(regexp_replace(intext, E'\\s*Short( Break)?', '', 'i'));
> > return outtext;
> > end;
> > $$
> > LANGUAGE plpgsql;
> >
>
> yes it should be declared as immutable. plpgsql function is black box
> for executor, so you have to use some flag. language sql is different,
> executor see inside, so there you can not do it.
Hmm, that's interesting. So for simple functions (like my example) it is
better to write them in plain sql? And in that case no 'immutable' flag
is necessary?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Does IMMUTABLE property propagate?
2010-03-06 02:44 Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 14:45 ` Re: Does IMMUTABLE property propagate? Greg Stark <stark@mit.edu>
2010-03-06 16:16 ` Re: Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 21:31 ` Re: Does IMMUTABLE property propagate? Tom Lane <tgl@sss.pgh.pa.us>
2010-03-25 06:16 ` Re: Does IMMUTABLE property propagate? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-25 07:27 ` Re: Does IMMUTABLE property propagate? Pavel Stehule <pavel.stehule@gmail.com>
2010-03-25 07:54 ` Re: Does IMMUTABLE property propagate? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-03-25 14:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-25 16:05 ` Re: Does IMMUTABLE property propagate? Greg Stark <gsstark@mit.edu>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2010-03-25 14:35 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; +Cc: pgsql-sql
Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> writes:
> Hmm, that's interesting. So for simple functions (like my example) it is
> better to write them in plain sql? And in that case no 'immutable' flag
> is necessary?
If it's just a simple SQL expression, then yes write it as a SQL
function. The planner can "inline" those, eliminating the call overhead
that you'll pay with a plpgsql function.
When you're intending to have a SQL function be inlined, it's probably
best not to mark it as either IMMUTABLE or STRICT --- that doesn't buy
anything and it can complicate matters as to whether inlining is legal.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Does IMMUTABLE property propagate?
2010-03-06 02:44 Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 14:45 ` Re: Does IMMUTABLE property propagate? Greg Stark <stark@mit.edu>
2010-03-06 16:16 ` Re: Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 21:31 ` Re: Does IMMUTABLE property propagate? Tom Lane <tgl@sss.pgh.pa.us>
2010-03-25 06:16 ` Re: Does IMMUTABLE property propagate? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-25 07:27 ` Re: Does IMMUTABLE property propagate? Pavel Stehule <pavel.stehule@gmail.com>
2010-03-25 07:54 ` Re: Does IMMUTABLE property propagate? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-25 14:35 ` Re: Does IMMUTABLE property propagate? Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-03-25 16:05 ` Greg Stark <gsstark@mit.edu>
2010-03-25 16:55 ` Re: Does IMMUTABLE property propagate? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Greg Stark @ 2010-03-25 16:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; pgsql-sql
On Thu, Mar 25, 2010 at 2:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> When you're intending to have a SQL function be inlined, it's probably
> best not to mark it as either IMMUTABLE or STRICT --- that doesn't buy
> anything and it can complicate matters as to whether inlining is legal.
I'm confused, I thought it was volatile and strict that prevented inlining.
--
greg
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Does IMMUTABLE property propagate?
2010-03-06 02:44 Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 14:45 ` Re: Does IMMUTABLE property propagate? Greg Stark <stark@mit.edu>
2010-03-06 16:16 ` Re: Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 21:31 ` Re: Does IMMUTABLE property propagate? Tom Lane <tgl@sss.pgh.pa.us>
2010-03-25 06:16 ` Re: Does IMMUTABLE property propagate? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-25 07:27 ` Re: Does IMMUTABLE property propagate? Pavel Stehule <pavel.stehule@gmail.com>
2010-03-25 07:54 ` Re: Does IMMUTABLE property propagate? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-25 14:35 ` Re: Does IMMUTABLE property propagate? Tom Lane <tgl@sss.pgh.pa.us>
2010-03-25 16:05 ` Re: Does IMMUTABLE property propagate? Greg Stark <gsstark@mit.edu>
@ 2010-03-25 16:55 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2010-03-25 16:55 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; pgsql-sql
Greg Stark <gsstark@mit.edu> writes:
> On Thu, Mar 25, 2010 at 2:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> When you're intending to have a SQL function be inlined, it's probably
>> best not to mark it as either IMMUTABLE or STRICT --- that doesn't buy
>> anything and it can complicate matters as to whether inlining is legal.
> I'm confused, I thought it was volatile and strict that prevented inlining.
No: we won't inline if the contained expression is more volatile than
what the function is marked as being. This is a hack that prevents the
inlining logic from defeating kluges that people might be using in
certain applications, namely putting "immutable" or "stable" wrapper
functions around functions that are more volatile than that in the eyes
of the system. You can do that to force the planner to treat things as
immutable/stable in certain contexts; but of course the trick wouldn't
work if the inliner opens up the function and exposes its true contents.
But in the other direction, exposing a definition that is less volatile
than the function's declaration cannot break anything.
Similarly, a STRICT marking prevents inlining unless the planner can
prove that the contained expression would act the same as the function
declaration w.r.t. returning null for any null input; and in all but
the simplest cases it can't prove that.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Does IMMUTABLE property propagate?
2010-03-06 02:44 Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 14:45 ` Re: Does IMMUTABLE property propagate? Greg Stark <stark@mit.edu>
2010-03-06 16:16 ` Re: Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 21:31 ` Re: Does IMMUTABLE property propagate? Tom Lane <tgl@sss.pgh.pa.us>
2010-03-25 06:16 ` Re: Does IMMUTABLE property propagate? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-25 07:27 ` Re: Does IMMUTABLE property propagate? Pavel Stehule <pavel.stehule@gmail.com>
2010-03-25 07:54 ` Re: Does IMMUTABLE property propagate? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-03-25 16:26 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Pavel Stehule @ 2010-03-25 16:26 UTC (permalink / raw)
To: pgsql-sql
2010/3/25 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>:
> On Thu, Mar 25, 2010 at 08:27:27AM +0100, Pavel Stehule wrote:
>> 2010/3/25 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>:
>> > On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote:
>> >> Petru Ghita <petrutz@venaver.info> writes:
>> >> > "..immediately replaced with the function value" doesn't mean that the
>> >> > results of a previously evaluated function for the same parameters are
>> >> > stored and reused?
>> >>
>> >> No, it means what it says: the function is executed once and replaced
>> >> with a constant representing the result value.
>> >
>> > So for example a function like:
>> >
>> >
>> > CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS text
>> > AS $$
>> > declare
>> > outtext text;
>> > begin
>> > outtext = trim(regexp_replace(intext, E'\\s*Short( Break)?', '', 'i'));
>> > return outtext;
>> > end;
>> > $$
>> > LANGUAGE plpgsql;
>> >
>>
>> yes it should be declared as immutable. plpgsql function is black box
>> for executor, so you have to use some flag. language sql is different,
>> executor see inside, so there you can not do it.
>
> Hmm, that's interesting. So for simple functions (like my example) it is
> better to write them in plain sql? And in that case no 'immutable' flag
> is necessary?
>
sure
Pavel
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2010-03-25 16:55 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-06 02:44 Does IMMUTABLE property propagate? Petru Ghita <petrutz@venaver.info>
2010-03-06 08:02 ` Jasen Betts <jasen@xnet.co.nz>
2010-03-06 14:45 ` Greg Stark <stark@mit.edu>
2010-03-06 16:16 ` Petru Ghita <petrutz@venaver.info>
2010-03-06 21:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-25 06:16 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-25 07:27 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-03-25 07:54 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-25 14:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-25 16:05 ` Greg Stark <gsstark@mit.edu>
2010-03-25 16:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-25 16:26 ` Pavel Stehule <pavel.stehule@gmail.com>
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