public inbox for [email protected]  

help / color / mirror / Atom feed

From: Durgamahesh Manne <[email protected]>
To: Greg Sabino Mullane <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: Christoph Berg <[email protected]>
Cc: [email protected]
Cc: semab tariq <[email protected]>
Cc: PostgreSQL mailing lists <[email protected]>
Subject: Re: Performance degrade on insert on conflict do nothing
Date: Thu, 12 Sep 2024 19:53:45 +0530
Message-ID: <CAJCZko+jsuNP5z5dRem0oowiB_F25Q4_pOAAuFiG5pvUYTfbaA@mail.gmail.com> (raw)
In-Reply-To: <CAKAnmm+v4=iSLibiTL9QUb2_4hqJRVNVRjNKYst-PNrN+etsOQ@mail.gmail.com>
References: <CAJCZkoLwXzjRfoE_b1YiS2cEC=B6N8vd3+6hWaPxD-LY1YCJZQ@mail.gmail.com>
	<CAKAnmm+v4=iSLibiTL9QUb2_4hqJRVNVRjNKYst-PNrN+etsOQ@mail.gmail.com>

Hi Greg

Great response from you this worked

Regards
Durga Mahesh

On Wed, Sep 11, 2024 at 7:12 PM Greg Sabino Mullane <[email protected]>
wrote:

> On Wed, Sep 11, 2024 at 1:02 AM Durgamahesh Manne <
> [email protected]> 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
>
>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Performance degrade on insert on conflict do nothing
  In-Reply-To: <CAJCZko+jsuNP5z5dRem0oowiB_F25Q4_pOAAuFiG5pvUYTfbaA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox