Function planer related parameters helps in giving the planner information about the function execution cost, this helps the planner to generate a good execution plans. The following three parameters are used by the planner to determine the cost of executing the function, the number of rows that are expected to be returned, and whether the function pushes down when evaluating predicates. These parameters are:

• Leakproof: Leakproof means that the function has no side effects. It does not reveal any information about its argument. For example, it does not throw error messages about its argument. This parameter affects views with the security_barrier parameter.
• Cost: Declares the execution cost per row; the default value for the C language function is 1, and for PL/pgSQL it is 100. The cost is used by the planner to determine the best execution plan.
• Rows: The estimated number of rows returned by the function if the function is set-returning. The default value is 1000.

To understand the effect of the rows configuration parameter, let's consider the following example:

CREATE OR REPLACE FUNCTION a() RETURNS SETOF INTEGER AS $$
  SELECT 1;
$$ LANGUAGE SQL;

Now, let's execute the following query:

car_portal=> EXPLAIN SELECT * FROM a() CROSS JOIN (Values(1),(2),(3)) as foo;
                               QUERY PLAN 
-------------------------------------------------------------------------
 Nested Loop (cost=0.25..47.80 rows=3000 width=8)
   -> Function Scan on a (cost=0.25..10.25 rows=1000 width=4)
   -> Materialize (cost=0.00..0.05 rows=3 width=4)
         -> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=4)
(4 rows)

The SQL function return type is SETOF INTEGER, which means that the planner expected more than one row to be returned from the function. Since the ROWS parameter is not specified, the planner uses the default value, which is 1000. Finally, due to CROSS JOIN, the total estimated number of rows is 3000, which is calculated as 3 * 1000.

In the preceding example, an incorrect estimation is not critical. However, in a real-life example, where one might have several joins, the error of rows estimation will be propagated and amplified, leading to bad execution plans.

The COST function parameter determines when the function will be executed, such as:

• It determines the function execution order
• It determines whether the function call can be pushed down

The following example shows how the execution order for functions is affected by the function COST. Let's assume we have two functions, as follows:

CREATE OR REPLACE FUNCTION slow_function (anyelement) RETURNS BOOLEAN AS $$
BEGIN
  RAISE NOTICE 'Slow function %', $1;
  RETURN TRUE;
END; $$ LANGUAGE PLPGSQL COST 10000;

CREATE OR REPLACE FUNCTION fast_function (anyelement) RETURNS BOOLEAN AS $$
BEGIN
  RAISE NOTICE 'Fast function %', $1;
  RETURN TRUE;
END; $$ LANGUAGE PLPGSQL COST 0.0001;

The fast_function and the slow_function are identical except for the cost parameter:

car_portal=> EXPLAIN SELECT * FROM pg_language WHERE fast_function(lanname) AND slow_function(lanname) AND lanname ILIKE '%sql%';
 QUERY PLAN 
-----------------------------------------------------------------------------------------------
 Seq Scan on pg_language (cost=0.00..101.05 rows=1 width=114)
 Filter: (fast_function(lanname) AND (lanname ~~* '%sql%'::text) AND slow_function(lanname))
(2 rows)
car_portal=# EXPLAIN SELECT * FROM pg_language WHERE slow_function(lanname) AND fast_function(lanname) AND lanname ILIKE '%sql%';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on pg_language (cost=0.00..101.05 rows=1 width=114)
   Filter: (fast_function(lanname) AND (lanname ~~* '%sql%'::text) AND slow_function(lanname))
(2 rows)

The preceding two SQL statements are identical, but the predicates are shuffled. Both statements give the same execution plan. Notice how the predicates are arranged in the filter execution plane node. The fast_function is evaluated first, followed by the ILIKE operator, and finally, the slow_function is pushed. When executing one of the preceding statements, one will get the following result:

car_portal=> SELECT lanname FROM pg_language WHERE lanname ILIKE '%sql%' AND slow_function(lanname)AND fast_function(lanname);
NOTICE: Fast function internal
NOTICE: Fast function c
NOTICE: Fast function sql
NOTICE: Slow function sql
NOTICE: Fast function plpgsql
NOTICE: Slow function plpgsql
 lanname 
---------
 sql
 plpgsql
(2 rows)

Notice that fast_function was executed four times, and slow_function was executed only twice. This behavior is known as short circuit evaluation. slow_function is executed only when fast_function and the ILIKE operator have returned true.

As discussed in Chapter 04, PostgreSQL Advanced Building Blocks, views can be used to implement authorization, and they can be used to hide data from some users. The function cost parameter can be exploited in earlier versions of postgres to crack views; however, this has been improved by the introduction of the LEAKPROOF and SECURITY_BARRIER flags.

To be able to exploit the function cost parameter to get data from a view, several conditions should be met, some of which are as follows:

• The function cost should be very low.
• The function should be marked as LEAKPROOF. Note that only superusers are allowed to mark functions as LEAKPROOF.
• The view security barrier flag should not be set.
• The function should be executed and not ignored due to short-circuit evaluation.
• Meeting all these conditions is very difficult.

The following code shows a hypothetical example of exploiting views. First, let's create a view, alter the function fast_function, and set it as LEAKPROOF.

CREATE OR REPLACE VIEW pg_sql_pl AS SELECT lanname FROM pg_language WHERE lanname ILIKE '%sql%';
ALTER FUNCTION fast_function(anyelement) LEAKPROOF;

To exploit the function, let's execute the following query:

car_portal=# SELECT * FROM pg_sql_pl WHERE fast_function(lanname);
NOTICE: Fast function internal
NOTICE: Fast function c
NOTICE: Fast function sql
NOTICE: Fast function plpgsql
 lanname 
---------
 sql
 plpgsql
(2 rows)

In the preceding example, the view itself should not show c and internal. By exploiting the function cost, the function was executed before executing the filter lanname ILIKE '%sql%', exposing information that will never be shown by the view.

Since only superusers are allowed to mark a function as LEAKPROOF, exploiting the function cost in newer versions of postgres is not possible.