Thread: 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
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@gmail.com> wrote:
Hicreatedat | 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@gmail.com> wrote:
On Wed, Sep 11, 2024 at 1:02 AM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:Hicreatedat | 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 UPDATE3. 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 needCheers,Greg