How to estimate execution cost of function in pgsql?-CodePudding

I am trying to define custom function and I wanted to find how can I calculate estimated cost of that function

https://www.postgresql.org/docs/current/sql-createfunction.html

I tried giving different values of cost function but unable to find to find how to estimate that cost.

CodePudding user response：

The unit of costs in PostgreSQL is not defined, but 1 is the default value of seq_page_cost, the cost of reading one 8kB page during a sequential scan. So you could measure the average duration of that operation and assign the cost of the function in comparison with that.

CodePudding user response：

You can use EXPLAIN to see the cost of CPU from each query on the computer its being executed.

    CREATE OR REPLACE FUNCTION a() RETURNS SET OF INTEGER AS $$
 SELECT 1;
$$
LANGUAGE SQL;
EXPLAIN SELECT * FROM a() CROSS JOIN (Values(1),(2),(3)) as foo;

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)

If two functions with COST 0.0001 AND 10000 get executed on the same time as the predicate of a SELECT statement the query planner will execute first the function of cost 0.0001 and only later the 10000 cost condition as you can see in this example below.

EXPLAIN SELECT * FROM pg_language WHERE lanname ILIKE '%sql%' AND slow_
function(lanname)AND fast_function(lanname);
 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)

CodePudding user response：

If I cared enough to bother, I would do it experimentally.

For example, if your function takes double precision, you could compare:

explain analyze select sqrt(x::double precision) from generate_series(1,1000000) f(x);

explain analyze select your_func(x::double precision) from generate_series(1,1000000) f(x);

And then find the cost setting that makes the ratio of the cost estimates about match the ratio of the actual times.

You could try to subtract the baseline costs of the generate_series and the cast, but if the added time of your function is so small that it warrants such precision, then it is probably small enough to just make the cost 1 and not worry about it.

CodePudding user response：

In PostgreSQL, you can define the estimated cost of a custom function using the COST parameter in the CREATE FUNCTION statement. The cost represents the estimated number of disk page fetches that the function will perform. This value is used by the PostgreSQL query planner to determine the most efficient execution plan for a query.

For example, if you have a custom function get_customer_orders(customer_id int) that retrieves all orders for a given customer, you can define the estimated cost of the function as follows:

CREATE FUNCTION get_customer_orders(customer_id int)
RETURNS SETOF orders
AS $$
  SELECT * FROM orders WHERE customer_id = $1;
$$ LANGUAGE SQL
COST 100;

In this example, the COST parameter is set to 100, which means that the query planner estimates that the function will perform 100 disk page fetches. You can adjust this value based on your own observations and tests of the function's performance.

It's important to note that the COST parameter is only an estimate, and it may not always accurately reflect the actual cost of the function. The query planner uses this value as a starting point, but it can adjust the execution plan based on other factors, such as the size and distribution of data in the tables that the function accesses.