Performance degrade on insert on conflict do nothing

From: Durgamahesh Manne <maheshpostgres9(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org, pgsql-in-general(at)postgresql(dot)org, Christoph Berg <myon(at)debian(dot)org>, laurenz(dot)albe(at)cybertec(dot)at, semab tariq <semabtariq1(at)gmail(dot)com>, PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org>
Subject: Performance degrade on insert on conflict do nothing
Date: 2024-09-11 05:05:16
Message-ID: CAJCZkoLwXzjRfoE_b1YiS2cEC=B6N8vd3+6hWaPxD-LY1YCJZQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-in-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Durgamahesh Manne 2024-09-11 08:53:04 Recommendations on improving the insert on conflict do nothing performance
Previous Message Chris Miller 2024-09-11 01:51:41 Re: Test mail for pgsql-general

Browse pgsql-in-general by date

  From Date Subject
Next Message Durgamahesh Manne 2024-09-11 08:53:04 Recommendations on improving the insert on conflict do nothing performance
Previous Message Durgamahesh Manne 2024-08-13 19:38:53 Re: autovacuum freeze recommendations at table level