What does "LANGUAGE plpgsql VOLATILE COST 100" mean in functions

Question

I am new to Postgres triggers. I saw an example of triggers on www.postgresql.org and I didn't understand what is LANGUAGE plpgsql VOLATILE COST 100; at the end of the trigger-function.

What is the use of this line?

I saw a question related to this what does “LANGUAGE 'plpgsql' VOLATILE” mean? but it is only about volatile, what is cost 100 and language in this line?

Answer 1

LANGUAGE plpgsql VOLATILE COST 100;

LANGUAGE:-programming language used for creating the stored procedure in PostgreSQL. Here it is plpgsql.

(before going to volatile and cost there is something you need to know first...'query optimizer' for which we are giving these informations. The query optimizer used to determine the most efficient way to execute a given query.)

VOLATILE:-In PostgreSQL Every function has a volatility classification and volatile is one of volatility classification. A VOLATILE function can do anything, including modifying the database. It is default, so it can be omitted.

COST 100:- COST is completely independent from VOLATILE. It declares the cost per row of the result, which is used by the query planner to find the cheapest plan. The default is COST 100 which also can be omitted. Its better to leave it at the default.

In these statement we are giving information's about function to query optimizer. Here it... what language are using and what is the volatility of the function and what is the cost per row of the result.

Answer 2

You can use more programming languages for stored procedures programming in Postgres. So mandatory attribut LANGUAGE specifies used language - plpgsql, sql, plpython, plperl, ...

These functions are black box for optimizer - optimizer doesn't know what is inside and how expensive this code is. Optimizer can choose a way how a SQL query will be calculated and it can prefer or minimize a evaluation of some functions. Functions with low cost are preferred and function with high cost is penalized. COST is +/- value how function is expensive (how function is fast or slow). Almost all built-in functions have COST 1, pgAdmin uses a COST 100 for custom function - it shows an expectation so plpgsql functions will be slower than built-in functions. COST is not linear - so it doesn't mean so plpgsql function is 100x slower than built-in function.