Mastering PostgreSQL 10

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 FUNCTIONDescription: Define a new functionSyntax:CREATE [ OR REPLACE ] FUNCTION 
... 
| COST  execution_cost 
| ROWS  result_rows 
... 

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