MIME-Version: 1.0
From: yudhi s <learnerdatabase99@gmail.com>
Date: Mon, 16 Feb 2026 00:34:30 +0530
Message-ID: <CAEzWdqeGj9FcubNXegJ8PGTnXNahUhgc6T+yNFW7O12EkKR9yA@mail.gmail.com>
Subject: Question on execution plan and suitable index
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000efe209064ae1820e"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqeGj9FcubNXegJ8PGTnXNahUhgc6T%2ByNFW7O12EkKR9yA%40mail.gmail.com>
Precedence: bulk

--000000000000efe209064ae1820e
Content-Type: text/plain; charset="UTF-8"

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

--000000000000efe209064ae1820e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,<br>It&#39;s postgres version 17. We are having a criti=
cal UI query which runs for ~7 seconds+. The requirement is to bring down t=
he 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 shou=
ld be addressed. i.e. &quot;Full scan of table &quot;orders&quot; and Neste=
d loop with event_audit_log table&quot;.<br><div><font face=3D"monospace"><=
br></font></div><div><font face=3D"monospace"><b>Below is the query and its=
 complete plan:-=C2=A0</b></font></div><div><a href=3D"https://gist.github.=
com/databasetech0073/f564ac23ee35d1f0413980fe4d00efa9">https://gist.github.=
com/databasetech0073/f564ac23ee35d1f0413980fe4d00efa9</a></div><div><br cla=
ss=3D"gmail-Apple-interchange-newline"></div>I am a bit new to the indexing=
 strategy in postgres. My question is, what suitable index should we create=
 to cater these above?<br><br>1)For table event_audit_log:- Should we creat=
e composite Index on column (request_id,created_at,event_comment_text) or s=
hould we create the covering index i.e. just on two column (request_id,crea=
ted_at) with &quot;include&quot; clause for &quot;event_comment_text&quot;.=
 How and when the covering index indexes should be used here in postgres. W=
ant to understand from experts?=C2=A0<br>2)Similarly for table orders:- Sho=
uld we create a covering index on column (entity_id,due_date,order_type) wi=
th include clause (firm_dspt_case_id). Or just a composite index (entity_id=
,due_date,order_type).<br>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?<br><br><font face=3D"monospa=
ce">-&gt; =C2=A0Nested Loop =C2=A0(cost=3D50.06..2791551.71 rows=3D3148 wid=
th=3D19) (actual time=3D280.735..7065.313 rows=3D57943 loops=3D3)<br>	 =C2=
=A0Buffers: shared hit=3D10014901<br>	 =C2=A0-&gt; =C2=A0Hash Join =C2=A0(c=
ost=3D49.49..1033247.35 rows=3D36729 width=3D8) (actual time=3D196.407..380=
5.755 rows=3D278131 loops=3D3)<br>			Hash Cond: ((ord.entity_id)::numeric =
=3D e.entity_id)<br>			Buffers: shared hit=3D755352<br>			-&gt; =C2=A0Paral=
lel Seq Scan on orders ord =C2=A0(cost=3D0.00..1022872.54 rows=3D3672860 wi=
dth=3D16) (actual time=3D139.883..3152.627 rows=3D2944671 loops=3D3)<br>			=
	 =C2=A0Filter: ((due_date &gt;=3D &#39;2024-01-01&#39;::date) AND (due_dat=
e &lt;=3D &#39;2024-04-01&#39;::date) AND (order_type =3D ANY (&#39;{TYPE_A=
,TYPE_B}&#39;::text[])))<br>				 =C2=A0Rows Removed by Filter: 6572678<br>	=
			 =C2=A0Buffers: shared hit=3D755208</font><div><font face=3D"monospace">=
<br></font></div><div><br></div><div><div><font face=3D"monospace">Regards<=
/font></div><div><font face=3D"monospace">Yudhi</font></div></div></div>

--000000000000efe209064ae1820e--