PostgreSQL: Why calling function (NUMERIC -> NUMERIC) in SELECT is too expensive?-CodePudding

I have a query with a long select part with many cases when statement and I tried to put them to function for clarity, but the query start running longer than before.

So, for testing, I create one simple table

CREATE TABLE t1 AS
select random()::numeric from generate_series(1, 5000000, 1)

and one very simple function

create funtion f1 (i NUMERIC)
returns NUMERIC
LANGUAGE plpgsql
AS
$body$
begin
return i 1
end
$body$

and running two queries:

1: SELECT random, random 1 from T1
2: SELECT random, f1(random) from T1

and I don't know why the first is running two times faster than the second, so please help. Sorry for my English :(

CodePudding user response：

That is because the function f1 has to be called for every row, and calling a PL/pgSQL function is quite expensive.

If you define it as an SQL function, it could be inlined, which would be much cheaper:

FREATE FUNCTION f1(i NUMERIC) RETURNS numeric
   LANGUAGE sql AS
'SELECT i   1';