While looking at several examples of
pl/pgsql, I have seen many – but not all – using
CREATE OR REPLACE FUNCTION my_function() RETURNS setof record AS $BODY$ -- code $BODY$ LANGUAGE plpgsql VOLATILE COST 100;
Searching for more information about
volatile cost I have discovered (at first sight) that roughly at least 90% of web examples are using
volatile cost 100, and sometimes somethimes
volatile cost 1. (for
rows it’s 1000).
As I have understood, this indication helps the query plan optimizer to decide how to set priorities in short-circuit boolean operations.
Is it premature optimization if I start to give an estimate
rows for each of my stored procedures ? Should I only do it when I want to optimize certain query ? Is it an art for choosing the good value of cost ?
I know about the command
explain, which I have have not learnt yet. Is this command helpful for estimating
VOLATILE is completely independent from
The first defines function volatility and
VOLATILE is the default, so it can be omitted. Alternatives are
The latter is only valid in combination with a
COST parameter, like
COST 500. The default is
COST 100 which, again, can be omitted. It declares the cost per row of the result, which is used by the query planner to find the cheapest plan. If you don’t know what you are doing exactly, just leave it at the default.
Read the very fine manual, starting at the chapter