Feedback-ID: i76984098:Fastmail
Message-ID: <a636b1aa-8ff7-44de-ada9-25ebde3aab15@aklaver.com>
Date: Sun, 15 Feb 2026 12:51:31 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Question on execution plan and suitable index
To: yudhi s <learnerdatabase99@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
References: <CAEzWdqeGj9FcubNXegJ8PGTnXNahUhgc6T+yNFW7O12EkKR9yA@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAEzWdqeGj9FcubNXegJ8PGTnXNahUhgc6T+yNFW7O12EkKR9yA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/a636b1aa-8ff7-44de-ada9-25ebde3aab15%40aklaver.com>
Precedence: bulk

On 2/15/26 11:04, yudhi s wrote:
> 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".

For a start:

1) Supply the complete schema for the tables involved.

2) Also what is the minor version you are using e.g the x in 17.x?

I also recommend reading:

https://wiki.postgresql.org/wiki/Slow_Query_Questions

> 
> *Below is the query and its complete plan:- *
> https://gist.github.com/databasetech0073/ 
> f564ac23ee35d1f0413980fe4d00efa9 <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


-- 
Adrian Klaver
adrian.klaver@aklaver.com