Re: Weird behavior of INSERT QUERY

Em dom., 4 de jun. de 2023 às 11:49, Satalabaha Postgres <
satalabaha(dot)postgres(at)gmail(dot)com> escreveu:

>
>
>
> 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 | |
>
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