Message-ID: <826536fd65cb6b25e996ac449336ce9c2476174b.camel@cybertec.at>
Subject: Re: Regarding query optimisation (select for update)
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Durgamahesh Manne <maheshpostgres9@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Date: Tue, 15 Jul 2025 16:06:20 +0200
In-Reply-To: <CAJCZkoLJ6G9J3iK591o35UaKu3x7Kn96ma-u_yqGNgkrLjf6ag@mail.gmail.com>
References: 
	<CAJCZko+fiU+=NSEGY7d7YMpFxg_jANpiO-LU6ur+yqRc_Ej6eQ@mail.gmail.com>
	 <c116cd0011bb36a55f58274daecf4464a23911e7.camel@cybertec.at>
	 <CAJCZkoLJ6G9J3iK591o35UaKu3x7Kn96ma-u_yqGNgkrLjf6ag@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/826536fd65cb6b25e996ac449336ce9c2476174b.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-07-15 at 18:26 +0530, Durgamahesh Manne wrote:
> On Tue, Jul 15, 2025 at 6:14=E2=80=AFPM Laurenz Albe <laurenz.albe@cybert=
ec.at> wrote:
> > On Tue, 2025-07-15 at 15:40 +0530, Durgamahesh Manne wrote:
> > > We are facing issues=C2=A0with slow running query=C2=A0
> > > =C2=A0 =C2=A0SELECT betid, versionid, betdata, processed, messagetime=
, createdat, updatedat
> > > =C2=A0 =C2=A0 FROM praermabetdata where processed =3D 'false'
> > > =C2=A0 =C2=A0 ORDER BY betid, versionid LIMIT 200 OFFSET 0 FOR UPDATE=
;=C2=A0=C2=A0
> > >=20
> > > =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 =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=A0QUERY PLAN
> > > ---------------------------------------------------------------------=
-----------------------------------------------------------
> > > =C2=A0Limit =C2=A0(cost=3D0.28..1.89 rows=3D1 width=3D78)
> > > =C2=A0 =C2=A0-> =C2=A0LockRows =C2=A0(cost=3D0.28..1.89 rows=3D1 widt=
h=3D78)
> > > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using idx_praer=
mabetdata_processed_betid_versionid on praermabetdata =C2=A0(cost=3D0.28..1=
.88 rows=3D1 width=3D78)
> > > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (p=
rocessed =3D false)
> > >=20
> > > image.png
> > >=20
> > > Do we have any alternative way to improve the performance?
> > > Sometimes processed column use true as well as false=C2=A0
> >=20
> > Please provide EXPLAIN (ANALYZE, BUFFERS) output and use "log_lock_wait=
s"
> > to see if you are hanging behind locks for a longer time.
>=20
> image.png

Text is easier to read than images...

Anyway, this statement was done in under a millisecond.
I wouldn't call that slow...

Yours,
Laurenz Albe