public inbox for [email protected]
help / color / mirror / Atom feedQuestion on execution plan and suitable index
3+ messages / 3 participants
[nested] [flat]
* Question on execution plan and suitable index
@ 2026-02-15 19:04 yudhi s <[email protected]>
2026-02-15 20:51 ` Re: Question on execution plan and suitable index Adrian Klaver <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: yudhi s @ 2026-02-15 19:04 UTC (permalink / raw)
To: pgsql-general <[email protected]>
Hi,
It's postgres version 17. We are having a critical UI query which runs for
~7 seconds+. The requirement is to bring down the response time within ~1
sec. Now in this plan , If i read this correctly, the below section is
consuming a significant amount of resources and should be addressed. i.e.
"Full scan of table "orders" and Nested loop with event_audit_log table".
*Below is the query and its complete plan:- *
https://gist.github.com/databasetech0073/f564ac23ee35d1f0413980fe4d00efa9
I am a bit new to the indexing strategy in postgres. My question is, what
suitable index should we create to cater these above?
1)For table event_audit_log:- Should we create composite Index on column
(request_id,created_at,event_comment_text) or should we create the covering
index i.e. just on two column (request_id,created_at) with "include" clause
for "event_comment_text". How and when the covering index indexes should be
used here in postgres. Want to understand from experts?
2)Similarly for table orders:- Should we create a covering index on column
(entity_id,due_date,order_type) with include clause (firm_dspt_case_id). Or
just a composite index (entity_id,due_date,order_type).
3)Whether the column used as range operator (here created_at or due_date)
should be used as leading column in the composite index or is it fine to
keep it as non leading?
-> Nested Loop (cost=50.06..2791551.71 rows=3148 width=19) (actual
time=280.735..7065.313 rows=57943 loops=3)
Buffers: shared hit=10014901
-> Hash Join (cost=49.49..1033247.35 rows=36729 width=8) (actual
time=196.407..3805.755 rows=278131 loops=3)
Hash Cond: ((ord.entity_id)::numeric = e.entity_id)
Buffers: shared hit=755352
-> Parallel Seq Scan on orders ord (cost=0.00..1022872.54 rows=3672860
width=16) (actual time=139.883..3152.627 rows=2944671 loops=3)
Filter: ((due_date >= '2024-01-01'::date) AND (due_date <=
'2024-04-01'::date) AND (order_type = ANY ('{TYPE_A,TYPE_B}'::text[])))
Rows Removed by Filter: 6572678
Buffers: shared hit=755208
Regards
Yudhi
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question on execution plan and suitable index
2026-02-15 19:04 Question on execution plan and suitable index yudhi s <[email protected]>
@ 2026-02-15 20:51 ` Adrian Klaver <[email protected]>
2026-02-15 21:38 ` Re: Question on execution plan and suitable index Nisarg Patel <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Adrian Klaver @ 2026-02-15 20:51 UTC (permalink / raw)
To: yudhi s <[email protected]>; pgsql-general <[email protected]>
On 2/15/26 11:04, yudhi s wrote:
> Hi,
> It's postgres version 17. We are having a critical UI query which runs
> for ~7 seconds+. The requirement is to bring down the response time
> within ~1 sec. Now in this plan , If i read this correctly, the below
> section is consuming a significant amount of resources and should be
> addressed. i.e. "Full scan of table "orders" and Nested loop with
> event_audit_log table".
For a start:
1) Supply the complete schema for the tables involved.
2) Also what is the minor version you are using e.g the x in 17.x?
I also recommend reading:
https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> *Below is the query and its complete plan:- *
> https://gist.github.com/databasetech0073/
> f564ac23ee35d1f0413980fe4d00efa9 <https://gist.github.com/
> databasetech0073/f564ac23ee35d1f0413980fe4d00efa9>
>
> I am a bit new to the indexing strategy in postgres. My question is,
> what suitable index should we create to cater these above?
>
> 1)For table event_audit_log:- Should we create composite Index on column
> (request_id,created_at,event_comment_text) or should we create the
> covering index i.e. just on two column (request_id,created_at) with
> "include" clause for "event_comment_text". How and when the covering
> index indexes should be used here in postgres. Want to understand from
> experts?
> 2)Similarly for table orders:- Should we create a covering index on
> column (entity_id,due_date,order_type) with include clause
> (firm_dspt_case_id). Or just a composite index
> (entity_id,due_date,order_type).
> 3)Whether the column used as range operator (here created_at or
> due_date) should be used as leading column in the composite index or is
> it fine to keep it as non leading?
>
> -> Nested Loop (cost=50.06..2791551.71 rows=3148 width=19) (actual
> time=280.735..7065.313 rows=57943 loops=3)
> Buffers: shared hit=10014901
> -> Hash Join (cost=49.49..1033247.35 rows=36729 width=8) (actual
> time=196.407..3805.755 rows=278131 loops=3)
> Hash Cond: ((ord.entity_id)::numeric = e.entity_id)
> Buffers: shared hit=755352
> -> Parallel Seq Scan on orders ord (cost=0.00..1022872.54 rows=3672860
> width=16) (actual time=139.883..3152.627 rows=2944671 loops=3)
> Filter: ((due_date >= '2024-01-01'::date) AND (due_date <=
> '2024-04-01'::date) AND (order_type = ANY ('{TYPE_A,TYPE_B}'::text[])))
> Rows Removed by Filter: 6572678
> Buffers: shared hit=755208
>
>
> Regards
> Yudhi
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question on execution plan and suitable index
2026-02-15 19:04 Question on execution plan and suitable index yudhi s <[email protected]>
2026-02-15 20:51 ` Re: Question on execution plan and suitable index Adrian Klaver <[email protected]>
@ 2026-02-15 21:38 ` Nisarg Patel <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Nisarg Patel @ 2026-02-15 21:38 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: yudhi s <[email protected]>; pgsql-general <[email protected]>
I would definitely recommend to focus on this section:
Filter: ((due_date >= '2024-01-01'::date) AND (due_date <=
'2024-04-01'::date) AND (order_type = ANY ('{TYPE_A,TYPE_B}'::text[])))
Rows Removed by Filter: 6572678
You can certainly try composite index or partial index for order_type.
Thank you,
Nisarg
On Sun, Feb 15, 2026, 3:51 PM Adrian Klaver <[email protected]>
wrote:
> On 2/15/26 11:04, yudhi s wrote:
> > Hi,
> > It's postgres version 17. We are having a critical UI query which runs
> > for ~7 seconds+. The requirement is to bring down the response time
> > within ~1 sec. Now in this plan , If i read this correctly, the below
> > section is consuming a significant amount of resources and should be
> > addressed. i.e. "Full scan of table "orders" and Nested loop with
> > event_audit_log table".
>
> For a start:
>
> 1) Supply the complete schema for the tables involved.
>
> 2) Also what is the minor version you are using e.g the x in 17.x?
>
> I also recommend reading:
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> >
> > *Below is the query and its complete plan:- *
> > https://gist.github.com/databasetech0073/
> > f564ac23ee35d1f0413980fe4d00efa9 <https://gist.github.com/
> > databasetech0073/f564ac23ee35d1f0413980fe4d00efa9>
> >
> > I am a bit new to the indexing strategy in postgres. My question is,
> > what suitable index should we create to cater these above?
> >
> > 1)For table event_audit_log:- Should we create composite Index on column
> > (request_id,created_at,event_comment_text) or should we create the
> > covering index i.e. just on two column (request_id,created_at) with
> > "include" clause for "event_comment_text". How and when the covering
> > index indexes should be used here in postgres. Want to understand from
> > experts?
> > 2)Similarly for table orders:- Should we create a covering index on
> > column (entity_id,due_date,order_type) with include clause
> > (firm_dspt_case_id). Or just a composite index
> > (entity_id,due_date,order_type).
> > 3)Whether the column used as range operator (here created_at or
> > due_date) should be used as leading column in the composite index or is
> > it fine to keep it as non leading?
> >
> > -> Nested Loop (cost=50.06..2791551.71 rows=3148 width=19) (actual
> > time=280.735..7065.313 rows=57943 loops=3)
> > Buffers: shared hit=10014901
> > -> Hash Join (cost=49.49..1033247.35 rows=36729 width=8) (actual
> > time=196.407..3805.755 rows=278131 loops=3)
> > Hash Cond: ((ord.entity_id)::numeric = e.entity_id)
> > Buffers: shared hit=755352
> > -> Parallel Seq Scan on orders ord (cost=0.00..1022872.54 rows=3672860
> > width=16) (actual time=139.883..3152.627 rows=2944671 loops=3)
> > Filter: ((due_date >= '2024-01-01'::date) AND (due_date <=
> > '2024-04-01'::date) AND (order_type = ANY ('{TYPE_A,TYPE_B}'::text[])))
> > Rows Removed by Filter: 6572678
> > Buffers: shared hit=755208
> >
> >
> > Regards
> > Yudhi
>
>
> --
> Adrian Klaver
> [email protected]
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-02-15 21:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-15 19:04 Question on execution plan and suitable index yudhi s <[email protected]>
2026-02-15 20:51 ` Adrian Klaver <[email protected]>
2026-02-15 21:38 ` Nisarg Patel <[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