public inbox for [email protected]
help / color / mirror / Atom feedFrom: Greg Sabino Mullane <[email protected]>
To: Durgamahesh Manne <[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: Wed, 11 Sep 2024 09:41:39 -0400
Message-ID: <CAKAnmm+v4=iSLibiTL9QUb2_4hqJRVNVRjNKYst-PNrN+etsOQ@mail.gmail.com> (raw)
In-Reply-To: <CAJCZkoLwXzjRfoE_b1YiS2cEC=B6N8vd3+6hWaPxD-LY1YCJZQ@mail.gmail.com>
References: <CAJCZkoLwXzjRfoE_b1YiS2cEC=B6N8vd3+6hWaPxD-LY1YCJZQ@mail.gmail.com>
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: <CAKAnmm+v4=iSLibiTL9QUb2_4hqJRVNVRjNKYst-PNrN+etsOQ@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