Re: Stored procedure declared as VOLATILE => no good optimization is done

On Mon, Oct 11, 2010 at 7:10 PM, Damon Snyder <damon(at)huddler-inc(dot)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