We are experiencing weird performance issue of one simple insert statement taking several minutes to insert data. The application calls insert statement via stored procedure show mentioned below.
The select query in the insert returns about 499 rows. However, this insert statement when executed from application user i.e. schema1_u takes close to 8 minutes. When the same insert statement gets executed as postgres user it takes less than 280 ms. Both the executions use the same execution plan with only difference that when schema1_u executes the SQL, we observe "Trigger for constraint fk_con_tablea: time=426499.314 calls=499" taking more time. Both the parent and child tables are not big in size. There is no table bloat etc for both of these tables. Below are the details. Is there any way we can identify why as postgres user the insert statement works fine and why not with application user schema1_u?
Stored Procedure: ====================
CREATE OR REPLACE FUNCTION schema1.ins_staging_fn(parfileid double precision, parcreatedby text) RETURNS void LANGUAGE plpgsql AS $function$ BEGIN insert into table_a ( ROWVERSION, CREATED, ISDELETED, ISIGNORED, IMPORTEDACCOUNTCODE, IMPORTEDUNITCODE, BEGINNINGBALANCE, ENDINGBALANCE, CREATEDBY, FILEID ) select to_timestamp(To_char(clock_timestamp(),'DD-MON-YY HH.MI.SS.FF4 AM'),'DD-MON-YY HH.MI.SS.FF4 AM'), to_timestamp(To_char(clock_timestamp() at time zone 'utc', 'DD-MON-YY HH.MI.SS.MS AM'),'DD-MON-YY HH.MI.SS.FF4 AM'), false, false, IMPORTEDACCOUNTCODE, IMPORTEDUNITCODE, BEGINNINGBALANCE, ENDINGBALANCE, parCreatedBy, FILEID from STAGING_table_a where FILEID = parFileId;