I am running in little performance issue in PostgreSQL (V16) and calls between procedures. If I take only this code
CREATE OR REPLACE PROCEDURE perf_test_3() AS
$body$
DECLARE
begin
end;
$body$ LANGUAGE PLPGSQL;
CREATE OR REPLACE PROCEDURE perf_test_2() AS
$body$
DECLARE
begin
CALL perf_test_3();
end;
$body$ LANGUAGE PLPGSQL;
CREATE OR REPLACE PROCEDURE perf_test_1() AS
$body$
DECLARE
begin
CALL perf_test_2();
end;
$body$ LANGUAGE PLPGSQL;
do $$
DECLARE
v_ii int;
v_start timestamp(3);
v_end timestamp(3);
BEGIN
v_start := clock_timestamp();
for v_ii in 1..500000 LOOP
call perf_test_1();
END LOOP;
v_end := clock_timestamp();
RAISE NOTICE 'start time %', v_start;
RAISE NOTICE 'end time %', v_end;
RAISE NOTICE 'run time %', v_end-v_start;
END;
$$;
This loop took about 2sec doing "nothing"
NOTICE: start time 2024-02-20 16:37:15.76
NOTICE: end time 2024-02-20 16:37:17.547
NOTICE: run time 00:00:01.787
If I have tons of records and multiple different procedures for calculations, the total run is compared to Oracle PL/SQL much too high (4min in Oracle, 1h in PostgreSQL). It is not the processing within each of the procedures, those are fast (statement, calculations, etc.), it is more the call between of the procedures, which makes it slow.
Based on the post Hot to optimize, with functions goes better (save 1sec), but from my perspective even to high. Is there any other option/information, how to optimize the pl/sql engine.