PostgreSQL查询添加过滤/排序后执行缓慢（耗时约2分钟）求助

当添加visit_date过滤条件或按ib.id排序时，PostgreSQL查询执行时间大幅增加至约2分钟。已为facility_id和visit_date字段创建索引，但问题仍未解决，寻求优化思路。

查询代码 #

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.benf_search_str like '%%' OR ib.pid like '%%') 
) AS ordered_data 
WHERE ordered_data.visit_date >= '2024-04-15'
LIMIT 10;

Explain Analyze结果 #

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]

补充信息 #

查询的explain(analyze, verbose, buffers, settings)结果截图内容：查询执行计划详情

优化思路 #

• 调整过滤条件位置：将visit_date >= '2024-04-15'移至内层查询的LEFT JOIN soch.ictc_visit iv条件中，直接在关联时过滤数据，避免先生成包含7068条记录的中间结果集再过滤，减少内存占用和数据处理量。修改后的关联部分示例：

  LEFT JOIN soch.ictc_visit iv ON ib.recent_visit_id = iv.id AND iv.visit_date >= '2024-04-15'
  

• 创建针对性联合索引：
  • 在ictc_beneficiary表上创建(facility_id, beneficiary_id, recent_visit_id, current_test_result_id)联合索引，覆盖查询中用到的关联和过滤字段，避免回表；
  • 在ictc_visit表上创建(id, visit_date)覆盖索引，满足关联id和过滤visit_date的需求，无需回表查询；
  • 在beneficiary表上创建(id, category_id, is_active)联合索引，匹配关联和过滤条件。
• 清理无效模糊查询：ben.benf_search_str like '%%' OR ib.pid like '%%'逻辑上等同于无过滤条件，且会导致索引失效。如果是动态生成的查询，当无搜索关键词时应移除该条件，避免触发不必要的全表扫描。
• 更新统计信息：执行以下命令更新表统计信息，让PostgreSQL生成更准确的执行计划：

  ANALYZE soch.ictc_beneficiary;
  ANALYZE soch.ictc_visit;
  ANALYZE soch.beneficiary;
  

• 尝试切换连接类型：当前执行计划使用Nested Loop，当关联数据量较大时Hash Join可能更高效。可在会话级别临时关闭Nested Loop测试性能：

  SET enable_nestloop = off;
  

  测试后若有效，可考虑通过调整random_page_cost等参数引导优化器选择更优连接方式，不建议长期全局禁用Nested Loop。
• **避免SELECT ***：子查询中使用SELECT *会返回所有字段，增加数据传输和内存消耗。应明确指定需要的字段，减少中间结果集大小。

内容的提问来源于stack exchange，提问作者Neeraj Mehta