Regarding query optimisation (select for update)

On Tue, 2025-07-15 at 15:40 +0530, Durgamahesh Manne wrote:

We are facing issues with slow running query 
   SELECT betid, versionid, betdata, processed, messagetime, createdat, updatedat
FROM praermabetdata where processed = 'false'
ORDER BY betid, versionid LIMIT 200 OFFSET 0 FOR UPDATE;  

                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.28..1.89 rows=1 width=78)
   ->  LockRows  (cost=0.28..1.89 rows=1 width=78)
         ->  Index Scan using idx_praermabetdata_processed_betid_versionid on praermabetdata  (cost=0.28..1.88 rows=1 width=78)
               Index Cond: (processed = false)

image.png

Do we have any alternative way to improve the performance?
Sometimes processed column use true as well as false 

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

On Tue, Jul 15, 2025 at 6:14 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Tue, 2025-07-15 at 15:40 +0530, Durgamahesh Manne wrote:

We are facing issues with slow running query
SELECT betid, versionid, betdata, processed, messagetime, createdat,

updatedat

FROM praermabetdata where processed = 'false'
ORDER BY betid, versionid LIMIT 200 OFFSET 0 FOR UPDATE;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------

Limit (cost=0.28..1.89 rows=1 width=78)
-> LockRows (cost=0.28..1.89 rows=1 width=78)
-> Index Scan using

idx_praermabetdata_processed_betid_versionid on praermabetdata
(cost=0.28..1.88 rows=1 width=78)

Index Cond: (processed = false)

image.png

Do we have any alternative way to improve the performance?
Sometimes processed column use true as well as false

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

Hi Team

[image: image.png]

I can use log_lock_waits to check further information

Regards,
Durga Mahesh

Can you try like: ORDER BY betid desc

---Original---
From: "Durgamahesh Manne"<maheshpostgres9@gmail.com&gt;
Date: Tue, Jul 15, 2025 18:10 PM
To: "pgsql-general"<pgsql-general@lists.postgresql.org&gt;;
Subject: Regarding query optimisation (select for update)

Hi Team,

We are facing issues&nbsp;with slow running query&nbsp;
&nbsp; &nbsp;SELECT betid, versionid, betdata, processed, messagetime, createdat, updatedat FROM praermabetdata where processed = 'false' ORDER BY betid, versionid LIMIT 200 OFFSET 0 FOR UPDATE;&nbsp;&nbsp;

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
&nbsp;Limit &nbsp;(cost=0.28..1.89 rows=1 width=78)
&nbsp; &nbsp;-&gt; &nbsp;LockRows &nbsp;(cost=0.28..1.89 rows=1 width=78)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Index Scan using idx_praermabetdata_processed_betid_versionid on praermabetdata &nbsp;(cost=0.28..1.88 rows=1 width=78)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Index Cond: (processed = false)

Do we have any alternative way to improve the performance?
Sometimes processed column use true as well as false&nbsp;

Regards,
Durga Mahesh&nbsp;

On Tue, 2025-07-15 at 18:26 +0530, Durgamahesh Manne wrote:

On Tue, Jul 15, 2025 at 6:14 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Tue, 2025-07-15 at 15:40 +0530, Durgamahesh Manne wrote:

We are facing issues with slow running query 
   SELECT betid, versionid, betdata, processed, messagetime, createdat, updatedat
    FROM praermabetdata where processed = 'false'
    ORDER BY betid, versionid LIMIT 200 OFFSET 0 FOR UPDATE;  

                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.28..1.89 rows=1 width=78)
   ->  LockRows  (cost=0.28..1.89 rows=1 width=78)
         ->  Index Scan using idx_praermabetdata_processed_betid_versionid on praermabetdata  (cost=0.28..1.88 rows=1 width=78)
               Index Cond: (processed = false)

image.png

Do we have any alternative way to improve the performance?
Sometimes processed column use true as well as false 

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

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

Laurenz Albe <laurenz.albe@cybertec.at> writes:

Text is easier to read than images...

Indeed.

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

It looks to me like this EXPLAIN ANALYZE was done against a totally
empty table, so probably in a freshly-set-up dev environment. That
is not going to be helpful in identifying your production problem.
Note the caveat at [1]https://www.postgresql.org/docs/current/using-explain.html#USING-EXPLAIN-CAVEATS:

EXPLAIN results should not be extrapolated to situations much
different from the one you are actually testing; for example,
results on a toy-sized table cannot be assumed to apply to large
tables.

regards, tom lane

[1]: https://www.postgresql.org/docs/current/using-explain.html#USING-EXPLAIN-CAVEATS

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

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

On Mon, 16 Feb, 2026, 05:56 Durgamahesh Manne, <maheshpostgres9@gmail.com>
wrote:

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

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