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


RETURNS setof record AS
-- code
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 ?

1 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.

  • I have written a function which do recursion and will return one row. In this case, can I give cost 1 and row 1?
    – RBB
    Jan 4, 2016 at 7:48
  • 1
    ROW 1 yes. COST 1 almost certainly wrong, that's about the cost for a trivial C-language function. Apr 2, 2021 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.