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

Reply via email to