Re: Regarding query optimisation (select for update)

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

>