public inbox for [email protected]
help / color / mirror / Atom feedRegarding query optimisation (select for update)
6+ messages / 4 participants
[nested] [flat]
* Regarding query optimisation (select for update)
@ 2025-07-15 10:10 Durgamahesh Manne <[email protected]>
2025-07-15 12:43 ` Re: Regarding query optimisation (select for update) Laurenz Albe <[email protected]>
2025-07-15 13:05 ` Re: Regarding query optimisation (select for update) =?utf-8?B?QW5keSBIdWFuZw==?= <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Durgamahesh Manne @ 2025-07-15 10:10 UTC (permalink / raw)
To: pgsql-general <[email protected]>
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)
[image: image.png]
Do we have any alternative way to improve the performance?
Sometimes processed column use true as well as false
Regards,
Durga Mahesh
Attachments:
[image/png] image.png (18.0K, 3-image.png)
download | view image
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Regarding query optimisation (select for update)
2025-07-15 10:10 Regarding query optimisation (select for update) Durgamahesh Manne <[email protected]>
@ 2025-07-15 12:43 ` Laurenz Albe <[email protected]>
2025-07-15 12:56 ` Re: Regarding query optimisation (select for update) Durgamahesh Manne <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2025-07-15 12:43 UTC (permalink / raw)
To: Durgamahesh Manne <[email protected]>; pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Regarding query optimisation (select for update)
2025-07-15 10:10 Regarding query optimisation (select for update) Durgamahesh Manne <[email protected]>
2025-07-15 12:43 ` Re: Regarding query optimisation (select for update) Laurenz Albe <[email protected]>
@ 2025-07-15 12:56 ` Durgamahesh Manne <[email protected]>
2025-07-15 14:06 ` Re: Regarding query optimisation (select for update) Laurenz Albe <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Durgamahesh Manne @ 2025-07-15 12:56 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: pgsql-general <[email protected]>
On Tue, Jul 15, 2025 at 6:14 PM Laurenz Albe <[email protected]>
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
Attachments:
[image/png] image.png (16.8K, 3-image.png)
download | view image
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Regarding query optimisation (select for update)
2025-07-15 10:10 Regarding query optimisation (select for update) Durgamahesh Manne <[email protected]>
2025-07-15 12:43 ` Re: Regarding query optimisation (select for update) Laurenz Albe <[email protected]>
2025-07-15 12:56 ` Re: Regarding query optimisation (select for update) Durgamahesh Manne <[email protected]>
@ 2025-07-15 14:06 ` Laurenz Albe <[email protected]>
2025-07-15 14:45 ` Re: Regarding query optimisation (select for update) Tom Lane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2025-07-15 14:06 UTC (permalink / raw)
To: Durgamahesh Manne <[email protected]>; +Cc: pgsql-general <[email protected]>
On Tue, 2025-07-15 at 18:26 +0530, Durgamahesh Manne wrote:
> On Tue, Jul 15, 2025 at 6:14 PM Laurenz Albe <[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Regarding query optimisation (select for update)
2025-07-15 10:10 Regarding query optimisation (select for update) Durgamahesh Manne <[email protected]>
2025-07-15 12:43 ` Re: Regarding query optimisation (select for update) Laurenz Albe <[email protected]>
2025-07-15 12:56 ` Re: Regarding query optimisation (select for update) Durgamahesh Manne <[email protected]>
2025-07-15 14:06 ` Re: Regarding query optimisation (select for update) Laurenz Albe <[email protected]>
@ 2025-07-15 14:45 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2025-07-15 14:45 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Durgamahesh Manne <[email protected]>; pgsql-general <[email protected]>
Laurenz Albe <[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Regarding query optimisation (select for update)
2025-07-15 10:10 Regarding query optimisation (select for update) Durgamahesh Manne <[email protected]>
@ 2025-07-15 13:05 ` =?utf-8?B?QW5keSBIdWFuZw==?= <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: =?utf-8?B?QW5keSBIdWFuZw==?= @ 2025-07-15 13:05 UTC (permalink / raw)
To: =?utf-8?B?RHVyZ2FtYWhlc2ggTWFubmU=?= <[email protected]>; =?utf-8?B?cGdzcWwtZ2VuZXJhbA==?= <[email protected]>
Can you try like: ORDER BY betid desc
---Original---
From: "Durgamahesh Manne"<[email protected]>
Date: Tue, Jul 15, 2025 18:10 PM
To: "pgsql-general"<[email protected]>;
Subject: Regarding query optimisation (select for update)
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
Attachments:
[application/octet-stream] [email protected] (18.0K, [email protected])
download
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-07-15 14:45 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-15 10:10 Regarding query optimisation (select for update) Durgamahesh Manne <[email protected]>
2025-07-15 12:43 ` Laurenz Albe <[email protected]>
2025-07-15 12:56 ` Durgamahesh Manne <[email protected]>
2025-07-15 14:06 ` Laurenz Albe <[email protected]>
2025-07-15 14:45 ` Tom Lane <[email protected]>
2025-07-15 13:05 ` =?utf-8?B?QW5keSBIdWFuZw==?= <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox