Message-ID: <c116cd0011bb36a55f58274daecf4464a23911e7.camel@cybertec.at>
Subject: Re: Regarding query optimisation (select for update)
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Durgamahesh Manne <maheshpostgres9@gmail.com>, pgsql-general
	 <pgsql-general@lists.postgresql.org>
Date: Tue, 15 Jul 2025 14:43:59 +0200
In-Reply-To: <CAJCZko+fiU+=NSEGY7d7YMpFxg_jANpiO-LU6ur+yqRc_Ej6eQ@mail.gmail.com>
References: 
	<CAJCZko+fiU+=NSEGY7d7YMpFxg_jANpiO-LU6ur+yqRc_Ej6eQ@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/c116cd0011bb36a55f58274daecf4464a23911e7.camel%40cybertec.at>
Precedence: bulk

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, cr=
eatedat, updatedat
>    FROM praermabetdata where processed =3D 'false'
>    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 width=3D=
78)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using idx_praermabe=
tdata_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: (proce=
ssed =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

Please provide EXPLAIN (ANALYZE, BUFFERS) output and use "log_lock_waits"
to see if you are hanging behind locks for a longer time.

Yours,
Laurenz Albe