On Mon, Oct 11, 2010 at 7:10 PM, Damon Snyder <damon@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance