Message-ID: <2fa62200cf92dc03dff20f8a42e45bb30fc40f35.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>, Ron Johnson
 <ronljohnsonjr@gmail.com>,  Adrian Klaver <adrian.klaver@aklaver.com>,
 Nisarg Patel <er.nisarg@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Date: Mon, 16 Feb 2026 10:54:47 +0100
In-Reply-To: <CAEzWdqfxtEzxO10Rnr0Yw+tPJMtCuu2c2e1mr6bEzuYL1U1BvA@mail.gmail.com>
References: 
	<CAEzWdqeGj9FcubNXegJ8PGTnXNahUhgc6T+yNFW7O12EkKR9yA@mail.gmail.com>
	 <818d0359d8b629a80b55b2e068dab958fc8e0a2a.camel@cybertec.at>
	 <CAEzWdqfxtEzxO10Rnr0Yw+tPJMtCuu2c2e1mr6bEzuYL1U1BvA@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/2fa62200cf92dc03dff20f8a42e45bb30fc40f35.camel%40cybertec.at>
Precedence: bulk

On Mon, 2026-02-16 at 14:43 +0530, yudhi s wrote:
> On Mon, Feb 16, 2026 at 2:29=E2=80=AFPM Laurenz Albe <laurenz.albe@cybert=
ec.at> wrote:
> > 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 run=
s for ~7 seconds+. The requirement is to bring down the response time withi=
n ~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".
> > >=20
> > > Below is the query and its complete plan:-=C2=A0
> > > https://gist.github.com/databasetech0073/f564ac23ee35d1f0413980fe4d00=
efa9
> > >=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 col=
umn (request_id,created_at,event_comment_text) or should we create the cove=
ring index i.e. just on two column (request_id,created_at) with "include" c=
lause for "event_comment_text". How and when the covering index indexes sho=
uld be used here in postgres. Want to understand from experts?=C2=A0
> > > 2)Similarly for table orders:- Should we create a covering index on c=
olumn (entity_id,due_date,order_type) with include clause (firm_dspt_case_i=
d). Or just a composite index (entity_id,due_date,order_type).
> > > 3)Whether the column used as range operator (here created_at or due_d=
ate) should be used as leading column in the composite index or is it fine =
to keep it as non leading?
> > >=20
> > > -> =C2=A0Nested Loop =C2=A0(cost=3D50.06..2791551.71 rows=3D3148 widt=
h=3D19) (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=
=3D36729 width=3D8) (actual time=3D196.407..3805.755 rows=3D278131 loops=3D=
3)
> > > =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..10=
22872.54 rows=3D3672860 width=3D16) (actual time=3D139.883..3152.627 rows=
=3D2944671 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
> >=20
> > You are selecting a lot of rows, so the query will never be really chea=
p.
> > But I agree that an index scan should be a win.
> >=20
> > If the condition on "order_type" is always the same, a partial index is=
 ideal:
> >=20
> > =C2=A0 =C2=A0CREATE INDEX ON orders (due_date) WHERE order_type IN ('TY=
PE_A', 'TYPE_B');
> >=20
> > Otherwise, I'd create two indexes: one on "order_type" and one on "due_=
date".
>=20
> Version is 17.7. Below is the table definitions as i pulled from Dbeaver =
tool:-
>=20
> https://gist.github.com/databasetech0073/f22d95de18dc3f1fa54af13e7fd2ce9e=
=C2=A0
>=20
> The Order_type will be TYPE_A and TYPE_B in most of the cases. And below =
is the distribution.
> So , it looks like the index on this column will not help much. Correct m=
e if I'm wrong.
>=20
> TYPE_A  25 Million
> TYPE_B  2 Million
> TYPE_C  700K
> TYPE_D  200K
> TYPE_E  6k

No, you are right about that.

> I am wondering why the already=C2=A0existing index on column "due_date" o=
f table "order" is not
> getting used by the optimizer? Should we also add the column "entity_id" =
to the index too?

Seeing that your execution plan is incomplete, it is hard to say anything a=
bout that.
The scans of "entities" are missing, as is the UNION.

> And, Yes there are differences in data types of the "entity_id" for colum=
ns of table "order"
> and "entity". We need to fix that after analyzing the data.
>=20
> Also the highlighted Nested loop above shows ~10M shared hits (which will=
 be ~70GB+ if we
> consider one hit as an 8K block). So does that mean , apart from the Full=
 scan on the "order"
> table , the main resource consuming factor here is the scanning of "event=
_audit_log".

Correct.

Yours,
Laurenz Albe