Re: Performance degrade on insert on conflict do nothing

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