I’ve noticed that my query execution time increases significantly when I add filters such as visit_date or sort by ib.id. It takes around 2 minutes to execute. Here is the code:

SELECT *
FROM (
SELECT ib.id,
    ben.uid,
    ib.pid,
    ben.first_name,
    ben.middle_name,
    ben.last_name,
    iv.visit_date, 
    ben.id AS beneficiary_id,
    ben.mobile_number,
    ben.age,
    iv.is_pregnant,
    itr.tested_date,
    ben.hiv_status_id AS hiv_status,
    ben.hiv_type_id AS hiv_type,
    ben.date_of_birth,
    itr.result_status,
    ib.recent_visit_id AS visit_id,
    ib.beneficiary_status,
    ben.gender_id,
    ib.is_active AS ictc_ben_is_active,
    ib.is_deleted AS ictc_ben_is_deleted,
    ib.deleted_reason AS ictc_ben_deleted_reason,
    ib.deleted_reason_comment AS ictc_ben_deleted_reason_comment,
    ib.facility_id AS registered_facility_id,
    ibs.name AS beneficiary_status_desc,
    hs.name AS hiv_status_desc, 
    ib.infant_code as infant_code  
 FROM soch.ictc_beneficiary ib 
  JOIN soch.beneficiary ben ON ib.beneficiary_id = ben.id 
  LEFT JOIN soch.ictc_test_result itr ON ib.current_test_result_id = itr.id
  LEFT JOIN soch.ictc_visit iv ON ib.recent_visit_id = iv.id
  LEFT JOIN soch.master_hiv_status hs ON ben.hiv_status_id = hs.id
  LEFT JOIN soch.master_ictc_beneficiary_status ibs ON ib.beneficiary_status = ibs.id
where ib.facility_id = 13649 and ben.category_id <>1 
and  ben.is_active = true --and (ben.is_delete = false or ben.is_delete = null)
and (ben.benf_search_str like '%%' or ib.pid like '%%') 
) AS ordered_data where ordered_data.visit_date >= '2024-04-15'
limit 10;

Here is Explain Analyse of the Query:

    Node Type   Entity  Cost    Rows    Time    Condition
Limit   [NULL]  2.26 - 2784.90  10  57874.052   [NULL]
Nested Loop [NULL]  2.26 - 33393.90 10  57874.037   [NULL]
Nested Loop [NULL]  2.26 - 33287.67 10  57873.995   [NULL]
Nested Loop [NULL]  2.26 - 33278.41 10  57873.941   [NULL]
Nested Loop [NULL]  1.69 - 32947.84 10  57686.039   [NULL]
Nested Loop [NULL]  1.13 - 32613.39 10  57599.678   [NULL]
Index Scan  ictc_beneficiary    0.56 - 9424.30  7068    14378.517   (facility_id = 13649)
Index Scan  ictc_visit  0.56 - 2.76 0   6.114   (id = ib.recent_visit_id)
Index Scan  beneficiary 0.56 - 2.78 1   8.631   (id = ib.beneficiary_id)
Index Scan  ictc_test_result    0.56 - 2.75 1   18.786  (id = ib.current_test_result_id)
Materialize [NULL]  0.00 - 1.07 1   0.003   [NULL]
Seq Scan    master_hiv_status   0.00 - 1.05 1   0.014   [NULL]
Materialize [NULL]  0.00 - 1.88 3   0.002   [NULL]
Seq Scan    master_ictc_beneficiary_status  0.00 - 1.59 3   0.007   [NULL]

I have an index at facility_id as well as at visit_data. Any idea, I am struggling with this since many days