Costs

It is possible to provide hints to the executor about the cost of a function. PostgreSQL query optimizer is a cost-based optimizer, which means it will carefully evaluate the cost of each operation and choose the execution path with the lowest cost. Explaining the cost system and the optimizer is out of the scope of this book, but it is important to know that functions can have a cost estimation.

There are two properties that provide this cost estimation:

• COST is a positive number indicating how many CPU operations the function will use
• ROWS is an estimation of the rows returned from a RETURNS SETOF (or RETURNS TABLE) function

The COST property is the more straightforward one: it expresses how many CPU operations the whole execution of the function will use. CPU operations have a cost of cpu_operator_cost each, which is, by default, 1/40 the cost of performing a sequential read from disk. If not specified, the COST for a PL/pgSQL or any non-C language is set to 100.

If the function returns a result set, COST is intended to be the cost of a single tuple from the function result set. Moreover, it is possible to provide the optimizer with a hint about how many tuples a function will return by specifying a positive ROWS number.

As a simple example, a function that performs a simple limited tag query can be declared to have a ROWS property set to the limit itself:

testdb=> CREATE FUNCTION get_ten_tags()
RETURNS SETOF tag AS $code$
 BEGIN
 RETURN QUERY SELECT *
 FROM tags
 LIMIT 10;
 END $code$ LANGUAGE plpgsql
ROWS 10; -- it is clear this function cannot return more rows

Tuning cost properties can quickly become a complicated task, so it is recommended to add these properties only when they are really needed and to check the impact on query execution carefully.