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

On Mon, 2026-02-16 at 17:52 +0530, yudhi s wrote:
> Regarding the composite index on (request_id, event_comment_text, created=
_at) for
> table event_audit_log, is there any advice, which we should follow for ke=
eping
> "date column"(like column "Created_at" here) in the indexing order (apart=
 from
> the frequency of usage in the query)?

The rule is to first have all columns that are compared with equality, then=
 the
others, starting with the most selective one.  Based on the condition, I gu=
essed
that that wouldn't be your "date" column, but if it is, put it second.

> And to help the table scan of the ORDER table, should we also have "entit=
y_id"
> added to the index along with "due_date" i.e. a composite index on (entit=
y_id,due_date)?

I don't know; you'd have to test it on your test system.
The current execution plan has no use for such an index, but adding additio=
nal
columns could

1. lead to an efficient index-only scan

2. make PostgreSQL pick an altogether different, better plan

Yours,
Laurenz Albe