Re: Weird behavior of INSERT QUERY

On Sun, 4 Jun 2023 at 19:46, Ranier Vilela <ranier(dot)vf(at)gmail(dot)com> wrote:

> Em dom., 4 de jun. de 2023 às 05:35, Satalabaha Postgres <
> satalabaha(dot)postgres(at)gmail(dot)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