MIME-Version: 1.0
References: <CAJCZko+fiU+=NSEGY7d7YMpFxg_jANpiO-LU6ur+yqRc_Ej6eQ@mail.gmail.com>
 <c116cd0011bb36a55f58274daecf4464a23911e7.camel@cybertec.at>
 <CAJCZkoLJ6G9J3iK591o35UaKu3x7Kn96ma-u_yqGNgkrLjf6ag@mail.gmail.com>
 <826536fd65cb6b25e996ac449336ce9c2476174b.camel@cybertec.at>
 <903134.1752590744@sss.pgh.pa.us> <CAKAnmmK5mJh+3_p5i41UVV41M_dr9wLz-m7c=V2O343X-MDWfw@mail.gmail.com>
In-Reply-To: <CAKAnmmK5mJh+3_p5i41UVV41M_dr9wLz-m7c=V2O343X-MDWfw@mail.gmail.com>
From: Durgamahesh Manne <maheshpostgres9@gmail.com>
Date: Mon, 16 Feb 2026 05:56:44 +0530
Message-ID: <CAJCZkoJWLPdM223+n1dLKBR27LFPt_8uVXTzuZAg0DWGNXjzVw@mail.gmail.com>
Subject: Re: Regarding query optimisation (select for update)
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Laurenz Albe <laurenz.albe@cybertec.at>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002f88f6064ae60346"
Archived-At: <https://www.postgresql.org/message-id/CAJCZkoJWLPdM223%2Bn1dLKBR27LFPt_8uVXTzuZAg0DWGNXjzVw%40mail.gmail.com>
Precedence: bulk

--0000000000002f88f6064ae60346
Content-Type: text/plain; charset="UTF-8"

On Tue, 15 Jul, 2025, 20:21 Greg Sabino Mullane, <htamfids@gmail.com> wrote:

> You might want to examine the SKIP LOCKED feature as well, if you are
> using this query to have multiple workers grab chunks of the table to work
> on concurrently.
>
> Cheers,
> Greg
>
> --
> Crunchy Data - https://www.crunchydata.com
> Enterprise Postgres Software Products & Tech Support
>

Hi

We are facing issues with session blocking

> e3scoring=> \d+ competition_category Table
> "e3scoring.competition_category" Column | Type | Collation | Nullable |
> Default | Storage | Compression | Stats target | Description
> --------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
> id | character varying(36) | | not null | | extended | | | name | character
> varying | | | | extended | | | short_name | character varying | | | |
> extended | | | sport_id | character varying(36) | | | | extended | | |
> competitions | jsonb | | | | extended | | | sort_factor | real | | | |
> plain | | | brand_id | character varying(36) | | not null | | extended | |
> | created_at | timestamp without time zone | | | now() | plain | | |
> modified | timestamp without time zone | | | | plain | | | version |
> integer | | not null | 0 | plain | | | Indexes: "competition_category_pk"
> PRIMARY KEY, btree (id) "unique_name_brand_sport" UNIQUE CONSTRAINT, btree
> (name, brand_id, sport_id) Foreign-key constraints:
> "competition_category_fk" FOREIGN KEY (brand_id) REFERENCES brand(brandid)
> Access method: heap Options: fillfactor=75
>

select "version", competitions from competition_category cc where name = $1
and brand_id = $2 and sport_id = $3 FOR UPDATE

Is there any best approach to optimize this with out sessions blocking most
of the time  while running mentioned query?

Regards
Durga Mahesh

>

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

<div dir=3D"auto"><div><br><br><div class=3D"gmail_quote gmail_quote_contai=
ner"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, 15 Jul, 2025, 20:21 Greg=
 Sabino Mullane, &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmail.c=
om</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr">=
<div dir=3D"ltr">You might want to examine the SKIP LOCKED feature as well,=
 if you are using this query to have multiple workers grab chunks of the ta=
ble to work on concurrently.</div><div><br></div><div dir=3D"ltr" class=3D"=
gmail_signature"><div dir=3D"ltr"><div>Cheers,</div><div>Greg</div><div><br=
></div><div>--</div><div>Crunchy Data - <a href=3D"https://www.crunchydata.=
com" target=3D"_blank" rel=3D"noreferrer">https://www.crunchydata.com</a></=
div><div>Enterprise Postgres Software Products &amp; Tech Support</div><div=
></div></div></div></div></blockquote></div></div><div dir=3D"auto"><br></d=
iv><div dir=3D"auto">Hi</div><div dir=3D"auto"><br></div><div dir=3D"auto">=
We are facing issues with session blocking=C2=A0</div><div dir=3D"auto"><di=
v class=3D"gmail_quote gmail_quote_container"><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex=
"><div dir=3D"ltr"><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"l=
tr"><div>e3scoring=3D&gt; \d+ competition_category Table &quot;e3scoring.co=
mpetition_category&quot; Column | Type | Collation | Nullable | Default | S=
torage | Compression | Stats target | Description --------------+----------=
-------------------+-----------+----------+---------+----------+-----------=
--+--------------+------------- id | character varying(36) | | not null | |=
 extended | | | name | character varying | | | | extended | | | short_name =
| character varying | | | | extended | | | sport_id | character varying(36)=
 | | | | extended | | | competitions | jsonb | | | | extended | | | sort_fa=
ctor | real | | | | plain | | | brand_id | character varying(36) | | not nu=
ll | | extended | | | created_at | timestamp without time zone | | | now() =
| plain | | | modified | timestamp without time zone | | | | plain | | | ve=
rsion | integer | | not null | 0 | plain | | | Indexes: &quot;competition_c=
ategory_pk&quot; PRIMARY KEY, btree (id) &quot;unique_name_brand_sport&quot=
; UNIQUE CONSTRAINT, btree (name, brand_id, sport_id) Foreign-key constrain=
ts: &quot;competition_category_fk&quot; FOREIGN KEY (brand_id) REFERENCES b=
rand(brandid) Access method: heap Options: fillfactor=3D75</div></div></div=
></div></blockquote></div></div><div dir=3D"auto"><br></div><div dir=3D"aut=
o">select &quot;version&quot;, competitions from competition_category cc wh=
ere name =3D $1 and brand_id =3D $2 and sport_id =3D $3 FOR UPDATE</div><di=
v dir=3D"auto"><br></div><div dir=3D"auto">Is there any best approach to op=
timize this with out sessions blocking most of the time=C2=A0 while running=
 mentioned query?</div><div dir=3D"auto"><br></div><div dir=3D"auto">Regard=
s</div><div dir=3D"auto">Durga Mahesh</div><div dir=3D"auto"><div class=3D"=
gmail_quote gmail_quote_container"><blockquote class=3D"gmail_quote" style=
=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
</blockquote></div></div></div>

--0000000000002f88f6064ae60346--