pgsql连表查询count慢

最近在使用tdsql pg版时发现一个问题，因为查询语句需要进行分页，所以mybatis会自动进行一次count获取总条数来统计页数等参数，但是进行连表后一个简单的count都需要4-5秒的时间，单表count很快，连表一对一也很快，但是遇到一对多的情况就会很慢。

慢sql分析

select count(1)
from policy_list m
         left join igis_yw_gu_policy_risk_relatedparty r
                   on r.policy_no = m.policy_no and r.sub_policy_no = m.sub_policy_no and
                      r.endor_seq_no = m.endor_seq_no and r.item_no = m.item_no
WHERE (m.start_date <= '2025-07-14'::date and m.end_date >= '2025-07-14'::date);

其中policy_list一条会对应多条igis_yw_gu_policy_risk_relatedparty 因为一个保单至少会有一个或多个被保人。最终执行时间 5s 83ms 执行计划如下：

我们发现，如果是一对一关联，或者是多对一则不会有merge join的过程 脚本如下：

select count(1)
from policy_list m
         left join igis_yw_gu_policy_relatedparty r
                   on r.policy_no = m.policy_no and
                      r.endor_seq_no = m.endor_seq_no
WHERE (m.start_date <= '2025-07-14'::date and m.end_date >= '2025-07-14'::date);

由于一个保单只会有一个被保人 而policy_list是以标的纬度为主，所以是policy_list中同一个保单多个标的会对应一个igis_yw_gu_policy_relatedparty表里的数据。最终执行时间：359ms 执行计划如下：

同时我们发现如果直接进行分页查询不使用count进行统计的话也是非常快的，脚本如下：

select m.*
from policy_list m
         left join igis_yw_gu_policy_risk_relatedparty r
                   on r.policy_no = m.policy_no and r.sub_policy_no = m.sub_policy_no and
                      r.endor_seq_no = m.endor_seq_no and r.item_no = m.item_no
WHERE (m.start_date <= '2025-07-14'::date and m.end_date >= '2025-07-14'::date) limit 15;

执行时间：343ms 执行计划如下：

这里发现其实也进行了连表循环 也就是Nested Loop，但是由于数据量非常小只取了15条所以很快，如果不分页两表连接有28万数据循环就会很慢

优化

由于查询所需条件的字段都有索引，所以无法通过加索引来加速查询，百度也搜索了很多关于pgsql统计慢的帖子 但是都没有好的解决方案，要么是加函数要么是查询pg_class模糊统计，但是都不符合我们想要的效果。

最终我们选择了以空间换时间的方案，将我们接口需要查询和返回的字段都放到同一张表中作为宽表，单独查询一张表数据就会非常快，并且我们的业务数据不会频繁的进行变更，所以我们使用到了postgresql中的一个特性 “物化视图”。

“物化视图”实际上也属于视图的一种，但是视图只存储需要执行的sql，而物化视图则是将sql执行的数据存储起来，相当于一张表，后续查询时直接返回物化视图中存储的数据无需再次访问业务表，并且物化视图支持添加索引，需要刷新数据时仅需使用REFRESH MATERIALIZED VIEW 即可刷新整张表数据，无需像常规业务表一样需要使用 truncate table 或者 delete 先删除数据再使用insert 插入数据，维护起来更为简单。

创建物化视图

我们将所有需要放到where中的条件和需要返回的字段都进行连表查询，最终脚本如下：

create materialized view policy_list_ext as
select m.policy_no,
       m.endor_seq_no,
       m.sub_policy_no,
       m.item_no,
       m.address_flag,
       m.geographical_coordinates_lon,
       m.geographical_coordinates_lat,
       m.gemo,
       m.sum_insured,
       m.sum_pricetax_total,
       m.risk_code,
       k.riskcname as riskName,
       m.class_code,
       c.classcname,
       m.company_code,
       m.start_date,
       m.end_date,
       m.branch_company_code,
       m.branch_company_name,
       m.suppor_company_code,
       m.suppor_company_name,
       m.item_province_code,
       m.item_province_name,
       m.item_city_code,
       m.item_city_name,
       m.item_district_code,
       m.item_district_name,
       m.address,
       m.o_item_province_code,
       m.o_item_province_name,
       m.o_item_city_code,
       m.o_item_city_name,
       m.o_item_district_code,
       m.o_item_district_name,
       m.o_address,
       m.score,
       m.state,
       m.valid_ind,
       m.create_date,
       m.update_date,
       m.domestic_foreign_flag,
       i.item_country_area,
       gc.countrycname,
       y.insured_name as appliName,
       r.insured_name
from policy_list m
         left join igis_yw_gu_policy_relatedparty y
                   on m.policy_no = y.policy_no and m.endor_seq_no = y.endor_seq_no
         left join hx_gg_risk k on k.riskcode = m.risk_code
    left join hx_gg_risk_class c on c.riskclass = m.class_code
         left join igis_yw_gu_policy_risk_relatedparty r
                   on m.policy_no = r.policy_no and m.endor_seq_no = r.endor_seq_no and m.sub_policy_no = r.sub_policy_no and
                      m.item_no = r.item_no
         left join igis_yw_gu_policy_item i
                   on m.policy_no = i.policy_no and m.endor_seq_no = i.endor_seq_no and m.sub_policy_no = i.sub_policy_no and
                      m.item_no = i.item_no
         LEFT JOIN hx_gg_country gc
                   ON i.item_country_area = gc.countrycode
                       AND gc.validind = '1';

新增索引

物化视图新增索引与常规表新增索引无异，只需使用create index 语法即可，但是要注意，物化视图中没有主键的概念，如果你需要 也可以创建唯一索引。

create index idx_policy_list_ext_rc
    on policy_list_ext (risk_code);

create index policy_list_ext_branch_company_code_index
    on policy_list_ext (branch_company_code);

create index policy_list_ext_class_code_index
    on policy_list_ext (class_code);

create index policy_list_ext_create_date_index
    on policy_list_ext (create_date);

create index policy_list_ext_domestic_foreign_flag_index
    on policy_list_ext (domestic_foreign_flag);

create index policy_list_ext_end_date_index
    on policy_list_ext (end_date);

create index policy_list_ext_geom
    on policy_list_ext using gist(gemo);

create index policy_list_ext_score_index
    on policy_list_ext (score);

create index policy_list_ext_start_date_index
    on policy_list_ext (start_date);

create index policy_list_ext_state_index
    on policy_list_ext (state);

create index policy_list_ext_suppor_company_code_index
    on policy_list_ext (suppor_company_code);

create index policy_list_ext_update_date_index
    on policy_list_ext (update_date);

create index policy_list_ext_valid_ind_index
    on policy_list_ext (valid_ind);

create index policy_list_ext_item_country_area_index
    on policy_list_ext (item_country_area);

create index policy_list_ext_company_code_index
    on policy_list_ext (company_code);

验证

最后我们将新生成的物化视图连接其他表进行查询验证，脚本如下：

SELECT COUNT(*) AS total
FROM policy_list_ext m
         LEFT JOIN igis_yw_gu_item_reference ir ON ir.address_flag = m.address_flag AND ir.policy_no = m.policy_no AND
                                                   ir.sub_policy_no = m.sub_policy_no AND ir.item_no = m.item_no AND
                                                   ir.endor_seq_no = m.endor_seq_no
         LEFT JOIN igis_gg_code g
                   ON g.codetype = 'MARITTIME_ITEM' AND g.codecode = ir.maritime_item AND g.companycode = '03' AND
                      g.validind = '1'
WHERE (m.start_date <= '2025-07-14'::date AND m.end_date >= '2025-07-14'::date);

最终执行时间：594ms 执行计划如下：

如果业务数据会频繁发生变动，则需要寻找其他解决方案。