KingbaseES数据库-生产环境慢查询性能优化案例
一、背景
在生产环境中，慢查询不仅影响系统、业务的正常处理。同时严重影响用户的使用感受。
二、表相关信息
2.1 表结构及索引：
Table "yktcore.t_dtl_ymt"
栏位 | 类型 | 校对规则 | 可空的 | 预设
--------------------+-----------------------------+----------+----------+--------------
sys_sett_date | character(8 char) | | not null |
cen_seq | character varying(10 char) | | not null |
sett_date | character(8 char) | | |
chnl_uk | character varying(100 char) | | |
trans_uk | character varying(100 char) | | |
chnl_id | character varying(2 char) | | |
unit_id | character varying(11 char) | | |
unit_name | character varying(100 char) | | |
mchnt_id | character varying(15 char) | | |
mchnt_name | character varying(100 char) | | |
pos_id | character varying(20 char) | | |
sam_id | character varying(12 char) | | |
pos_name | character varying(100 char) | | |
acq_inst_cd | character varying(11 char) | | |
card_no | character varying(20 char) | | |
card_org | character varying(3 char) | | |
iss_inst_cd | character varying(16 char) | | |
txn_type | character(2 char) | | |
txn_st | character(2 char) | | |
inn_txn_type | character varying(8 char) | | |
txn_date | character(8 char) | | |
txn_time | character(6 char) | | |
txn_amt | numeric(10,0) | | |
org_amt | numeric(10,0) | | |
fee | numeric(10,0) | | |
pos_seq | character varying(10 char) | | |
order_no | character varying(50 char) | | |
pay_unit_id | character varying(11 char) | | |
pay_unit_name | character varying(100 char) | | |
pay_mchnt_id | character varying(15 char) | | |
pay_mchnt_name | character varying(100 char) | | |
pay_st | character varying(2 char) | | |
pay_seq | character varying(100 char) | | |
plat_seq | character varying(32 char) | | |
deduct_date | character varying(8 char) | | |
deduct_time | character varying(6 char) | | |
match_flg | character(1 char) | | |
pkg_id | character varying(12 char) | | |
txn_order | numeric(10,0) | | |
test_flg | character(2 char) | | |
err_cd | character varying(6 char) | | |
inn_err | character varying(6 char) | | |
cust_no | character varying(20 char) | | |
adj_flg | numeric(4,0) | | |
fund_src | character varying(2 char) | | |
spec_type | character varying(4 char) | | |
spec_sys_sett_date | character varying(8 char) | | |
spec_seq | character varying(12 char) | | |
biz_attr | character(2 char) | | |
biz_amt | numeric(10,0) | | |
part_no | numeric(3,0) | | not null |
rsvd | character varying(10 char) | | |
fmf_chnl_id | character varying(8 char) | | |
qr_chnl_id | character varying(8 char) | | |
user_type | character varying(2 char) | | |
medium_type | character varying(2 char) | | |
pay_acc_no | character varying(20 char) | | |
pay_type | numeric(4,0) | | |
opera_date | character varying(8 char) | | |
orig_txn_amt | numeric(10,0) | | |
orig_plat_seq | character varying(32 char) | | |
orig_fmf_chnl_id | character varying(8 char) | | |
orig_qr_chnl_id | character varying(8 char) | | |
match_type | character varying(2 char) | | |
fare_type | character varying(2 char) | | |
ticket_type | character varying(4 char) | | |
orig_opera_date | character varying(8 char) | | |
if_rtn | character varying(1 char) | | | 'N'::varchar
tot_rtn_amt | numeric(10,0) | | | 0
match_cnt | numeric(4,0) | | | 0
fvr_amt | numeric(10,0) | | | 0
actl_amt | numeric(10,0) | | |
fvr_mode | numeric(2,0) | | | 0
fvr_type1 | numeric(4,0) | | | 0
fvr_type2 | numeric(4,0) | | | 0
fvr_type3 | numeric(4,0) | | | 0
fvr_amt1 | numeric(10,0) | | | 0
fvr_amt2 | numeric(10,0) | | | 0
fvr_amt3 | numeric(10,0) | | | 0
Indexes：
"pk_t_dtl_ymt_d281c30c" PRIMARY KEY, btree (sys_sett_date, cen_seq, part_no)
"idx_uk_t_dtl_ymt_198a4137" UNIQUE, btree (trans_uk, part_no)
"idx_t_dtl_ymt_1" btree (sett_date)
"idx_t_dtl_ymt_cardno_f2cf54bf" btree (card_no)
"idx_uk_t_dtl_ymt_1_38290b9d" btree (plat_seq)
Table "yktcore.t_spec_ymt"
栏位 | 类型 | 校对规则 | 可空的 | 预设
----------------------+------------------------------+----------+----------+------
sys_sett_date | character(8 char) | | not null |
cen_date | character varying(8 char) | | |
cen_seq | character varying(10 char) | | not null |
txn_type | character(2 char) | | |
txn_st | character(2 char) | | |
pay_unit | character varying(8 char) | | |
pay_mchnt | character varying(12 char) | | |
up_time | character varying(14 char) | | |
up_line_id | character varying(12 char) | | |
up_stat_id | character varying(12 char) | | |
up_stat_name | character varying(30 char) | | |
up_pos_id | character varying(10 char) | | |
up_pos_cos_cnt | numeric(12,0) | | |
up_hash_val | character varying(48 char) | | |
dn_time | character varying(14 char) | | |
dn_line_id | character varying(12 char) | | |
dn_stat_id | character varying(12 char) | | |
dn_stat_name | character varying(30 char) | | |
dn_pos_id | character varying(10 char) | | |
dn_pos_cos_cnt | numeric(12,0) | | |
dn_hash_val | character varying(48 char) | | |
txn_amt | numeric(12,0) | | |
bef_favordiscountamt | numeric(12,0) | | |
aft_favordiscountamt | numeric(12,0) | | |
up_qr_info | character varying(1000 char) | | |
dn_qr_info | character varying(1000 char) | | |
data_send_time | character varying(14 char) | | |
fvr_type | character varying(2 char) | | |
fvr_name | character varying(40 char) | | |
fvr_amt | numeric(12,0) | | |
Indexes：
"pk_t_spec_ymt_b490c786" PRIMARY KEY, btree (sys_sett_date, cen_seq)
2.2 数据量：
select count(1) from T_DTL_YMT;
count
-----------
226802274
(1 行记录)
select count(1) from T_SPEC_YMT;
count
-----------
215649506
(1 行记录)
三、原始慢查询语句及执行计划
3.1 查询sql语句：
select * from (
select sys_sett_date sysDatee,cen_seq seq, t.spec_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.spec_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_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date and B.CEN_SEQ=t.spec_seq ) upLineId ,
(select b.dn_LINE_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date and B.CEN_SEQ=t.spec_seq ) dnLineId,
(select b.UP_time from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date and B.CEN_SEQ=t.spec_seq ) upDttm ,
(select b.UP_STAT_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date and B.CEN_SEQ=t.spec_seq ) upStatId ,
(select b.UP_STAT_NAME from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date and B.CEN_SEQ=t.spec_seq ) upStatName ,
(select b.dn_STAT_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date and B.CEN_SEQ=t.spec_seq ) dnStatId,
(select b.DN_STAT_NAME from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date and B.CEN_SEQ=t.spec_seq ) dnStatName , '' upBusId, t.err_cd AS errCd, t.acq_inst_cd AS acqInstCd, b.ISS_INST_NAME AS acqInstName
from T_DTL_YMT 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.spec_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.spec_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_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date and B.CEN_SEQ=t.spec_seq ) upLineId ,
(select b.dn_LINE_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date and B.CEN_SEQ=t.spec_seq ) dnLineId,
(select b.UP_time from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date and B.CEN_SEQ=t.spec_seq ) upDttm ,
(select b.UP_STAT_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date and B.CEN_SEQ=t.spec_seq ) upStatId , '' upStatName ,
(select b.dn_STAT_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date and B.CEN_SEQ=t.spec_seq ) dnStatId, '' dnStatName ,
(select b.UP_BUS_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date and B.CEN_SEQ=t.spec_seq ) upBusId, t.err_cd AS errCd, t.acq_inst_cd AS acqInstCd, b.ISS_INST_NAME AS acqInstName
from T_DTL_CLOUD_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;
3.2 原始sql语句执行计划：
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_ymt_cardno_f2cf54bf on t_dtl_ymt 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_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 3
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 4
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 5
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 6
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 7
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 8
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_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_dtl_cloud_card_dtl_p001_card_no_idx on t_dtl_cloud_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_dtl_cloud_card_dtl_p002_card_no_idx on t_dtl_cloud_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_dtl_cloud_card_dtl_p003_card_no_idx on t_dtl_cloud_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_dtl_cloud_card_dtl_p004_card_no_idx on t_dtl_cloud_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_dtl_cloud_card_dtl_p005_card_no_idx on t_dtl_cloud_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_dtl_cloud_card_dtl_p006_card_no_idx on t_dtl_cloud_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_dtl_cloud_card_dtl_p007_card_no_idx on t_dtl_cloud_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_dtl_cloud_card_dtl_p008_card_no_idx on t_dtl_cloud_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_dtl_cloud_card_dtl_p009_card_no_idx on t_dtl_cloud_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_dtl_cloud_card_dtl_p010_card_no_idx on t_dtl_cloud_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_dtl_cloud_card_dtl_p011_card_no_idx on t_dtl_cloud_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_dtl_cloud_card_dtl_p012_card_no_idx on t_dtl_cloud_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_dtl_cloud_card_dtl_p013_card_no_idx on t_dtl_cloud_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_dtl_cloud_card_dtl_default_card_no_idx on t_dtl_cloud_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_SPEC_YMT_d3a0142e on T_SPEC_YMT b_9 (cost=0.57..2463334.30 rows=1 width=5) (never executed)
Index Cond: ((cen_seq)::text = (t_1.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.spec_sys_sett_date)::text)
SubPlan 11
-> Index Scan using pk_T_SPEC_YMT_d3a0142e on T_SPEC_YMT b_10 (cost=0.57..2463334.30 rows=1 width=5) (never executed)
Index Cond: ((cen_seq)::text = (t_1.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.spec_sys_sett_date)::text)
SubPlan 12
-> Index Scan using pk_T_SPEC_YMT_d3a0142e on T_SPEC_YMT b_11 (cost=0.57..2463334.30 rows=1 width=15) (never executed)
Index Cond: ((cen_seq)::text = (t_1.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.spec_sys_sett_date)::text)
SubPlan 13
-> Index Scan using pk_T_SPEC_YMT_d3a0142e on T_SPEC_YMT b_12 (cost=0.57..2463334.30 rows=1 width=2) (never executed)
Index Cond: ((cen_seq)::text = (t_1.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.spec_sys_sett_date)::text)
SubPlan 14
-> Index Scan using pk_T_SPEC_YMT_d3a0142e on T_SPEC_YMT b_13 (cost=0.57..2463334.30 rows=1 width=2) (never executed)
Index Cond: ((cen_seq)::text = (t_1.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.spec_sys_sett_date)::text)
SubPlan 15
-> Index Scan using pk_T_SPEC_YMT_d3a0142e on T_SPEC_YMT b_14 (cost=0.57..2463334.30 rows=1 width=9) (never executed)
Index Cond: ((cen_seq)::text = (t_1.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.spec_sys_sett_date)::text)
Planning Time: 87.307 ms
Execution Time: 398723.048 ms
四、问题定位：
通过原始的执行计划，查询99%时间都耗费在t_spec_ymt表关联查询，总共关联查询7次，每个关联需要loops 4次
--计算查询大概耗费时间，由于成本因子差异，导致计算结果可能不是十分的精确。此计算只是大概值
--SubPlan 2 （14266.232*4）+ SubPlan 3（14252.017*4）+ SubPlan 4（14206.960*4）+ SubPlan 5（14092.144*4*4）+ SubPlan 6（14296.261*4）+ SubPlan 7（14315.098*4）+ SubPlan 8（14251.161*4）= 398719.492ms
SubPlan 2
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 3
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 4
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 5
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 6
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 7
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 8
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt 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.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
五、问题分析：
5.1 执行计划分析：
T_SPEC_YMT表查询有使用索引扫描Index Scan using pk_t_spec_ymt_b490c786，pk_t_spec_ymt_b490c786索引是对表添加主键后自动创建的唯一索引，此索引使用sys_sett_date, cen_seq字段。
T_SPEC_YMT表sys_sett_date字段类型为character(8 char)也就是char(8)，cen_seq字段类型为character varying(10 char)也就是varchar(10)。
T_DTL_YMT表spec_sys_sett_date字段类型为character varying(8 char)也就是varchar(8)，spec_seq字段类型为character varying(12 char)也就是varchar(12)。
T_DTL_YMT T表关联T_SPEC_YMT B表查询：
关联条件 WHERE B.SYS_SETT_DATE=T.SPEC_SYS_SETT_DATE AND B.CEN_SEQ=T.SPEC_SEQ
B.SYS_SETT_DATE字段类型char(8)，T.SPEC_SYS_SETT_DATE字段类型varchar(8)
B.CEN_SEQ字段类型varchar(10)，T.SPEC_SEQ字段类型varchar(12)
5.2 表信息分析：
关联条件字段类型、长度不一致。数据类型、长度的差异会导致数据发生隐氏转换，同时B.SYS_SETT_DATE字段恰好又是联合索引的第一列，隐氏转换后无法用到组合索引的全部索引列，导致查询效率变慢。
举例：
上图是个两列索引，btree索引是按照第一列去排序并存储。发生隐式转换导致第一列（排序列）失效，不能使用索引。用第二列的话实际比全表扫描seqscan还慢。
六、问题解决：
定位到查询慢的原因解决问题就可以很好的解决问题。
方法1：强制关联字段不发生隐氏转换
WHERE B.SYS_SETT_DATE=T.SPEC_SYS_SETT_DATE::CHAR(8) AND B.CEN_SEQ=T.SPEC_SEQ
通过以上方法修改之后，查询就可以正常使用到T_SPEC_YMT表pk_t_spec_ymt_b490c786索引。
修改之后的查询语句：
select * from (
select sys_sett_date sysDatee,cen_seq seq, t.spec_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.spec_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_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq) upLineId ,
(select b.dn_LINE_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq) dnLineId,
(select b.UP_time from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq) upDttm ,
(select b.UP_STAT_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq) upStatId ,
(select b.UP_STAT_NAME from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq) upStatName ,
(select b.dn_STAT_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq) dnStatId,
(select b.DN_STAT_NAME from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq) dnStatName , '' upBusId, t.err_cd AS errCd, t.acq_inst_cd AS acqInstCd, b.ISS_INST_NAME AS acqInstName
from T_DTL_YMT 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.spec_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.spec_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_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq) upLineId ,
(select b.dn_LINE_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq) dnLineId,
(select b.UP_time from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq) upDttm ,
(select b.UP_STAT_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq) upStatId , '' upStatName ,
(select b.dn_STAT_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq) dnStatId, '' dnStatName ,
(select b.UP_BUS_ID from T_SPEC_YMT b where b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq) upBusId, t.err_cd AS errCd, t.acq_inst_cd AS acqInstCd, b.ISS_INST_NAME AS acqInstName
from T_DTL_CLOUD_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=5941.02..5941.06 rows=15 width=567) (actual time=3.120..3.133 rows=4 loops=1)
-> Sort (cost=5941.02..5941.06 rows=15 width=567) (actual time=3.116..3.129 rows=4 loops=1)
Sort Key: t.card_no, t.txn_date, t.txn_time
Sort Method: quicksort Memory: 26kB
-> Append (cost=0.99..5940.73 rows=15 width=565) (actual time=0.959..3.091 rows=4 loops=1)
-> Nested Loop Left Join (cost=0.99..1945.77 rows=1 width=721) (actual time=0.958..2.309 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.505..0.925 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.305..0.382 rows=4 loops=1)
-> Index Scan using idx_t_dtl_ymt_cardno_f2cf54bf on t_dtl_ymt t (cost=0.57..1822.18 rows=1 width=224) (actual time=0.290..0.360 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.006..0.070 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.066 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.027..0.057 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_spec_ymt_b490c786 on t_spec_ymt b_2 (cost=0.57..8.59 rows=1 width=3) (actual time=0.025..0.025 rows=0 loops=4)
Index Cond: ((sys_sett_date = (t.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = ((t.spec_seq)::text))
SubPlan 3
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt b_3 (cost=0.57..8.59 rows=1 width=3) (actual time=0.019..0.019 rows=0 loops=4)
Index Cond: ((sys_sett_date = (t.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = ((t.spec_seq)::text))
SubPlan 4
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt b_4 (cost=0.57..8.59 rows=1 width=15) (actual time=0.017..0.017 rows=0 loops=4)
Index Cond: ((sys_sett_date = (t.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = ((t.spec_seq)::text))
SubPlan 5
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt b_5 (cost=0.57..8.59 rows=1 width=2) (actual time=0.017..0.018 rows=0 loops=4)
Index Cond: ((sys_sett_date = (t.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = ((t.spec_seq)::text))
SubPlan 6
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt b_6 (cost=0.57..8.59 rows=1 width=19) (actual time=0.018..0.018 rows=0 loops=4)
Index Cond: ((sys_sett_date = (t.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = ((t.spec_seq)::text))
SubPlan 7
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt b_7 (cost=0.57..8.59 rows=1 width=3) (actual time=0.017..0.017 rows=0 loops=4)
Index Cond: ((sys_sett_date = (t.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = ((t.spec_seq)::text))
SubPlan 8
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt b_8 (cost=0.57..8.59 rows=1 width=19) (actual time=0.017..0.017 rows=0 loops=4)
Index Cond: ((sys_sett_date = (t.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = ((t.spec_seq)::text))
-> Hash Left Join (cost=40.90..3994.73 rows=14 width=533) (actual time=0.772..0.779 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.771..0.776 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.770..0.773 rows=0 loops=1)
-> Append (cost=0.56..3015.85 rows=14 width=181) (actual time=0.769..0.772 rows=0 loops=1)
-> Index Scan using t_dtl_cloud_card_dtl_p001_card_no_idx on t_dtl_cloud_card_dtl_p001 t_1 (cost=0.56..239.49 rows=1 width=141) (actual time=0.062..0.062 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_dtl_cloud_card_dtl_p002_card_no_idx on t_dtl_cloud_card_dtl_p002 t_2 (cost=0.56..227.60 rows=1 width=141) (actual time=0.064..0.064 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_dtl_cloud_card_dtl_p003_card_no_idx on t_dtl_cloud_card_dtl_p003 t_3 (cost=0.56..224.85 rows=1 width=141) (actual time=0.060..0.060 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_dtl_cloud_card_dtl_p004_card_no_idx on t_dtl_cloud_card_dtl_p004 t_4 (cost=0.56..229.65 rows=1 width=141) (actual time=0.058..0.058 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_dtl_cloud_card_dtl_p005_card_no_idx on t_dtl_cloud_card_dtl_p005 t_5 (cost=0.56..253.06 rows=1 width=141) (actual time=0.052..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_dtl_cloud_card_dtl_p006_card_no_idx on t_dtl_cloud_card_dtl_p006 t_6 (cost=0.56..246.06 rows=1 width=198) (actual time=0.057..0.057 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_dtl_cloud_card_dtl_p007_card_no_idx on t_dtl_cloud_card_dtl_p007 t_7 (cost=0.56..231.29 rows=1 width=198) (actual time=0.058..0.058 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_dtl_cloud_card_dtl_p008_card_no_idx on t_dtl_cloud_card_dtl_p008 t_8 (cost=0.56..229.59 rows=1 width=141) (actual time=0.057..0.057 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_dtl_cloud_card_dtl_p009_card_no_idx on t_dtl_cloud_card_dtl_p009 t_9 (cost=0.56..251.06 rows=1 width=141) (actual time=0.052..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_dtl_cloud_card_dtl_p010_card_no_idx on t_dtl_cloud_card_dtl_p010 t_10 (cost=0.56..199.10 rows=1 width=141) (actual time=0.057..0.057 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_dtl_cloud_card_dtl_p011_card_no_idx on t_dtl_cloud_card_dtl_p011 t_11 (cost=0.56..216.61 rows=1 width=141) (actual time=0.059..0.059 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_dtl_cloud_card_dtl_p012_card_no_idx on t_dtl_cloud_card_dtl_p012 t_12 (cost=0.56..240.01 rows=1 width=141) (actual time=0.061..0.061 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_dtl_cloud_card_dtl_p013_card_no_idx on t_dtl_cloud_card_dtl_p013 t_13 (cost=0.56..219.27 rows=1 width=141) (actual time=0.058..0.058 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_dtl_cloud_card_dtl_default_card_no_idx on t_dtl_cloud_card_dtl_default t_14 (cost=0.14..8.16 rows=1 width=586) (actual time=0.010..0.010 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_SPEC_YMT_d3a0142e on T_SPEC_YMT b_9 (cost=0.57..8.59 rows=1 width=5) (never executed)
Index Cond: ((sys_sett_date = (t_1.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = ((t_1.spec_seq)::character(8 char))::text))
SubPlan 11
-> Index Scan using pk_T_SPEC_YMT_d3a0142e on T_SPEC_YMT b_10 (cost=0.57..8.59 rows=1 width=5) (never executed)
Index Cond: ((sys_sett_date = (t_1.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = ((t_1.spec_seq)::character(8 char))::text))
SubPlan 12
-> Index Scan using pk_T_SPEC_YMT_d3a0142e on T_SPEC_YMT b_11 (cost=0.57..8.59 rows=1 width=15) (never executed)
Index Cond: ((sys_sett_date = (t_1.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = ((t_1.spec_seq)::character(8 char))::text))
SubPlan 13
-> Index Scan using pk_T_SPEC_YMT_d3a0142e on T_SPEC_YMT b_12 (cost=0.57..8.59 rows=1 width=2) (never executed)
Index Cond: ((sys_sett_date = (t_1.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = ((t_1.spec_seq)::character(8 char))::text))
SubPlan 14
-> Index Scan using pk_T_SPEC_YMT_d3a0142e on T_SPEC_YMT b_13 (cost=0.57..8.59 rows=1 width=2) (never executed)
Index Cond: ((sys_sett_date = (t_1.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = ((t_1.spec_seq)::character(8 char))::text))
SubPlan 15
-> Index Scan using pk_T_SPEC_YMT_d3a0142e on T_SPEC_YMT b_14 (cost=0.57..8.59 rows=1 width=9) (never executed)
Index Cond: ((sys_sett_date = (t_1.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = ((t_1.spec_seq)::character(8 char))::text))
Planning Time: 8.099 ms
Execution Time: 3.742 ms
(122 行记录)
此方法在应用不方便修改查询语句、不能修改表结构时可以结合Query mapping使用。
方法2：修改表结构使关联字段类型保持一致
修改T_DTL_YMT表spec_sys_sett_date字段类型为char(8)，或者修改T_SPEC_YMT表sys_sett_date字段类型为varchar(8)。使关联条件不发生隐氏转换。
alter table T_DTL_YMT alter spec_sys_sett_date type char(8);
alter table T_SPEC_YMT alter sys_sett_date type varchar(8);
由于客户坚决不同意进行表结构变更，此方法不能使用。
方法3：重新新建索引，并调整索引字段顺序
通过问题分析-表信息分析，btree索引是按照第一列去排序并存储。发生隐式转换导致第一列（排序列）失效，不能使用索引。通过将索引字段列顺序调整解决问题。
create index concurrently idx_name on T_SPEC_YMT(cen_seq,sys_sett_date);
再次执行原查询语句执行计划：
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1627.43..1627.47 rows=15 width=607) (actual time=57.858..57.907 rows=4 loops=1)
-> Sort (cost=1627.43..1627.47 rows=15 width=607) (actual time=57.855..57.901 rows=4 loops=1)
Sort Key: t.card_no, t.txn_date, t.txn_time
Sort Method: quicksort Memory: 26kB
-> Append (cost=1.27..1627.14 rows=15 width=605) (actual time=22.099..57.624 rows=4 loops=1)
-> Nested Loop Left Join (cost=1.27..531.13 rows=1 width=721) (actual time=22.097..41.507 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=1.27..496.08 rows=1 width=269) (actual time=19.771..35.461 rows=4 loops=1)
-> Nested Loop Left Join (cost=0.99..493.78 rows=1 width=248) (actual time=19.699..35.319 rows=4 loops=1)
-> Index Scan using idx_t_dtl_ymt_cardno_f2cf54bf on t_dtl_ymt t (cost=0.57..489.32 rows=1 width=224) (actual time=19.659..35.251 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: 78
-> Index Scan using pk_t_bse_pos_5bc5f933 on t_bse_pos p (cost=0.42..2.44 rows=1 width=37) (actual time=0.008..0.008 rows=0 loops=4)
Index Cond: ((sam_id)::text = (t.sam_id)::text)
-> Index Scan using pk_t_bse_unit_9c036540 on t_bse_unit u (cost=0.28..2.29 rows=1 width=29) (actual time=0.029..0.029 rows=1 loops=4)
Index Cond: ((unit_id)::text = (t.unit_id)::text)
-> Seq Scan on t_bse_iss_acq_cmpt b (cost=0.00..9.33 rows=433 width=27) (actual time=0.008..0.081 rows=433 loops=4)
SubPlan 1
-> Index Scan using pk_t_par_txn_type_st_7cb588ce on t_par_txn_type_st (cost=0.15..2.17 rows=1 width=20) (actual time=0.110..0.112 rows=1 loops=4)
Index Cond: ((txn_type = t.txn_type) AND (txn_st = t.txn_st))
SubPlan 2
-> Index Scan using idx_t_spec_ymt_2 on t_spec_ymt b_2 (cost=0.57..2.59 rows=1 width=3) (actual time=0.935..0.935 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.spec_seq)::text)
Filter: ((sys_sett_date )::text = (t.spec_sys_sett_date)::text)
SubPlan 3
-> Index Scan using idx_t_spec_ymt_2 on t_spec_ymt b_3 (cost=0.57..2.59 rows=1 width=3) (actual time=0.046..0.046 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 4
-> Index Scan using idx_t_spec_ymt_2 on t_spec_ymt b_4 (cost=0.57..2.59 rows=1 width=15) (actual time=0.038..0.038 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 5
-> Index Scan using idx_t_spec_ymt_2 on t_spec_ymt b_5 (cost=0.57..2.59 rows=1 width=2) (actual time=0.031..0.031 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 6
-> Index Scan using idx_t_spec_ymt_2 on t_spec_ymt b_6 (cost=0.57..2.59 rows=1 width=19) (actual time=0.031..0.031 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 7
-> Index Scan using idx_t_spec_ymt_2 on t_spec_ymt b_7 (cost=0.57..2.59 rows=1 width=3) (actual time=0.029..0.029 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
SubPlan 8
-> Index Scan using idx_t_spec_ymt_2 on t_spec_ymt b_8 (cost=0.57..2.59 rows=1 width=19) (actual time=0.025..0.025 rows=0 loops=4)
Index Cond: ((cen_seq)::text = (t.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t.spec_sys_sett_date)::text)
-> Hash Left Join (cost=16.00..1095.79 rows=14 width=609) (actual time=16.084..16.108 rows=0 loops=1)
Hash Cond: ((t_1.acq_inst_cd)::text = (b_1.acq_inst_cd)::text)
-> Nested Loop Left Join (cost=1.26..832.81 rows=14 width=227) (actual time=16.081..16.094 rows=0 loops=1)
-> Nested Loop Left Join (cost=0.98..813.71 rows=14 width=206) (actual time=16.080..16.091 rows=0 loops=1)
-> Append (cost=0.56..779.58 rows=14 width=181) (actual time=16.078..16.089 rows=0 loops=1)
-> Index Scan using t_dtl_cloud_card_dtl_p001_card_no_idx on t_dtl_cloud_card_dtl_p001 t_1 (cost=0.56..61.31 rows=1 width=141) (actual time=1.234..1.235 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_dtl_cloud_card_dtl_p002_card_no_idx on t_dtl_cloud_card_dtl_p002 t_2 (cost=0.56..58.29 rows=1 width=141) (actual time=1.109..1.109 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_dtl_cloud_card_dtl_p003_card_no_idx on t_dtl_cloud_card_dtl_p003 t_3 (cost=0.56..57.57 rows=1 width=141) (actual time=1.128..1.128 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_dtl_cloud_card_dtl_p004_card_no_idx on t_dtl_cloud_card_dtl_p004 t_4 (cost=0.56..58.78 rows=1 width=141) (actual time=1.080..1.081 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_dtl_cloud_card_dtl_p005_card_no_idx on t_dtl_cloud_card_dtl_p005 t_5 (cost=0.56..64.74 rows=1 width=141) (actual time=1.167..1.167 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_dtl_cloud_card_dtl_p006_card_no_idx on t_dtl_cloud_card_dtl_p006 t_6 (cost=0.56..63.01 rows=1 width=198) (actual time=1.194..1.194 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_dtl_cloud_card_dtl_p007_card_no_idx on t_dtl_cloud_card_dtl_p007 t_7 (cost=0.56..59.23 rows=1 width=198) (actual time=1.410..1.410 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_dtl_cloud_card_dtl_p008_card_no_idx on t_dtl_cloud_card_dtl_p008 t_8 (cost=0.56..58.83 rows=1 width=141) (actual time=1.204..1.205 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_dtl_cloud_card_dtl_p009_card_no_idx on t_dtl_cloud_card_dtl_p009 t_9 (cost=0.56..64.33 rows=1 width=141) (actual time=1.522..1.522 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_dtl_cloud_card_dtl_p010_card_no_idx on t_dtl_cloud_card_dtl_p010 t_10 (cost=0.56..52.11 rows=1 width=141) (actual time=1.466..1.466 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_dtl_cloud_card_dtl_p011_card_no_idx on t_dtl_cloud_card_dtl_p011 t_11 (cost=0.56..61.53 rows=1 width=141) (actual time=0.197..0.198 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_dtl_cloud_card_dtl_p012_card_no_idx on t_dtl_cloud_card_dtl_p012 t_12 (cost=0.56..61.44 rows=1 width=141) (actual time=1.836..1.836 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_dtl_cloud_card_dtl_p013_card_no_idx on t_dtl_cloud_card_dtl_p013 t_13 (cost=0.56..56.18 rows=1 width=141) (actual time=1.454..1.454 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_dtl_cloud_card_dtl_default_card_no_idx on t_dtl_cloud_card_dtl_default t_14 (cost=0.14..2.16 rows=1 width=586) (actual time=0.062..0.062 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..2.44 rows=1 width=37) (never executed)
Index Cond: ((sam_id)::text = (t_1.sam_id)::text)
-> Index Scan using pk_t_bse_unit_9c036540 on t_bse_unit u_1 (cost=0.28..1.36 rows=1 width=29) (never executed)
Index Cond: ((unit_id)::text = (t_1.unit_id)::text)
-> 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
-> Index Scan using pk_t_par_txn_type_st_7cb588ce on t_par_txn_type_st t_par_txn_type_st_1 (cost=0.15..2.17 rows=1 width=20) (never executed)
Index Cond: ((txn_type = t_1.txn_type) AND (txn_st = t_1.txn_st))
SubPlan 10
-> Index Scan using idx_t_spec_ymt_2 on t_spec_ymt b_9 (cost=0.57..2.59 rows=1 width=3) (never executed)
Index Cond: ((cen_seq)::text = (t_1.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.spec_sys_sett_date)::text)
SubPlan 11
-> Index Scan using idx_t_spec_ymt_2 on t_spec_ymt b_10 (cost=0.57..2.59 rows=1 width=3) (never executed)
Index Cond: ((cen_seq)::text = (t_1.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.spec_sys_sett_date)::text)
SubPlan 12
-> Index Scan using idx_t_spec_ymt_2 on t_spec_ymt b_11 (cost=0.57..2.59 rows=1 width=15) (never executed)
Index Cond: ((cen_seq)::text = (t_1.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.spec_sys_sett_date)::text)
SubPlan 13
-> Index Scan using idx_t_spec_ymt_2 on t_spec_ymt b_12 (cost=0.57..2.59 rows=1 width=2) (never executed)
Index Cond: ((cen_seq)::text = (t_1.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.spec_sys_sett_date)::text)
SubPlan 14
-> Index Scan using idx_t_spec_ymt_2 on t_spec_ymt b_13 (cost=0.57..2.59 rows=1 width=3) (never executed)
Index Cond: ((cen_seq)::text = (t_1.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.spec_sys_sett_date)::text)
SubPlan 15
-> Index Scan using idx_t_spec_ymt_2 on t_spec_ymt b_14 (cost=0.57..2.59 rows=1 width=10) (never executed)
Index Cond: ((cen_seq)::text = (t_1.spec_seq)::text)
Filter: ((sys_sett_date)::text = (t_1.spec_sys_sett_date)::text)
Planning Time: 164.294 ms
Execution Time: 62.390 ms
从原始的398秒到现在的62毫秒。
方法4：修改查询语句减少子查询次数
通过问题定位，查询时间都耗费在t_spec_ymt表关联查询。相同的关联条件，关联t_spec_ymt表7次。实际只关联t_spec_ymt表一次就可以得到想要的结果。
修改后的查询语句：
select * from (
select t.sys_sett_date sysDatee,t.cen_seq seq, t.spec_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.spec_seq as cenSeq,
t.txn_type||t.txn_St||'-'||tp.dsp txnDsp, b.UP_LINE_ID upLineId , b.dn_LINE_ID dnLineId, b.UP_time upDttm , b.UP_STAT_ID upStatId , b.UP_STAT_NAME upStatName , b.dn_STAT_ID dnStatId, b.DN_STAT_NAME dnStatName , '' upBusId, t.err_cd AS errCd, t.acq_inst_cd AS acqInstCd, tb.ISS_INST_NAME AS acqInstName
from T_DTL_YMT 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 tb on tb.ACQ_INST_CD = t.ACQ_INST_CD
left join T_SPEC_YMT b on b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq
left join t_par_txn_type_st tp on tp.txn_type=t.txn_type and tp.txn_st=t.txn_st
WHERE 1=1 AND t.txn_Date >= '20230621' AND t.txn_Date <= '20230621' AND t.card_no in ( '3105170080004518536' )
union all
select t.sys_sett_date sysDatee,t.dtl_seq seq, t.spec_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.spec_seq as cenSeq,
t.txn_type||t.txn_St||'-'||tp.dsp txnDsp, b.UP_LINE_ID upLineId , b.dn_LINE_ID dnLineId, b.UP_time upDttm , b.UP_STAT_ID upStatId , b.UP_STAT_NAME upStatName , b.dn_STAT_ID dnStatId, b.DN_STAT_NAME dnStatName , '' upBusId, t.err_cd AS errCd, t.acq_inst_cd AS acqInstCd, tb.ISS_INST_NAME AS acqInstName
from T_DTL_CLOUD_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 tb on tb.ACQ_INST_CD = t.ACQ_INST_CD
left join T_SPEC_YMT b on b.sys_sett_date=t.spec_sys_sett_date::char(8) and B.CEN_SEQ=t.spec_seq
left join t_par_txn_type_st tp on tp.txn_type=t.txn_type and tp.txn_st=t.txn_st
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=5214.33..5214.37 rows=15 width=393) (actual time=2.570..2.581 rows=4 loops=1)
-> Sort (cost=5214.33..5214.37 rows=15 width=393) (actual time=2.568..2.578 rows=4 loops=1)
Sort Key: t.card_no, t.txn_date, t.txn_time
Sort Method: quicksort Memory: 26kB
-> Append (cost=1.56..5214.04 rows=15 width=391) (actual time=0.781..2.549 rows=4 loops=1)
-> Nested Loop Left Join (cost=1.56..1896.90 rows=1 width=415) (actual time=0.780..1.766 rows=4 loops=1)
Join Filter: ((tp.txn_type = t.txn_type) AND (tp.txn_st = t.txn_st))
Rows Removed by Join Filter: 496
-> Nested Loop Left Join (cost=1.56..1887.14 rows=1 width=351) (actual time=0.720..1.597 rows=4 loops=1)
-> Nested Loop Left Join (cost=0.99..1878.55 rows=1 width=287) (actual time=0.665..1.491 rows=4 loops=1)
Join Filter: ((tb.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.537..0.955 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.327..0.426 rows=4loops=1)
-> Index Scan using idx_t_dtl_ymt_cardno_f2cf54bf on t_dtl_ymt t (cost=0.57..1822.18 rows=1 width=224) (actual time=0.315..0.407 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.005..0.071 rows=384 loops=4)
-> Seq Scan on t_bse_iss_acq_cmpt tb (cost=0.00..9.33 rows=433 width=27) (actual time=0.006..0.063 rows=433 loops=4)
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt b (cost=0.57..8.59 rows=1 width=84) (actual time=0.024..0.024 rows=0 loops=4)
Index Cond: ((sys_sett_date = (t.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = (t.spec_seq)::text))
-> Seq Scan on t_par_txn_type_st tp (cost=0.00..5.70 rows=270 width=26) (actual time=0.007..0.017 rows=125 loops=4)
-> Nested Loop Left Join (cost=41.62..3316.91 rows=14 width=389) (actual time=0.774..0.780 rows=0 loops=1)
-> Nested Loop Left Join (cost=41.47..3294.42 rows=14 width=309) (actual time=0.774..0.780 rows=0 loops=1)
-> Hash Left Join (cost=40.90..3174.12 rows=14 width=245) (actual time=0.774..0.779 rows=0 loops=1)
Hash Cond: ((t_1.acq_inst_cd)::text = (tb_1.acq_inst_cd)::text)
-> Hash Left Join (cost=26.15..3159.19 rows=14 width=227) (actual time=0.773..0.778 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.772..0.776 rows=0 loops=1)
-> Append (cost=0.56..3015.85 rows=14 width=181) (actual time=0.772..0.775 rows=0 loops=1)
-> Index Scan using t_dtl_cloud_card_dtl_p001_card_no_idx on t_dtl_cloud_card_dtl_p001 t_1 (cost=0.56..239.49 rows=1 width=141) (actual time=0.065..0.065 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_dtl_cloud_card_dtl_p002_card_no_idx on t_dtl_cloud_card_dtl_p002 t_2 (cost=0.56..227.60 rows=1 width=141) (actual time=0.065..0.065 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_dtl_cloud_card_dtl_p003_card_no_idx on t_dtl_cloud_card_dtl_p003 t_3 (cost=0.56..224.85 rows=1 width=141) (actual time=0.059..0.059 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_dtl_cloud_card_dtl_p004_card_no_idx on t_dtl_cloud_card_dtl_p004 t_4 (cost=0.56..229.65 rows=1 width=141) (actual time=0.057..0.057 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_dtl_cloud_card_dtl_p005_card_no_idx on t_dtl_cloud_card_dtl_p005 t_5 (cost=0.56..253.06 rows=1 width=141) (actual time=0.052..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_dtl_cloud_card_dtl_p006_card_no_idx on t_dtl_cloud_card_dtl_p006 t_6 (cost=0.56..246.06 rows=1 width=198) (actual time=0.053..0.053 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_dtl_cloud_card_dtl_p007_card_no_idx on t_dtl_cloud_card_dtl_p007 t_7 (cost=0.56..231.29 rows=1 width=198) (actual time=0.059..0.059 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_dtl_cloud_card_dtl_p008_card_no_idx on t_dtl_cloud_card_dtl_p008 t_8 (cost=0.56..229.59 rows=1 width=141) (actual time=0.058..0.058 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_dtl_cloud_card_dtl_p009_card_no_idx on t_dtl_cloud_card_dtl_p009 t_9 (cost=0.56..251.06 rows=1 width=141) (actual time=0.053..0.054 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_dtl_cloud_card_dtl_p010_card_no_idx on t_dtl_cloud_card_dtl_p010 t_10 (cost=0.56..199.10 rows=1 width=141) (actual time=0.058..0.059 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_dtl_cloud_card_dtl_p011_card_no_idx on t_dtl_cloud_card_dtl_p011 t_11 (cost=0.56..216.61 rows=1 width=141) (actual time=0.057..0.057 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_dtl_cloud_card_dtl_p012_card_no_idx on t_dtl_cloud_card_dtl_p012 t_12 (cost=0.56..240.01 rows=1 width=141) (actual time=0.061..0.061 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_dtl_cloud_card_dtl_p013_card_no_idx on t_dtl_cloud_card_dtl_p013 t_13 (cost=0.56..219.27 rows=1 width=141) (actual time=0.056..0.056 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_dtl_cloud_card_dtl_default_card_no_idx on t_dtl_cloud_card_dtl_default t_14 (cost=0.14..8.16 rows=1 width=586) (actual time=0.015..0.015 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 tb_1 (cost=0.00..9.33 rows=433 width=27) (never executed)
-> Index Scan using pk_t_spec_ymt_b490c786 on t_spec_ymt b_1 (cost=0.57..8.59 rows=1 width=84) (never executed)
Index Cond: ((sys_sett_date = (t_1.spec_sys_sett_date)::character(8 char)) AND ((cen_seq)::text = (t_1.spec_seq)::text))
-> Index Scan using pk_t_par_txn_type_st_7cb588ce on t_par_txn_type_st tp_1 (cost=0.15..1.60 rows=1 width=26) (never executed)
Index Cond: ((txn_type = t_1.txn_type) AND (txn_st = t_1.txn_st))
Planning Time: 12.140 ms
Execution Time: 3.093 ms
(89 行记录)
七、优化案例总结：
在关联字段上创建索引，提高查询性能。
在关联条件上使用相同的数据类型和函数，避免隐式转换或函数调用导致的性能问题。
KINGBASE研究院
