pg mysql join 性能_pgsql_sql查询效率优化

在pgsql中执行一个 5表 关联查询，效率比较差，问题定位

环境说明

5张外表，其中with 中的临时表总记录数比较大，共有 2 亿条记录，通过时间序模型提高查询速度

另外4张表 左表的记录非常小，最大的记录数不超过 1w 条

在没有做过任何调优的pgsql 中执行explain，会发现它的访问计划中包含很多的 nested loop join

Aggregate (cost=99723528.30..99723528.31 rows=1 width=0)

CTE f_acct_vchr_1_tmp-> Foreign Scan on hdmp_pri5_fdm_f_acct_vchr vo_1 (cost=0.00..99722420.16 rows=1 width=1448)

Filter: ((posting_dt>= '2015-12-01'::date) AND (posting_dt <= '2015-12-31'::date) AND (trans_no ~~ '301%'::text) AND(a

mt= 1000::double precision) AND ((posting_flg = 'Y'::text) OR (gl_acc_id = '99900'::text)))ForeignNamespace: hdmp_pri5_fdm.f_acct_vchr-> Nested Loop Left Join (cost=0.00..1108.15 rows=1 width=0)Join Filter: (vo.calc_trans_action =d3.trans_action_cd)-> Nested Loop Left Join (cost=0.00..902.53 rows=1 width=32)Join Filter: (vo.trans_action_cd =d2.trans_action_cd)-> Nested Loop Left Join (cost=0.00..696.92 rows=1 width=64)Join Filter: (vo.fund_tnl_cd =f1.prod_cd)-> Nested Loop Left Join (cost=0.00..360.10 rows=1 width=96)Join Filter: (vo.calc_unit_id =u1.calc_unit_id)-> Nested Loop Left Join (cost=0.00..352.15 rows=1 width=104)Join Filter: (vo.modl_id =d1.modl_id)-> Nested Loop Left Join (cost=0.00..336.84 rows=1 width=112)Join Filter: (vo.prod_cd =p.prod_cd)-> CTE Scan on f_acct_vchr_1_tmp vo (cost=0.00..0.02 rows=1 width=144)-> Foreign Scan on d_prod p (cost=0.00..336.22 rows=48 width=32)

Filter: (eff_flg= 'Y'::text)ForeignNamespace: hdmp_pri5_fdm.d_prod-> Foreign Scan on d_modl d1 (cost=0.00..13.36 rows=156 width=8)ForeignNamespa