MIME-Version: 1.0
References: <CAJCZkoLwXzjRfoE_b1YiS2cEC=B6N8vd3+6hWaPxD-LY1YCJZQ@mail.gmail.com>
In-Reply-To: <CAJCZkoLwXzjRfoE_b1YiS2cEC=B6N8vd3+6hWaPxD-LY1YCJZQ@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Wed, 11 Sep 2024 09:41:39 -0400
Message-ID: <CAKAnmm+v4=iSLibiTL9QUb2_4hqJRVNVRjNKYst-PNrN+etsOQ@mail.gmail.com>
Subject: Re: Performance degrade on insert on conflict do nothing
To: Durgamahesh Manne <maheshpostgres9@gmail.com>
Cc: pgsql-general@lists.postgresql.org, pgsql-in-general@postgresql.org, 
	Christoph Berg <myon@debian.org>, laurenz.albe@cybertec.at, 
	semab tariq <semabtariq1@gmail.com>, 
	PostgreSQL mailing lists <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000008722df0621d82804"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2Bv4%3DiSLibiTL9QUb2_4hqJRVNVRjNKYst-PNrN%2BetsOQ%40mail.gmail.com>
Precedence: bulk

--0000000000008722df0621d82804
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Sep 11, 2024 at 1:02=E2=80=AFAM Durgamahesh Manne <maheshpostgres9@=
gmail.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 =3D
'foo', modified =3D DEFAULT, modified_by =3D 'foo' where tid =3D 12345;

4. Remove any indexes you do not absolutely need

Cheers,
Greg

--0000000000008722df0621d82804
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Sep 11, 2024 at 1:02=E2=80=AFAM D=
urgamahesh Manne &lt;<a href=3D"mailto:maheshpostgres9@gmail.com">maheshpos=
tgres9@gmail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hi=C2=A0<div>=
=C2=A0createdat =C2=A0 =C2=A0 =C2=A0| timestamp with time zone | =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 | not null | now() =C2=A0 =C2=A0| plain =C2=A0 =C2=
=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0|<br></div><div>=C2=A0modified =C2=A0 =C2=A0 =C2=A0=
 | timestamp with time zone | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null=
 | now() =C2=A0 =C2=A0| plain =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|<br>Trigge=
rs:<br>=C2=A0 =C2=A0 i_dictionary_createdat BEFORE INSERT ON dictionary FOR=
 EACH ROW EXECUTE FUNCTION update_createdat_col()<br>=C2=A0 =C2=A0 i_dictio=
nary_modified BEFORE INSERT OR UPDATE ON dictionary FOR EACH ROW EXECUTE FU=
NCTION update_modified_col()</div></div></blockquote><div>=C2=A0<br></div><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>How =
do we improve this query performance without taking more cpu?</div></div></=
blockquote><div><br></div><div>1. Lose the first trigger. I don&#39;t know =
exactly what those functions do, but if they are only for updating those co=
lumns, just remove the first one and let postgres handle it via NOT NULL DE=
FAULT.</div><div><br></div><div>2. Change the second trigger to just ON UPD=
ATE</div><div><br></div><div>3. Remove that second trigger as well, and hav=
e the app populate that column (assuming that is all it does), e.g. UPDATE =
dictionary SET lang =3D &#39;foo&#39;, modified =3D DEFAULT, modified_by =
=3D &#39;foo&#39; where tid =3D 12345;</div><div><br></div><div>4. Remove a=
ny indexes you do not absolutely need</div><div><br></div><div>Cheers,</div=
><div>Greg</div><div><br></div></div></div>

--0000000000008722df0621d82804--