On Sun, 4 Jun 2023 at 19:46, Ranier Vilela <ranier...@gmail.com> wrote:
> 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 > Thanks Ranier. Please find the below. \d+ schema1.table_a Table "schema1.table_a" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------------------+--------------------------------+-----------+----------+---------+----------+--------------+------------- id | numeric(20,0) | | not null | | main | | rowversion | timestamp(4) without time zone | | not null | | plain | | created | timestamp(4) without time zone | | not null | | plain | | isdeleted | boolean | | not null | | plain | | lastupdated | timestamp(4) without time zone | | | | plain | | isignored | boolean | | not null | | plain | | importedaccountcode | character varying(255) | | | | extended | | importedunitcode | character varying(255) | | | | extended | | beginningbalance | numeric(19,5) | | | | main | | endingbalance | numeric(19,5) | | | | main | | createdbyid | numeric(20,0) | | | | main | | updatedbyid | numeric(20,0) | | | | main | | fileid | numeric(20,0) | | not null | | main | | previousid | numeric(20,0) | | | | main | | createdby | character varying(255) | | | | extended | | lastupdatedby | character varying(255) | | | | extended | | \d+ schema1.table_b Table "schema1.table_b" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------------------+--------------------------------+-----------+----------+---------+----------+--------------+------------- id | numeric(20,0) | | not null | | main | | rowversion | timestamp(4) without time zone | | not null | | plain | | created | timestamp(4) without time zone | | not null | | plain | | isdeleted | boolean | | not null | | plain | | lastupdated | timestamp(4) without time zone | | | | plain | | version | numeric(10,0) | | not null | | main | | isactive | boolean | | not null | | plain | | name | character varying(255) | | not null | | extended | | displayname | character varying(255) | | not null | | extended | | ispublished | boolean | | not null | | plain | | isretired | boolean | | not null | | plain | | publishdatetime | timestamp(4) without time zone | | | | plain | | createdbyid | numeric(20,0) | | | | main | | updatedbyid | numeric(20,0) | | | | main | | periodid | numeric(20,0) | | not null | | main | | uploadchartyearversionid | numeric(20,0) | | not null | | main | | importchartyearversionid | numeric(20,0) | | | | main | | initialtbadjversionid | numeric(20,0) | | | | main | | latesttbadjversionid | numeric(20,0) | | | | main | | trialbalancesourceid | numeric(20,0) | | not null | | main | | filedefinitionid | numeric(20,0) | | not null | | main | | createdby | character varying(255) | | | | extended | | lastupdatedby | character varying(255) | | | | extended | | Regards, Satalabaha