Message-ID: <818d0359d8b629a80b55b2e068dab958fc8e0a2a.camel@cybertec.at>
Subject: Re: Question on execution plan and suitable index
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: yudhi s <learnerdatabase99@gmail.com>, pgsql-general
	 <pgsql-general@lists.postgresql.org>
Date: Mon, 16 Feb 2026 09:59:55 +0100
In-Reply-To: <CAEzWdqeGj9FcubNXegJ8PGTnXNahUhgc6T+yNFW7O12EkKR9yA@mail.gmail.com>
References: 
	<CAEzWdqeGj9FcubNXegJ8PGTnXNahUhgc6T+yNFW7O12EkKR9yA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/818d0359d8b629a80b55b2e068dab958fc8e0a2a.camel%40cybertec.at>
Precedence: bulk

On Mon, 2026-02-16 at 00:34 +0530, yudhi s wrote:
> It's postgres version 17. We are having a critical UI query which runs fo=
r ~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 con=
suming a significant amount of resources and should be addressed. i.e. "Ful=
l scan of table "orders" and Nested loop with event_audit_log table".
>=20
> Below is the query and its complete plan:-=C2=A0
> https://gist.github.com/databasetech0073/f564ac23ee35d1f0413980fe4d00efa9
>=20
> I am a bit new to the indexing strategy in postgres. My question is, what=
 suitable index should we create to cater these above?
>=20
> 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" claus=
e for "event_comment_text". How and when the covering index indexes should =
be used here in postgres. Want to understand from experts?=C2=A0
> 2)Similarly for table orders:- Should we create a covering index on colum=
n (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 k=
eep it as non leading?
>=20
> -> =C2=A0Nested Loop =C2=A0(cost=3D50.06..2791551.71 rows=3D3148 width=3D=
19) (actual time=3D280.735..7065.313 rows=3D57943 loops=3D3)
> =C2=A0=C2=A0Buffers: shared hit=3D10014901
> =C2=A0=C2=A0-> =C2=A0Hash Join =C2=A0(cost=3D49.49..1033247.35 rows=3D367=
29 width=3D8) (actual time=3D196.407..3805.755 rows=3D278131 loops=3D3)
> =C2=A0Hash Cond: ((ord.entity_id)::numeric =3D e.entity_id)
> =C2=A0Buffers: shared hit=3D755352
> =C2=A0-> =C2=A0Parallel Seq Scan on orders ord =C2=A0(cost=3D0.00..102287=
2.54 rows=3D3672860 width=3D16) (actual time=3D139.883..3152.627 rows=3D294=
4671 loops=3D3)
> =C2=A0=C2=A0Filter: ((due_date >=3D '2024-01-01'::date) AND (due_date <=
=3D '2024-04-01'::date) AND (order_type =3D ANY ('{TYPE_A,TYPE_B}'::text[])=
))
> =C2=A0=C2=A0Rows Removed by Filter: 6572678
> =C2=A0=C2=A0Buffers: shared hit=3D755208

You are selecting a lot of rows, so the query will never be really cheap.
But I agree that an index scan should be a win.

If the condition on "order_type" is always the same, a partial index is ide=
al:

   CREATE INDEX ON orders (due_date) WHERE order_type IN ('TYPE_A', 'TYPE_B=
');

Otherwise, I'd create two indexes: one on "order_type" and one on "due_date=
".

Yours,
Laurenz Albe