第一步：收集慢的SQL脚本，执行一次需求30秒左右
SELECT b.fmaterialid AS fmaterialid, b.fcompanyorgunitid AS fcompanyorgunitid, 0 AS fbeginamount, 0 AS fbegindiffamount, b.factualcost AS factualcostin, CASE WHEN t5.fnumber = '1001' THEN b.fstandardcost ELSE 0 END AS fstandardcostin, 0 AS factualcostout, 0 AS fstandardcostout, CASE WHEN t5.fnumber = '1005' THEN (b.factualcost - b.fstandardcost) ELSE 0 END AS fstandardcostdiffin, 0 AS fstandardcostdiffout, a.fyear AS fyear, a.fperiod AS fperiod, t6.fid AS factid
FROM t_im_purinwarehsbill a
INNER JOIN t_im_purinwarehsentry b ON a.fid = b.fparentid
INNER JOIN t_scm_transactiontype c ON a.ftransactiontypeid = c.fid
INNER JOIN t_im_invupdatetype d ON b.finvupdatetypeid = d.fid
INNER JOIN t_im_storetype e ON d.fstoretypeid = e.fid
INNER JOIN t_bd_materialcompanyinfo t3 ON (b.fmaterialid = t3.fmaterialid AND b.fcompanyorgunitid = t3.fcompanyid)
INNER JOIN t_bd_kaclassificationdetail t4 ON t3.fkaclassid = t4.fkaclassficlink
INNER JOIN t_bd_kaccountitem t5 ON t4.faccountitemlinkid = t5.fid
INNER JOIN t_bd_accountview t6 ON t4.faccountlinkid = t6.fid
WHERE (((((((b.fcompanyorgunitid = 'Qx30mtAgQTyvVMjG+1AqdcznrtQ=' AND (a.fisinitbill = 0 OR (a.fisinitbill IS NULL))) AND e.fisforwardamt = 1) AND c.fismanucheckaccount = 1) AND c.fiscalculate = 1) AND a.fbasestatus = 4) AND (a.fyear = 2021 AND a.fperiod = 4)) AND (t5.fnumber = '1001' OR (t5.fnumber = '1005' AND t3.faccounttype = 2)))
第二步：分析执行计划，检查是否存在全表扫描
QUERY PLAN
1 Nested Loop (cost=610.41..176175.16 rows=1 width=185)
2 -> Nested Loop (cost=609.99..176171.58 rows=2 width=109)
3 -> Nested Loop (cost=609.85..176171.20 rows=2 width=133)
4 -> Hash Join (cost=609.43..175812.09 rows=467 width=127)
5 Hash Cond: ((b.finvupdatetypeid)::text = (d.fid)::text)
6 -> Nested Loop (cost=606.04..175772.54 rows=8398 width=144)
7 -> Hash Join (cost=605.48..7364.34 rows=1224 width=85)
8 Hash Cond: ((t3.fkaclassid)::text = (t4.fkaclassficlink)::text)
9 Join Filter: (((t5.fnumber)::text = '1001'::text) OR (((t5.fnumber)::text = '1005'::text) AND (t3.faccounttype = '2'::numeric)))
10 -> Bitmap Heap Scan on t_bd_materialcompanyinfo t3 (cost=507.05..7186.83 rows=9630 width=85)
11 Recheck Cond: ((fcompanyid)::text = 'Qx30mtAgQTyvVMjG+1AqdcznrtQ='::text)
12 -> Bitmap Index Scan on ix_bd_matcommatid1 (cost=0.00..504.65 rows=9630 width=0)
13 Index Cond: ((fcompanyid)::text = 'Qx30mtAgQTyvVMjG+1AqdcznrtQ='::text)
14 -> Hash (cost=97.77..97.77 rows=53 width=58)
15 -> Hash Join (cost=6.30..97.77 rows=53 width=58)
16 Hash Cond: ((t4.faccountitemlinkid)::text = (t5.fid)::text)
17 -> Seq Scan on t_bd_kaclassificationdetail t4 (cost=0.00..85.32 rows=2232 width=94)
18 -> Hash (cost=6.28..6.28 rows=2 width=48)
19 -> Seq Scan on t_bd_kaccountitem t5 (cost=0.00..6.28 rows=2 width=48)
20 Filter: (((fnumber)::text = '1001'::text) OR ((fnumber)::text = '1005'::text))
21 -> Index Scan using idx_purine_ff on t_im_purinwarehsentry b (cost=0.56..137.14 rows=45 width=111)
22 Index Cond: (((fcompanyorgunitid)::text = 'Qx30mtAgQTyvVMjG+1AqdcznrtQ='::text) AND ((fmaterialid)::text = (t3.fmaterialid)::text))
23 -> Hash (cost=3.38..3.38 rows=1 width=106)
24 -> Hash Join (cost=1.14..3.38 rows=1 width=106)
25 Hash Cond: ((d.fstoretypeid)::text = (e.fid)::text)
26 -> Seq Scan on t_im_invupdatetype d (cost=0.00..2.18 rows=18 width=212)
27 -> Hash (cost=1.12..1.12 rows=1 width=106)
28 -> Seq Scan on t_im_storetype e (cost=0.00..1.12 rows=1 width=106)
29 Filter: (fisforwardamt = '1'::numeric)
30 -> Index Scan using pk_purinwarehsbill on t_im_purinwarehsbill a (cost=0.42..0.77 rows=1 width=62)
31 Index Cond: ((fid)::text = (b.fparentid)::text)
32 Filter: (((fisinitbill = '0'::numeric) OR (fisinitbill IS NULL)) AND (fbasestatus = '4'::numeric) AND (fperiod = '4'::numeric) AND (fyear = '2021'::numeric))
33 -> Index Scan using pk_transactiontype on t_scm_transactiontype c (cost=0.14..0.19 rows=1 width=23)
34 Index Cond: ((fid)::text = (a.ftransactiontypeid)::text)
35 Filter: ((fismanucheckaccount = '1'::numeric) AND (fiscalculate = '1'::numeric))
36 -> Index Only Scan using pk_bd_accountview on t_bd_accountview t6 (cost=0.41..1.78 rows=1 width=28)
37 Index Cond: (fid = (t4.faccountlinkid)::text)
第三步：对存在全表扫描的关联字段添加索引
CREATE INDEX ix_bd_kaclassid ON t_bd_materialcompanyinfo(fkaclassid);
CREATE INDEX ix_bd_acctitemlinkid ON t_bd_kaclassificationdetail(faccountitemlinkid);
第四步：对比分析执行结果
用户体验：对账业务操作时间从原来12分钟缩短到4分钟；
脚本执行时长：从30秒缩短到12秒。
推荐阅读