pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedStored procedure declared as VOLATILE => no good optimization is done
7+ messages / 5 participants
[nested] [flat]
* Stored procedure declared as VOLATILE => no good optimization is done
@ 2010-10-11 23:10 Damon Snyder <damon@huddler-inc.com>
0 siblings, 1 reply; 7+ messages in thread
From: Damon Snyder @ 2010-10-11 23:10 UTC (permalink / raw)
To: pgsql-performance
Hello,
I have heard it said that if a stored procedure is declared as VOLATILE,
then no good optimizations can be done on queries within the stored
procedure or queries that use the stored procedure (say as the column in a
view). I have seen this in practice, recommended on the irc channel, and in
the archives (
http://archives.postgresql.org/pgsql-performance/2008-01/msg00283.php). Can
someone help me understand or point me to some documentation explaining why
this is so?
Any insights would be appreciated. I'm new to pgsql and would like to know a
little more about what is going on under the hood.
Thanks,
Damon
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stored procedure declared as VOLATILE => no good optimization is done
@ 2010-10-15 19:24 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 7+ messages in thread
From: Kevin Grittner @ 2010-10-15 19:24 UTC (permalink / raw)
To: damon@huddler-inc.com; pgsql-performance
Damon Snyder wrote:
> I have heard it said that if a stored procedure is declared as
> VOLATILE, then no good optimizations can be done on queries within
> the stored procedure or queries that use the stored procedure (say
> as the column in a view). I have seen this in practice, recommended
> on the irc channel, and in the archives (
> http://archives.postgresql.org/pgsql-performance/2008-01/msg00283.php
> ). Can
> someone help me understand or point me to some documentation
> explaining why this is so?
Here's the documentation:
http://www.postgresql.org/docs/current/interactive/sql-createfunction.html
http://www.postgresql.org/docs/current/interactive/xfunc-volatility.html
-Kevin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stored procedure declared as VOLATILE => no good optimization is done
@ 2010-10-15 21:06 Merlin Moncure <mmoncure@gmail.com>
parent: Damon Snyder <damon@huddler-inc.com>
0 siblings, 1 reply; 7+ messages in thread
From: Merlin Moncure @ 2010-10-15 21:06 UTC (permalink / raw)
To: Damon Snyder <damon@huddler-inc.com>; +Cc: pgsql-performance
On Mon, Oct 11, 2010 at 7:10 PM, Damon Snyder <damon@huddler-inc.com> wrote:
> Hello,
> I have heard it said that if a stored procedure is declared as VOLATILE,
> then no good optimizations can be done on queries within the stored
> procedure or queries that use the stored procedure (say as the column in a
> view). I have seen this in practice, recommended on the irc channel, and in
> the archives
> (http://archives.postgresql.org/pgsql-performance/2008-01/msg00283.php). Can
> someone help me understand or point me to some documentation explaining why
> this is so?
> Any insights would be appreciated. I'm new to pgsql and would like to know a
> little more about what is going on under the hood.
> Thanks,
> Damon
The theory behind 'volatile' is pretty simple -- each execution of the
function, regardless of the inputs, can be expected to produce a
completely independent result, or modifies the datbase. In the case
of immutable, which is on the other end, particular set of inputs will
produce one and only result, and doesn't modify anything.
In the immutable case, the planner can shuffle the function call
around in the query, calling it less, simplifying joins, etc. There
are lots of theoretical optimizations that can be done since the
inputs (principally table column values and literal values) can be
assumed static for the duration of the query.
'stable' is almost like immutable, but is only guaranteed static for
the duration of the query. most functions that read from but don't
write to the database will fit in this category. Most optimizations
still apply here, but stable functions can't be used in indexes and
can't be executed and saved off in plan time where it might be helpful
(prepared statements and pl/pgsql plans).
broadly speaking:
*) function generates same output from inputs regardless of what's
going on in the database, and has no side effects: IMMUTABLE
*) function reads (only) from tables, or is an immutable function in
most senses but influenced from the GUC (or any other out of scope
thing): STABLE
*) all other cases: VOLATILE (which is btw the default)
merlin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stored procedure declared as VOLATILE => no good optimization is done
@ 2010-10-16 01:47 Tatsuo Ishii <ishii@postgresql.org>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tatsuo Ishii @ 2010-10-16 01:47 UTC (permalink / raw)
To: mmoncure@gmail.com; +Cc: damon@huddler-inc.com; pgsql-performance
> broadly speaking:
> *) function generates same output from inputs regardless of what's
> going on in the database, and has no side effects: IMMUTABLE
So can I say "if a function is marked IMMUTABLE, then it should never
modify database"? Is there any counter example?
> *) function reads (only) from tables, or is an immutable function in
> most senses but influenced from the GUC (or any other out of scope
> thing): STABLE
It seems if above is correct, I can say STABLE functions should never
modify databases as well.
> *) all other cases: VOLATILE (which is btw the default)
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stored procedure declared as VOLATILE => no good optimization is done
@ 2010-10-16 02:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2010-10-16 02:31 UTC (permalink / raw)
To: Tatsuo Ishii <ishii@postgresql.org>; +Cc: mmoncure@gmail.com; damon@huddler-inc.com; pgsql-performance
Tatsuo Ishii <ishii@postgresql.org> writes:
> So can I say "if a function is marked IMMUTABLE, then it should never
> modify database"? Is there any counter example?
> It seems if above is correct, I can say STABLE functions should never
> modify databases as well.
Both of those things are explicitly stated here:
http://developer.postgresql.org/pgdocs/postgres/xfunc-volatility.html
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stored procedure declared as VOLATILE => no good optimization is done
@ 2010-10-16 19:54 Merlin Moncure <mmoncure@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Merlin Moncure @ 2010-10-16 19:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tatsuo Ishii <ishii@postgresql.org>; damon@huddler-inc.com; pgsql-performance
On Fri, Oct 15, 2010 at 10:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Tatsuo Ishii <ishii@postgresql.org> writes:
>> So can I say "if a function is marked IMMUTABLE, then it should never
>> modify database"? Is there any counter example?
>> It seems if above is correct, I can say STABLE functions should never
>> modify databases as well.
>
> Both of those things are explicitly stated here:
> http://developer.postgresql.org/pgdocs/postgres/xfunc-volatility.html
Ok, being pedantic here, but:
I think more interesting is *why* the 'immutable shall not modify the
database' requirement is there. IOW, suppose you ignore the warnings
on the docs and force immutability on a function that writes (via the
function loophole) to the database, why exactly is this a bad idea?
The reasoning given in the documentation explains a problematic
symptom of doing so but gives little technical reasoning what it
should never be done.
One reason why writing to the database breaks immutability is that
writing to the database depends on resources that can change after the
fact: function immutability also pertains to failure -- if a function
errors (or not) with a set of inputs, it should always do so. If you
write to a table, you could violate a constraint from one call to the
next, or the table may not even be there at all...
Writing to the database means you are influencing other systems, and
via constraints they are influencing you, so it makes it wrong by
definition. That said, if you were writing to, say, a table with no
meaningful constraints this actually wouldn't be so bad as long as you
can also deal with the other big issue with immutability, namely that
there is not 1:1 correspondence between when the function is logically
evaluated and when it is executed. This more or less eliminates
logging (at least outside of debugging purposes), the only thing I can
figure you can usefully do on a table w/no enforceable constraints.
Also, a big use case for immutable function is to allow use in
indexing, and it would be just crazy (again, debugging purposes aside)
to write to a table on index evaluation.
merlin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stored procedure declared as VOLATILE => no good optimization is done
@ 2010-10-25 23:27 Damon Snyder <damon@huddler-inc.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Damon Snyder @ 2010-10-25 23:27 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Tatsuo Ishii <ishii@postgresql.org>; +Cc: pgsql-performance
Thank you for all of the responses. This was really helpful.
Damon
On Sat, Oct 16, 2010 at 12:54 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Fri, Oct 15, 2010 at 10:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Tatsuo Ishii <ishii@postgresql.org> writes:
> >> So can I say "if a function is marked IMMUTABLE, then it should never
> >> modify database"? Is there any counter example?
> >> It seems if above is correct, I can say STABLE functions should never
> >> modify databases as well.
> >
> > Both of those things are explicitly stated here:
> > http://developer.postgresql.org/pgdocs/postgres/xfunc-volatility.html
>
> Ok, being pedantic here, but:
>
> I think more interesting is *why* the 'immutable shall not modify the
> database' requirement is there. IOW, suppose you ignore the warnings
> on the docs and force immutability on a function that writes (via the
> function loophole) to the database, why exactly is this a bad idea?
> The reasoning given in the documentation explains a problematic
> symptom of doing so but gives little technical reasoning what it
> should never be done.
>
> One reason why writing to the database breaks immutability is that
> writing to the database depends on resources that can change after the
> fact: function immutability also pertains to failure -- if a function
> errors (or not) with a set of inputs, it should always do so. If you
> write to a table, you could violate a constraint from one call to the
> next, or the table may not even be there at all...
>
> Writing to the database means you are influencing other systems, and
> via constraints they are influencing you, so it makes it wrong by
> definition. That said, if you were writing to, say, a table with no
> meaningful constraints this actually wouldn't be so bad as long as you
> can also deal with the other big issue with immutability, namely that
> there is not 1:1 correspondence between when the function is logically
> evaluated and when it is executed. This more or less eliminates
> logging (at least outside of debugging purposes), the only thing I can
> figure you can usefully do on a table w/no enforceable constraints.
> Also, a big use case for immutable function is to allow use in
> indexing, and it would be just crazy (again, debugging purposes aside)
> to write to a table on index evaluation.
>
> merlin
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-10-25 23:27 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-11 23:10 Stored procedure declared as VOLATILE => no good optimization is done Damon Snyder <damon@huddler-inc.com>
2010-10-15 21:06 ` Merlin Moncure <mmoncure@gmail.com>
2010-10-16 01:47 ` Tatsuo Ishii <ishii@postgresql.org>
2010-10-16 02:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-10-16 19:54 ` Merlin Moncure <mmoncure@gmail.com>
2010-10-25 23:27 ` Damon Snyder <damon@huddler-inc.com>
2010-10-15 19:24 Re: Stored procedure declared as VOLATILE => no good optimization is done Kevin Grittner <Kevin.Grittner@wicourts.gov>
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