Subject: Regarding query optimisation (select for update)

From: Durgamahesh Manne <[email-masked]>
Date: Tue, 15 Jul 2025 15:40:34 +0530

Hi Team,

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)

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

Regards,
Durga Mahesh

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 15 Jul 2025 14:43:59 +0200

On Tue, 2025-07-15 at 15:40 +0530, Durgamahesh Manne wrote:
>    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
>          ->  Index Scan using idx_praermabetdata_processed_betid_versionid on praermabetdata  (cost=0.28..1.88 rows=1 width=78)
> image.png

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

---

From: Durgamahesh Manne <[email-masked]>
Date: Tue, 15 Jul 2025 18:26:34 +0530

On Tue, Jul 15, 2025 at 6:14 PM Laurenz Albe <[email-masked]>
wrote:

> updatedat
> >          ->  Index Scan using
> idx_praermabetdata_processed_betid_versionid on praermabetdata
>  (cost=0.28..1.88 rows=1 width=78)
Hi Team

I can use log_lock_waits to check further information

Regards,
Durga Mahesh

---

From: "Andy Huang" <[email-masked]>
Date: Tue, 15 Jul 2025 21:05:52 +0800

Can you try like: ORDER BY betid desc

---Original---
From: "Durgamahesh Manne"<[email-masked]&gt;
Date: Tue, Jul 15, 2025 18:10 PM
To: "pgsql-general"<[email-masked]&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;

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 15 Jul 2025 16:06:20 +0200

On Tue, 2025-07-15 at 18:26 +0530, Durgamahesh Manne wrote:
> On Tue, Jul 15, 2025 at 6:14 PM Laurenz Albe <[email-masked]> wrote:

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

---

From: Tom Lane <[email-masked]>
Date: Tue, 15 Jul 2025 10:45:44 -0400

> Text is easier to read than images...

Indeed.

> 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]:

    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