Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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 more in the very fine manual, starting at the chapter CREATE FUNCTION.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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