Em dom., 4 de jun. de 2023 às 11:49, Satalabaha Postgres < satalabaha.postg...@gmail.com> escreveu:
> > > > 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 | | > I think you are in trouble when comparing float8 (double precision) with numeric. This small example shows problems. Postgres version 14.2: SELECT '8217316934885843456'::float8 = '8217316934885843456'::float8::bigint::float8, '8217316934885843456'::float8 = '8217316934885843456'::float8::numeric::float8; ?column? | ?column? ----------+---------- t | f (1 row) I suggest a study to switch to bigint. regards, Ranier Vilela
