Performance degrade on insert on conflict do nothing

Hi
insert into
dictionary(lang,tid,sportid,brandid,translatedtext,objecttype,basetid)
values ($1,$2,$3,$4,$5,$6,$7) on conflict do nothing
*8vcpus and 32gb ram
Number of calls per sec 1600 at this time 42% of cpu utilized
Max in ms 33.62 per call
Avg in ms 0.17 per call
Table
"dictionary.dictionary"
Column | Type | Collation | Nullable | Default
| Storage | Compression | Stats target | Description
----------------+--------------------------+-----------+----------+----------+----------+-------------+--------------+-------------
lang | text | | not null |
| extended | | |
tid | text | | not null |
| extended | | |
basetid | text | | not null |
| extended | | |
sportid | text | | |
| extended | | |
brandid | text | | not null |
| extended | | |
translatedtext | text | | |
| extended | | |
objecttype | text | | |
| extended | | |
createdat | timestamp with time zone | | not null | now()
| plain | | |
modified | timestamp with time zone | | not null | now()
| plain | | |
modifiedby | text | | not null |
''::text | extended | | |
version | integer | | not null | 0
| plain | | |
Indexes:
"pk_dictionary" PRIMARY KEY, btree (lang, tid)
"idx_dictionary_basetid" btree (basetid)
"idx_dictionary_brandid" btree (brandid)
"idx_dictionary_objecttype" btree (objecttype)
"idx_dictionary_sportid" btree (sportid)
Triggers:
i_dictionary_createdat BEFORE INSERT ON dictionary FOR EACH ROW EXECUTE
FUNCTION update_createdat_col()
i_dictionary_modified BEFORE INSERT OR UPDATE ON dictionary FOR EACH
ROW EXECUTE FUNCTION update_modified_col()
Access method: heap
How do we improve this query performance without taking more cpu?

Regards,
Durga Mahesh

On Wed, Sep 11, 2024 at 1:02 AM Durgamahesh Manne <maheshpostgres9(at)gmail(dot)com>
wrote:

> Hi
> createdat | timestamp with time zone | | not null | now()
> | plain | | |
> modified | timestamp with time zone | | not null | now()
> | plain | | |
> Triggers:
> i_dictionary_createdat BEFORE INSERT ON dictionary FOR EACH ROW
> EXECUTE FUNCTION update_createdat_col()
> i_dictionary_modified BEFORE INSERT OR UPDATE ON dictionary FOR EACH
> ROW EXECUTE FUNCTION update_modified_col()
>

> How do we improve this query performance without taking more cpu?
>

1. Lose the first trigger. I don't know exactly what those functions do,
but if they are only for updating those columns, just remove the first one
and let postgres handle it via NOT NULL DEFAULT.

2. Change the second trigger to just ON UPDATE

3. Remove that second trigger as well, and have the app populate that
column (assuming that is all it does), e.g. UPDATE dictionary SET lang =
'foo', modified = DEFAULT, modified_by = 'foo' where tid = 12345;

4. Remove any indexes you do not absolutely need

Cheers,
Greg

Hi Greg

Great response from you this worked

Regards
Durga Mahesh

On Wed, Sep 11, 2024 at 7:12 PM Greg Sabino Mullane <htamfids(at)gmail(dot)com>
wrote:

> On Wed, Sep 11, 2024 at 1:02 AM Durgamahesh Manne <
> maheshpostgres9(at)gmail(dot)com> wrote:
>
>> Hi
>> createdat | timestamp with time zone | | not null | now()
>> | plain | | |
>> modified | timestamp with time zone | | not null | now()
>> | plain | | |
>> Triggers:
>> i_dictionary_createdat BEFORE INSERT ON dictionary FOR EACH ROW
>> EXECUTE FUNCTION update_createdat_col()
>> i_dictionary_modified BEFORE INSERT OR UPDATE ON dictionary FOR EACH
>> ROW EXECUTE FUNCTION update_modified_col()
>>
>
>
>> How do we improve this query performance without taking more cpu?
>>
>
> 1. Lose the first trigger. I don't know exactly what those functions do,
> but if they are only for updating those columns, just remove the first one
> and let postgres handle it via NOT NULL DEFAULT.
>
> 2. Change the second trigger to just ON UPDATE
>
> 3. Remove that second trigger as well, and have the app populate that
> column (assuming that is all it does), e.g. UPDATE dictionary SET lang =
> 'foo', modified = DEFAULT, modified_by = 'foo' where tid = 12345;
>
> 4. Remove any indexes you do not absolutely need
>
> Cheers,
> Greg
>
>