public inbox for [email protected]  

help / color / mirror / Atom feed

From: yudhi s <[email protected]>
To: pgsql-general <[email protected]>
Subject: Question on execution plan and suitable index
Date: Mon, 16 Feb 2026 00:34:30 +0530
Message-ID: <CAEzWdqeGj9FcubNXegJ8PGTnXNahUhgc6T+yNFW7O12EkKR9yA@mail.gmail.com> (raw)

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

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Question on execution plan and suitable index
  In-Reply-To: <CAEzWdqeGj9FcubNXegJ8PGTnXNahUhgc6T+yNFW7O12EkKR9yA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox