Em dom., 4 de jun. de 2023 às 05:35, Satalabaha Postgres < satalabaha.postg...@gmail.com> escreveu:
> Hi Listers, > > DB : postgres 14. > > 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; > > END; > $function$ > ; > Can you show what type is FILEID? Can there be type mismatch? regards, Ranier Vilela
