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?

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

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

Comments

[Milen A. Radev]

Start here and come back if there's anything unclear.

[Shelvacu]

"COST is not linear" isn't it? The relevant docs say the cost is "A positive number giving the estimated execution cost for the function, in units of cpu_operator_cost" and the docs on costs say nothing about a logarithmic scale, although they do say "Only their relative values matter, hence scaling them all up or down by the same factor will result in no change in the planner's choices."

[Pavel Stehule]

@Shelvacu - I think so linear/non linear is not exact terminology. Total cost is calculated as sum of partial costs, and these costs are calculated by some formulas usually some coefficient * some constants * estimated values. But these formulas are too synthetic - nobody expects so all functions with cost 1 has same speed, or so function with cost 10 are ten times slower. These numbers are synthetic, nothing more. Nobody expects exact measured relation between costs and speed. Just functions with higher costs should be slower. How much, nobody knows.