Re: Regarding query optimisation (select for update)

On Mon, 16 Feb, 2026, 05:56 Durgamahesh Manne, <maheshpostgres9(at)gmail(dot)com>
wrote:

>
>
> On Tue, 15 Jul, 2025, 20:21 Greg Sabino Mullane, <htamfids(at)gmail(dot)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
>

Hi

Does this work in better way please check once
WITH c AS ( SELECT * FROM competition_category WHERE name = $1
AND brand_id = $2 AND sport_id = $3 ) SELECT "version", competitions
FROM c FOR UPDATE;
(OR)
SELECT "version", competitions FROM competition_category WHERE id = (
SELECT id FROM competition_category WHERE name = $1 AND brand_id =
$2 AND sport_id = $3 ) FOR UPDATE;
(OR)
UPDATE competition_category SET version = version WHERE name = $1 AND
brand_id = $2 AND sport_id = $3 RETURNING "version", competitions;

I am not a data developer.Was getting this multiple sources

Regards
Durga Mahesh

>