Does the filter on "due_date" eliminate many rows in "orders"? If yes, and an index on that column would actually perform better (which you could test with enable_seqscan = off), perhaps your "random_page_cost" parameter is set too high.
Where you can certainly make a difference is the repeated scan on "event_audit_log". An index on (request_id, event_comment_text, created_at) should speed up that part.
Yours, Laurenz Albe
Thank you so much. Will try this one.
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 keeping "date column"(like column "Created_at" here) in the indexing order (apart from the frequency of usage in the query)?
And to help the table scan of the ORDER table, should we also have "entity_id" added to the index along with "due_date" i.e. a composite index on (entity_id,due_date)?
