PostgreSQL Procedure Performance

47 Views Asked by At

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.

0

There are 0 best solutions below