MIME-Version: 1.0
References: <CAJCZkoLwXzjRfoE_b1YiS2cEC=B6N8vd3+6hWaPxD-LY1YCJZQ@mail.gmail.com>
 <CAKAnmm+v4=iSLibiTL9QUb2_4hqJRVNVRjNKYst-PNrN+etsOQ@mail.gmail.com>
In-Reply-To: <CAKAnmm+v4=iSLibiTL9QUb2_4hqJRVNVRjNKYst-PNrN+etsOQ@mail.gmail.com>
From: Durgamahesh Manne <maheshpostgres9@gmail.com>
Date: Thu, 12 Sep 2024 19:53:45 +0530
Message-ID: <CAJCZko+jsuNP5z5dRem0oowiB_F25Q4_pOAAuFiG5pvUYTfbaA@mail.gmail.com>
Subject: Re: Performance degrade on insert on conflict do nothing
To: Greg Sabino Mullane <htamfids@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="000000000000a933ce0621ecd1dc"
Archived-At: <https://www.postgresql.org/message-id/CAJCZko%2BjsuNP5z5dRem0oowiB_F25Q4_pOAAuFiG5pvUYTfbaA%40mail.gmail.com>
Precedence: bulk

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

Hi Greg

Great response from you this worked

Regards
Durga Mahesh

On Wed, Sep 11, 2024 at 7:12=E2=80=AFPM Greg Sabino Mullane <htamfids@gmail=
.com>
wrote:

> 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 on=
e
> 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
>
>

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

<div dir=3D"ltr">Hi=C2=A0Greg<div><br></div><div>Great response from you th=
is worked=C2=A0</div><div><br></div><div>Regards</div><div>Durga Mahesh=C2=
=A0</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gma=
il_attr">On Wed, Sep 11, 2024 at 7:12=E2=80=AFPM Greg Sabino Mullane &lt;<a=
 href=3D"mailto:htamfids@gmail.com">htamfids@gmail.com</a>&gt; wrote:<br></=
div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div=
 dir=3D"ltr">On Wed, Sep 11, 2024 at 1:02=E2=80=AFAM Durgamahesh Manne &lt;=
<a href=3D"mailto:maheshpostgres9@gmail.com" target=3D"_blank">maheshpostgr=
es9@gmail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid 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 | t=
imestamp with time zone | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null | n=
ow() =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>Triggers:<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_dictionary_m=
odified BEFORE INSERT OR UPDATE ON dictionary FOR EACH ROW EXECUTE FUNCTION=
 update_modified_col()</div></div></blockquote><div>=C2=A0<br></div><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"><div>How do we =
improve this query performance without taking more cpu?</div></div></blockq=
uote><div><br></div><div>1. Lose the first trigger. I don&#39;t know exactl=
y 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.=
</div><div><br></div><div>2. Change the second trigger to just ON UPDATE</d=
iv><div><br></div><div>3. Remove that second trigger as well, and have the =
app populate that column (assuming that is all it does), e.g. UPDATE dictio=
nary 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 any index=
es you do not absolutely need</div><div><br></div><div>Cheers,</div><div>Gr=
eg</div><div><br></div></div></div>
</blockquote></div>

--000000000000a933ce0621ecd1dc--