I would definitely recommend to focus on this section:
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: 6572678You can certainly try composite index or partial index for order_type. Thank you, Nisarg On Sun, Feb 15, 2026, 3:51 PM Adrian Klaver <[email protected]> wrote: > 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 > [email protected] > > >
