Обсуждение: 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, 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=1width=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]:

    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