我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
下面这个案例非常的经典，来至于朋友的生产环境，反馈SQL语句运行越来越慢。执行计划一看，妈啊，直接被吓着了，第一次看到PG中这么长的执行计划，下面我们就看看PG中怎么快速的分析SQL性能问题。
1，分析执行计划
SQL语句文本如下，典型的标量子查询的SQL语句，虽然整个SQL语句格式化出来比较长，但是SQL语句非常简单。
SELECT *
FROM
( SELECT sys_sett_date sysDatee,
cen_seq seq,
T.HTZZ_sys_Sett_Date AS sysSettDate,
T.txn_type AS txnType,
T.txn_St AS txnSt,
T.sett_date AS settDate,
T.unit_id AS unitId,
u.unit_sname unitName,
T.pos_id AS posId,
T.sam_id AS samId,
P.pos_name posName,
T.card_no AS cardNo,
T.txn_date AS txnDate,
T.txn_time AS txnTime,
T.txn_amt AS txnAmt,
T.org_amt AS orgAmt,
T.HTZZ_seq AS cenSeq,
txn_type || txn_St || '-' ||
( SELECT dsp
FROM t_par_txn_type_st
WHERE txn_type = T.txn_type
AND txn_st = T.txn_st) AS txnDsp,
( SELECT b.UP_LINE_ID
FROM T_HTZZ_HTZ b
WHERE b.sys_sett_date = T.HTZZ_sys_sett_date
AND B.CEN_SEQ = T.HTZZ_seq) upLineId,
( SELECT b.dn_LINE_ID
FROM T_HTZZ_HTZ b
WHERE b.sys_sett_date = T.HTZZ_sys_sett_date
AND B.CEN_SEQ = T.HTZZ_seq) dnLineId,
( SELECT b.UP_time
FROM T_HTZZ_HTZ b
WHERE b.sys_sett_date = T.HTZZ_sys_sett_date
AND B.CEN_SEQ = T.HTZZ_seq) upDttm,
( SELECT b.UP_STAT_ID
FROM T_HTZZ_HTZ b
WHERE b.sys_sett_date = T.HTZZ_sys_sett_date
AND B.CEN_SEQ = T.HTZZ_seq) upStatId,
( SELECT b.UP_STAT_NAME
FROM T_HTZZ_HTZ b
WHERE b.sys_sett_date = T.HTZZ_sys_sett_date
AND B.CEN_SEQ = T.HTZZ_seq) upStatName,
( SELECT b.dn_STAT_ID
FROM T_HTZZ_HTZ b
WHERE b.sys_sett_date = T.HTZZ_sys_sett_date
AND B.CEN_SEQ = T.HTZZ_seq) dnStatId,
( SELECT b.DN_STAT_NAME
FROM T_HTZZ_HTZ b
WHERE b.sys_sett_date = T.HTZZ_sys_sett_date
AND B.CEN_SEQ = T.HTZZ_seq) dnStatName,
'' upBusId,
T.err_cd AS errCd,
T.acq_inst_cd AS acqInstCd,
b.ISS_INST_NAME AS acqInstName
FROM T_DTL_HTZ T
LEFT JOIN t_bse_pos P ON T.sam_id = P.sam_id
LEFT JOIN t_bse_unit u ON T.unit_id = u.unit_id
LEFT JOIN T_BSE_ISS_ACQ_CMPT b ON b.ACQ_INST_CD = T.ACQ_INST_CD
WHERE 1 = 1
AND T.txn_Date >= '20230621'
AND T.txn_Date <= '20230621'
AND T.card_no IN ('3105170080004518536')
UNION ALL SELECT sys_sett_date sysDatee,
dtl_seq seq,
T.HTZZ_sys_Sett_Date AS sysSettDate,
T.txn_type AS txnType,
T.txn_St AS txnSt,
T.sett_date AS settDate,
T.unit_id AS unitId,
u.unit_sname unitName,
T.pos_id AS posId,
T.sam_id AS samId,
P.pos_name posName,
T.card_no AS cardNo,
T.txn_date AS txnDate,
T.txn_time AS txnTime,
T.txn_amt AS txnAmt,
T.org_amt AS orgAmt,
T.HTZZ_seq AS cenSeq,
txn_type || txn_St || '-' ||
( SELECT dsp
FROM t_par_txn_type_st
WHERE txn_type = T.txn_type
AND txn_st = T.txn_st) AS txnDsp,
( SELECT b.UP_LINE_ID
FROM T_HTZZ_HTZ b
WHERE b.sys_sett_date = T.HTZZ_sys_sett_date
AND B.CEN_SEQ = T.HTZZ_seq) upLineId,
( SELECT b.dn_LINE_ID
FROM T_HTZZ_HTZ b
WHERE b.sys_sett_date = T.HTZZ_sys_sett_date
AND B.CEN_SEQ = T.HTZZ_seq) dnLineId,
( SELECT b.UP_time
FROM T_HTZZ_HTZ b
WHERE b.sys_sett_date = T.HTZZ_sys_sett_date
AND B.CEN_SEQ = T.HTZZ_seq) upDttm,
( SELECT b.UP_STAT_ID
FROM T_HTZZ_HTZ b
WHERE b.sys_sett_date = T.HTZZ_sys_sett_date
AND B.CEN_SEQ = T.HTZZ_seq) upStatId,
'' upStatName,
( SELECT b.dn_STAT_ID
FROM T_HTZZ_HTZ b
WHERE b.sys_sett_date = T.HTZZ_sys_sett_date
AND B.CEN_SEQ = T.HTZZ_seq) dnStatId,
'' dnStatName,
( SELECT b.UP_BUS_ID
FROM T_HTZZ_HTZ b
WHERE b.sys_sett_date = T.HTZZ_sys_sett_date
AND B.CEN_SEQ = T.HTZZ_seq) upBusId,
T.err_cd AS errCd,
T.acq_inst_cd AS acqInstCd,
b.ISS_INST_NAME AS acqInstName
FROM T_HTZ_HUANG_CARD_DTL T
LEFT JOIN t_bse_pos P ON T.sam_id = P.sam_id
LEFT JOIN t_bse_unit u ON T.unit_id = u.unit_id
LEFT JOIN T_BSE_ISS_ACQ_CMPT b ON b.ACQ_INST_CD = T.ACQ_INST_CD
WHERE 1 = 1
AND T.txn_Date >= '20230621'
AND T.txn_Date <= '20230621'
AND T.card_no IN ('3105170080004518536'))
ORDER BY cardNo ASC,
txnDate ASC,
txnTime ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;
查看执行计划内容：
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=248337323.33..248337323.36 rows=15 width=567) (actual time=398722.449..398722.481 rows=4 loops=1)
-> Sort (cost=248337323.33..248337323.36 rows=15 width=567) (actual time=398722.446..398722.476 rows=4 loops=1)
Sort Key: t.card_no, t.txn_date, t.txn_time
Sort Method: quicksort Memory: 26kB
-> Append (cost=0.99..248337323.03 rows=15 width=565) (actual time=101491.712..398722.415 rows=4 loops=1)
-> Nested Loop Left Join (cost=0.99..41413968.65 rows=1 width=721) (actual time=101491.711..398721.796 rows=4 loops=1)
Join Filter: ((b.acq_inst_cd)::text = (t.acq_inst_cd)::text)
Rows Removed by Join Filter: 1728
-> Nested Loop Left Join (cost=0.99..1863.81 rows=1 width=269) (actual time=0.545..1.012 rows=4 loops=1)
Join Filter: ((t.unit_id)::text = (u.unit_id)::text)
Rows Removed by Join Filter: 1532
-> Nested Loop Left Join (cost=0.99..1838.64 rows=1 width=248) (actual time=0.318..0.432 rows=4 loops=1)
-> Index Scan using idx_t_dtl_HTZ_cardno_f2cf54bf on t_dtl_HTZ t (cost=0.57..1822.18 rows=1 width=224) (actual time=0.305..0.385 rows=4 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
Rows Removed by Filter: 57
-> Index Scan using pk_t_bse_pos_5bc5f933 on t_bse_pos p (cost=0.42..8.44 rows=1 width=37) (actual time=0.002..0.002 rows=0 loops=4)
Index Cond: ((sam_id)::text = (t.sam_id)::text)
-> Seq Scan on t_bse_unit u (cost=0.00..18.41 rows=541 width=29) (actual time=0.009..0.080 rows=384 loops=4)
-> Seq Scan on t_bse_iss_acq_cmpt b (cost=0.00..9.33 rows=433 width=27) (actual time=0.006..0.074 rows=433 loops=4)
SubPlan 1
-> Seq Scan on t_par_txn_type_st (cost=0.00..7.05 rows=1 width=20) (actual time=0.030..0.061 rows=1 loops=4)
Filter: ((txn_type = t.txn_type) AND (txn_st = t.txn_st))
Rows Removed by Filter: 269
SubPlan 2
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_2 (cost=0.57..5916011.86 rows=1 width=3) (actual time=14266.232..14266.232 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
SubPlan 3
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_3 (cost=0.57..5916011.86 rows=1 width=3) (actual time=14252.017..14252.017 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
SubPlan 4
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_4 (cost=0.57..5916011.86 rows=1 width=15) (actual time=14206.960..14206.960 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
SubPlan 5
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_5 (cost=0.57..5916011.86 rows=1 width=2) (actual time=14092.144..14092.144 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
SubPlan 6
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_6 (cost=0.57..5916011.86 rows=1 width=19) (actual time=14296.261..14296.261 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
SubPlan 7
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_7 (cost=0.57..5916011.86 rows=1 width=3) (actual time=14315.098..14315.098 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
SubPlan 8
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_8 (cost=0.57..5916011.86 rows=1 width=19) (actual time=14251.160..14251.161 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
-> Hash Left Join (cost=40.90..206923354.16 rows=14 width=533) (actual time=0.599..0.612 rows=0 loops=1)
Hash Cond: ((t_1.acq_inst_cd)::text = (b_1.acq_inst_cd)::text)
-> Hash Left Join (cost=26.15..3159.19 rows=14 width=227) (actual time=0.596..0.604 rows=0 loops=1)
Hash Cond: ((t_1.unit_id)::text = (u_1.unit_id)::text)
-> Nested Loop Left Join (cost=0.98..3133.98 rows=14 width=206) (actual time=0.594..0.599 rows=0 loops=1)
-> Append (cost=0.56..3015.85 rows=14 width=181) (actual time=0.592..0.595 rows=0 loops=1)
-> Index Scan using T_HTZ_HUANG_CARD_DTL_p001_card_no_idx on T_HTZ_HUANG_CARD_DTL_p001 t_1 (cost=0.56..239.49 rows=1 width=141) (actual time=0.051..0.052 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using T_HTZ_HUANG_CARD_DTL_p002_card_no_idx on T_HTZ_HUANG_CARD_DTL_p002 t_2 (cost=0.56..227.60 rows=1 width=141) (actual time=0.043..0.043 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using T_HTZ_HUANG_CARD_DTL_p003_card_no_idx on T_HTZ_HUANG_CARD_DTL_p003 t_3 (cost=0.56..224.85 rows=1 width=141) (actual time=0.042..0.042 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using T_HTZ_HUANG_CARD_DTL_p004_card_no_idx on T_HTZ_HUANG_CARD_DTL_p004 t_4 (cost=0.56..229.65 rows=1 width=141) (actual time=0.038..0.038 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using T_HTZ_HUANG_CARD_DTL_p005_card_no_idx on T_HTZ_HUANG_CARD_DTL_p005 t_5 (cost=0.56..253.06 rows=1 width=141) (actual time=0.039..0.039 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using T_HTZ_HUANG_CARD_DTL_p006_card_no_idx on T_HTZ_HUANG_CARD_DTL_p006 t_6 (cost=0.56..246.06 rows=1 width=198) (actual time=0.048..0.048 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using T_HTZ_HUANG_CARD_DTL_p007_card_no_idx on T_HTZ_HUANG_CARD_DTL_p007 t_7 (cost=0.56..231.29 rows=1 width=198) (actual time=0.047..0.047 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using T_HTZ_HUANG_CARD_DTL_p008_card_no_idx on T_HTZ_HUANG_CARD_DTL_p008 t_8 (cost=0.56..229.59 rows=1 width=141) (actual time=0.042..0.042 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using T_HTZ_HUANG_CARD_DTL_p009_card_no_idx on T_HTZ_HUANG_CARD_DTL_p009 t_9 (cost=0.56..251.06 rows=1 width=141) (actual time=0.042..0.042 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using T_HTZ_HUANG_CARD_DTL_p010_card_no_idx on T_HTZ_HUANG_CARD_DTL_p010 t_10 (cost=0.56..199.10 rows=1 width=141) (actual time=0.045..0.045 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using T_HTZ_HUANG_CARD_DTL_p011_card_no_idx on T_HTZ_HUANG_CARD_DTL_p011 t_11 (cost=0.56..216.61 rows=1 width=141) (actual time=0.041..0.042 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using T_HTZ_HUANG_CARD_DTL_p012_card_no_idx on T_HTZ_HUANG_CARD_DTL_p012 t_12 (cost=0.56..240.01 rows=1 width=141) (actual time=0.041..0.042 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using T_HTZ_HUANG_CARD_DTL_p013_card_no_idx on T_HTZ_HUANG_CARD_DTL_p013 t_13 (cost=0.56..219.27 rows=1 width=141) (actual time=0.045..0.045 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using T_HTZ_HUANG_CARD_DTL_default_card_no_idx on T_HTZ_HUANG_CARD_DTL_default t_14 (cost=0.14..8.16 rows=1 width=586) (actual time=0.017..0.017 rows=0 loops=1)
Index Cond: ((card_no)::text = '3105170080004518536'::text)
Filter: ((txn_date >= '20230621'::bpchar) AND (txn_date <= '20230621'::bpchar))
-> Index Scan using pk_t_bse_pos_5bc5f933 on t_bse_pos p_1 (cost=0.42..8.44 rows=1 width=37) (never executed)
Index Cond: ((sam_id)::text = (t_1.sam_id)::text)
-> Hash (cost=18.41..18.41 rows=541 width=29) (never executed)
-> Seq Scan on t_bse_unit u_1 (cost=0.00..18.41 rows=541 width=29) (never executed)
-> Hash (cost=9.33..9.33 rows=433 width=27) (never executed)
-> Seq Scan on t_bse_iss_acq_cmpt b_1 (cost=0.00..9.33 rows=433 width=27) (never executed)
SubPlan 9
-> Seq Scan on t_par_txn_type_st t_par_txn_type_st_1 (cost=0.00..7.05 rows=1 width=20) (never executed)
Filter: ((txn_type = t_1.txn_type) AND (txn_st = t_1.txn_st))
SubPlan 10
-> Index Scan using pk_T_HTZZ_HTZ_d3a0142e on T_HTZZ_HTZ b_9 (cost=0.57..2463334.30 rows=1 width=5) (never executed)
Index Cond: ((cen_seq)::text = (t_1.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.HTZZ_sys_sett_date)::text)
SubPlan 11
-> Index Scan using pk_T_HTZZ_HTZ_d3a0142e on T_HTZZ_HTZ b_10 (cost=0.57..2463334.30 rows=1 width=5) (never executed)
Index Cond: ((cen_seq)::text = (t_1.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.HTZZ_sys_sett_date)::text)
SubPlan 12
-> Index Scan using pk_T_HTZZ_HTZ_d3a0142e on T_HTZZ_HTZ b_11 (cost=0.57..2463334.30 rows=1 width=15) (never executed)
Index Cond: ((cen_seq)::text = (t_1.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.HTZZ_sys_sett_date)::text)
SubPlan 13
-> Index Scan using pk_T_HTZZ_HTZ_d3a0142e on T_HTZZ_HTZ b_12 (cost=0.57..2463334.30 rows=1 width=2) (never executed)
Index Cond: ((cen_seq)::text = (t_1.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.HTZZ_sys_sett_date)::text)
SubPlan 14
-> Index Scan using pk_T_HTZZ_HTZ_d3a0142e on T_HTZZ_HTZ b_13 (cost=0.57..2463334.30 rows=1 width=2) (never executed)
Index Cond: ((cen_seq)::text = (t_1.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.HTZZ_sys_sett_date)::text)
SubPlan 15
-> Index Scan using pk_T_HTZZ_HTZ_d3a0142e on T_HTZZ_HTZ b_14 (cost=0.57..2463334.30 rows=1 width=9) (never executed)
Index Cond: ((cen_seq)::text = (t_1.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.HTZZ_sys_sett_date)::text)
Planning Time: 87.307 ms
Execution Time: 398723.048 ms
实话实说PG中的执行计划的可观察性真心没有Oracle的好，估计后面还是需要自己写一个工具来做二次格式化，不然遇到上百行这种执行计划看起来真的有点吃力。
这里我们注意其中的几行关键内容，分别如下：
-> Nested Loop Left Join (cost=0.99..41413968.65 rows=1 width=721) (actual time=101491.711..398721.796 rows=4 loops=1)
SubPlan 1
-> Seq Scan on t_par_txn_type_st (cost=0.00..7.05 rows=1 width=20) (actual time=0.030..0.061 rows=1 loops=4)
Filter: ((txn_type = t.txn_type) AND (txn_st = t.txn_st))
Rows Removed by Filter: 269
SubPlan 2
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_2 (cost=0.57..5916011.86 rows=1 width=3) (actual time=14266.232..14266.232 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
SubPlan 3
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_3 (cost=0.57..5916011.86 rows=1 width=3) (actual time=14252.017..14252.017 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
SubPlan 4
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_4 (cost=0.57..5916011.86 rows=1 width=15) (actual time=14206.960..14206.960 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
SubPlan 5
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_5 (cost=0.57..5916011.86 rows=1 width=2) (actual time=14092.144..14092.144 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
SubPlan 6
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_6 (cost=0.57..5916011.86 rows=1 width=19) (actual time=14296.261..14296.261 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
SubPlan 7
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_7 (cost=0.57..5916011.86 rows=1 width=3) (actual time=14315.098..14315.098 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
SubPlan 8
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_8 (cost=0.57..5916011.86 rows=1 width=19) (actual time=14251.160..14251.161 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
这里标量子查询的可观察性真的太差了，这里注意Nested Loop Left Join返回4行，也就以为着下面的所有的Subplan都要执行4次，每次subplan的成本在上面有显示，比如SubPlan 8单词的成本和时间如下，(cost=0.57..5916011.86 rows=1 width=19) (actual time=14251.160..14251.161 rows=0 loops=4)，那么Subplan总的时间和成本为单次*4。将所有subplan的4次执行结果相加，会得到一个很大的数字，这就是执行计划慢的地方。
2，慢的原因分析
从执行计划中可以看到下面的数据：
SubPlan 2
-> Index Scan using pk_t_HTZZ_HTZ_b490c786 on t_HTZZ_HTZ b_2 (cost=0.57..5916011.86 rows=1 width=3) (actual time=14266.232..14266.232 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.HTZZ_seq)::text)
Filter: ((sys_sett_date)::text = (t.HTZZ_sys_sett_date)::text)
注意关键字Index Cond:这里表示subplan中走索引的信息。同时在标量子查询中，我们还知道标量子查询每次只会返回一行的数据，从rows=1 也可以看得出来，所以Index Cond+Filter最后返回的是一样数据，但是巨大的成本肯定就有异常，另外这里也需要注意在Index Cond和Filter中都发生了隐式转换，隐式转换后会导致索引失效，就会出现Filter的情况。查看表的索引信息，确定对应列上存在索引，所以解决方案很简单，手动指定转换即可。
3，模拟现象
下面我们模拟一下PG中的隐式转换导致索引不生效的情况，
3.1 创建表和插入数据
下面故意将member_id在两个表中定义成不同的列。
DROP TABLE IF EXISTS member_info;
DROP TABLE IF EXISTS order_info;
CREATE TABLE member_info (
member_id CHAR(8) PRIMARY KEY,
member_name TEXT
);
CREATE TABLE order_info (
order_id SERIAL PRIMARY KEY,
member_id VARCHAR(8),
order_amount NUMERIC
);
INSERT INTO member_info(member_id, member_name)
SELECT LPAD('M' || i::text, 8, '0'), 'Member ' || i
FROM generate_series(1, 100000) AS i;
INSERT INTO order_info(member_id, order_amount)
SELECT LPAD('M' || (i % 100000 + 1)::text, 8, '0'), random() * 100
FROM generate_series(1, 100000) AS i;
CREATE INDEX idx_order_info_member_id ON order_info(member_id);
在order_info的member_id上创建索引。
3.2 SQL隐式转换
下面SQL语句返回一行的数据，正常情况下走NL且被驱动表走索引时最好的执行计划。
EXPLAIN ANALYZE
SELECT m.member_id, COUNT(*) AS order_count
FROM member_info m
JOIN order_info o
ON m.member_id = o.member_id
where m.member_name='Member 1'
GROUP BY m.member_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=3877.53..3877.55 rows=1 width=17) (actual time=24.982..24.984 rows=1 loops=1)
Group Key: m.member_id
-> Sort (cost=3877.53..3877.54 rows=1 width=9) (actual time=24.977..24.978 rows=1 loops=1)
Sort Key: m.member_id
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=1887.01..3877.52 rows=1 width=9) (actual time=24.962..24.963 rows=1 loops=1)
Hash Cond: ((o.member_id)::bpchar = m.member_id)
-> Seq Scan on order_info o (cost=0.00..1728.00 rows=100000 width=9) (actual time=0.012..5.619 rows=100000 loops=1)
-> Hash (cost=1887.00..1887.00 rows=1 width=9) (actual time=11.057..11.057 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on member_info m (cost=0.00..1887.00 rows=1 width=9) (actual time=0.014..11.050 rows=1 loops=1)
Filter: (member_name = 'Member 1'::text)
Rows Removed by Filter: 99999
Planning Time: 0.565 ms
Execution Time: 25.047 ms
(15 rows)
Hash Cond: ((o.member_id)::bpchar = m.member_id)
执行计划这一行表示了隐式转换，且执行计划走了HASH JOIN，驱动表也表位了order_info。
3.3 创建函数索引
隐式转换最简单的就是创建函数索引了，如下所示：
创建索引：
CREATE INDEX idx_order_info_member_id_char
ON order_info ((member_id::char(4)));
查看执行计划：
htz=# EXPLAIN ANALYZE
SELECT m.member_id, COUNT(*) AS order_count
FROM member_info m
JOIN order_info o
ON m.member_id = o.member_id
where m.member_name='Member 1'
GROUP BY m.member_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=3877.53..3877.55 rows=1 width=17) (actual time=26.949..26.951 rows=1 loops=1)
Group Key: m.member_id
-> Sort (cost=3877.53..3877.54 rows=1 width=9) (actual time=26.946..26.947 rows=1 loops=1)
Sort Key: m.member_id
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=1887.01..3877.52 rows=1 width=9) (actual time=26.907..26.908 rows=1 loops=1)
Hash Cond: ((o.member_id)::bpchar = m.member_id)
-> Seq Scan on order_info o (cost=0.00..1728.00 rows=100000 width=9) (actual time=0.021..5.530 rows=100000 loops=1)
-> Hash (cost=1887.00..1887.00 rows=1 width=9) (actual time=13.416..13.416 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on member_info m (cost=0.00..1887.00 rows=1 width=9) (actual time=0.025..13.383 rows=1 loops=1)
Filter: (member_name = 'Member 1'::text)
Rows Removed by Filter: 99999
Planning Time: 0.608 ms
Execution Time: 27.052 ms
(15 rows)
执行计划没有变，任为HASH JOIN方式。
htz=# CREATE INDEX idx_order_info_member_id_bpchar
ON order_info ((member_id::bpchar));
CREATE INDEX
htz=# EXPLAIN ANALYZE
SELECT m.member_id, COUNT(*) AS order_count
FROM member_info m
JOIN order_info o
ON m.member_id = o.member_id
where m.member_name='Member 1'
GROUP BY m.member_id;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1890.06..1890.08 rows=1 width=17) (actual time=10.033..10.033 rows=1 loops=1)
Group Key: m.member_id
-> Sort (cost=1890.06..1890.06 rows=1 width=9) (actual time=10.026..10.027 rows=1 loops=1)
Sort Key: m.member_id
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.42..1890.05 rows=1 width=9) (actual time=0.493..10.015 rows=1 loops=1)
-> Seq Scan on member_info m (cost=0.00..1887.00 rows=1 width=9) (actual time=0.016..9.536 rows=1 loops=1)
Filter: (member_name = 'Member 1'::text)
Rows Removed by Filter: 99999
-> Index Scan using idx_order_info_member_id_bpchar on order_info o (cost=0.42..3.03 rows=1 width=9) (actual time=0.474..0.475 rows=1 loops=1)
Index Cond: ((member_id)::bpchar = m.member_id)
Planning Time: 1.696 ms
Execution Time: 10.105 ms
(13 rows)
这里执行计划成功的改为NL方式，被驱动表也走索引了。
上面创建函数索引时需要注意，函数索引中的列转换的类型需要跟执行计划中提示的一样，否则无法生效。
3.5 SQL语句中指定列转换模式
这里手动的将char转换为varchar类型（m.member_id::varchar = o.member_id）
htz=# EXPLAIN ANALYZE
SELECT m.member_id, COUNT(*) AS order_count
FROM member_info m
JOIN order_info o
ON m.member_id::varchar = o.member_id
where m.member_name='Member 1'
GROUP BY m.member_id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1888.76..1888.78 rows=1 width=17) (actual time=11.402..11.404 rows=1 loops=1)
Group Key: m.member_id
-> Sort (cost=1888.76..1888.76 rows=1 width=9) (actual time=11.391..11.393 rows=1 loops=1)
Sort Key: m.member_id
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.42..1888.75 rows=1 width=9) (actual time=1.365..11.374 rows=1 loops=1)
-> Seq Scan on member_info m (cost=0.00..1887.00 rows=1 width=9) (actual time=0.016..10.023 rows=1 loops=1)
Filter: (member_name = 'Member 1'::text)
Rows Removed by Filter: 99999
-> Index Only Scan using idx_order_info_member_id on order_info o (cost=0.42..1.74 rows=1 width=9) (actual time=1.343..1.344 rows=1 loops=1)
Index Cond: (member_id = ((m.member_id)::character varying)::text)
Heap Fetches: 0
Planning Time: 0.264 ms
Execution Time: 11.474 ms
(14 rows)
执行计划也从HASH JOIN改成了NL的方式，执行时间也从原来的25MS将到11MS。
4 总结
到这里简单的展示了PG列隐式转换的分析和对应的解决方案，关于PG支持哪些隐式转换，可以直接去PG数据库里面查询即可。建议在表设计时尽量保持相同的列在所有的表中类型一致，不然很可能随时带来隐式转换，影响SQL执行的性能。
——————作者介绍———————–
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
PG优化系列：列隐式转换导致SQL性能下降的案例分析：等您坐沙发呢！