Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure

Question

While looking at several examples of pl/python and pl/pgsql, I have seen many - but not all - using volatile cost.

ie:

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 cost or 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 cost ?

Answer 1

VOLATILE is completely independent from COST.

The first defines function volatility and VOLATILE is the default, so it can be omitted. Alternatives are STABLE and IMMUTABLE.

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 CREATE FUNCTION.