Mastering PostgreSQL 9.6

Assigning costs to functions

From the optimizer point of view, a function is basically just like an operator. PostgreSQL will also treat the costs the same way as if it was a standard operator. The problem is just this: adding two numbers is usually cheaper than intersecting coastlines using some PostGIS-provided function. The thing is that the optimizer does not know whether a function is cheap or expensive. Fortunately, we can tell the optimizer to make functions cheaper or more expensive:

test=# h CREATE FUNCTION Command:     CREATE FUNCTION Description: define a new function Syntax: CREATE [ OR REPLACE ] FUNCTION ...     | COST execution_cost     | ROWS result_rows   ... 

The COST parameter indicates how much more expensive than a standard operator ...