PostgreSQL 数据库 SQL 语句优化：提升性能的技巧

本文将优化以下 PostgreSQL SQL 语句：

with issue as (select cmm_id,string_agg(l3_flag, ';') as l3_flag,
string_agg(care_id, ';') as care_id,
string_agg(care_name, ';') as care_name,
string_agg(ump_id, ';') as issue_id  from t_issue_track_summary_info  where deleted is null group by cmm_id) 
Select collect.id,
case when issue.l3_flag is null then '' else issue.l3_flag End as L3flag,
issue.care_id,
case when issue.care_name is null then '' else issue.care_name End as CareName,
issue.issue_id,
collect.patch_plan,
collect.patch_id,
collect.cur_step,
collect.turns,
collect.cur_test,
...
from t_dts_collect collect
left join t_patch_info patch on collect.patch_id=patch.id
left join t_dts_details details on collect.dts_biz_no = details.dts_biz_no
left join t_dts_details details_parent on details.parent_no = details_parent.dts_biz_no
left join issue on collect.dts_biz_no = issue.cmm_id
where collect.is_deleted = 0
and collect.version_id = '23735486'
and collect.patch_plan in ('CP2118')
order by details.create_time DESC 
limit 200

优化方法

1. 索引优化

   为经常用到的字段添加索引，可以加快查询速度。建议为以下字段添加索引：

   • t_issue_track_summary_info 表的 deleted 字段
   • t_dts_collect 表的 is_deleted 字段、version_id 字段和 patch_plan 字段
   • t_patch_info 表的 id 字段
   • t_dts_details 表的 create_time 字段、dts_biz_no 字段和 parent_no 字段

2. 子查询优化

   将子查询中的聚合操作移至主查询中，避免使用 WITH 语句。修改后的 SQL 语句如下：

   SELECT collect.id,
       CASE WHEN issue.l3_flag IS NULL THEN '' ELSE issue.l3_flag END AS L3flag,
       issue.care_id,
       CASE WHEN issue.care_name IS NULL THEN '' ELSE issue.care_name END AS CareName,
       issue.issue_id,
       collect.patch_plan,
       collect.patch_id,
       collect.cur_step,
       collect.turns,
       collect.cur_test,
       ...
   FROM t_dts_collect collect
   LEFT JOIN t_patch_info patch ON collect.patch_id = patch.id
   LEFT JOIN t_dts_details details ON collect.dts_biz_no = details.dts_biz_no
   LEFT JOIN t_dts_details details_parent ON details.parent_no = details_parent.dts_biz_no
   LEFT JOIN (
       SELECT cmm_id,
           string_agg(l3_flag, ';') AS l3_flag,
           string_agg(care_id, ';') AS care_id,
           string_agg(care_name, ';') AS care_name,
           string_agg(ump_id, ';') AS issue_id
       FROM t_issue_track_summary_info
       WHERE deleted IS NULL
       GROUP BY cmm_id
   ) issue ON collect.dts_biz_no = issue.cmm_id
   WHERE collect.is_deleted = 0
       AND collect.version_id = '23735486'
       AND collect.patch_plan IN ('CP2118')
   ORDER BY details.create_time DESC
   LIMIT 200
   

   通过以上优化措施，可以提高查询的执行效率。请根据实际情况选择适合的优化方法。