好久没写博客，平时工作非常忙，而且现在对接的应用基本都是微服务架构。
微服务这种架构平时也很难遇到复杂SQL，架构层面也限制了不允许有复杂SQL，平时处理的都是简单一批的点查SQL。
基本上优化的内容就是业务，架构上改改和开发扯皮，每条SQL扣毫秒这样来搞，并发情况下程序接口的整体RT降低而达到优化指标，实在没意思。
说实话还是传统行业复杂SQL好玩，昨晚来了个传统行业的PG慢SQL，正好有案例写博客了，这个CASE 搞了近三个小时左右，也算是复杂SQL了。
客户环境 PG11版本。
慢SQL数据量：
-- -- 数据量 SELECT COUNT(1) FROM xxxxxx -- 10881 UNION ALL SELECT COUNT(1) FROM sssssss -- 6237204 UNION ALL SELECT COUNT(1) FROM xzxzxz.zzzzzz; -- 303437
慢SQL：
select l05.mid,
xzxzxz.func1(
case
when l05.shift_id = 1 and (extract(hour from cast(l05.shift_begin_time as timestamp))) > (extract(hour from cast(xzxzxz.func2('hour', -5,(to_char('2024-10-17'::timestamp, 'yyyy-mm-dd') ||' ' || to_char(starttime::timestamp, 'hh24:mi:ss')):: timestamp) as timestamp))) then xzxzxz.func2('day', 1, l05.shift_begin_time::date::timestamp)
when l05.shift_id = 4 and (extract(hour from cast(l05.shift_begin_time as timestamp))) < (extract(hour from cast(xzxzxz.func2('hour', 5, (to_char((case when endtime < starttime then xzxzxz.func2('day', 1, '2024-10-17') else '2024-10-17' end) ::timestamp, 'yyyy-mm-dd') || ' ' || to_char(endtime::timestamp, 'hh24:mi:ss')):: timestamp) as timestamp))) then xzxzxz.func2('day', -1, l05.shift_begin_time::date::timestamp)
else l05.shift_begin_time::date::timestamp end
) * 10 + l05.shift_id as shift_index,
l05.plaza_id,
l05.lane_id,
l05.lane_type,
l05.operator_id,
l05.shift_begin_time,
0 as ls_type,
case
when l05.pay_type_new = 1 then 0
when l05.pay_type_new = 4 and l05.medium_type <> 13 then 2
when l05.pay_type_new = 4 and l05.medium_type = 13 then 1
when l05.pay_type_new not in (1, 4) then 7
end as data_source,
case
when char_length(coalesce(l05.icard_issuer_num, '')) >= 16 and
char_length(coalesce(l05.icard_license, '')) >= 7 and l05.bill_no = 0 and l05.pay_type_new <> 4
then 82
else l05.pay_type_new end as medium_type,
l05.veh_type,
l05.ex_vehicle_class,
(case
when l.organ_id > 0 then l.organ_id
when coalesce(l.organ_id, 0) = 0 then COALESCE(k.organ_id, 0)
else 0 end) as ent_plaza_id,
case
when l05.real_fare = mobile.order_fee * 100 then COALESCE(l05.real_fare, 0)
else COALESCE(mobile.order_fee * 100, 0) end as realfare,
l05.real_fare as l05fee,
mobile.order_fee as mobilefee,
l05.pass_id,
case when l05.real_fare = mobile.order_fee * 100 then 0 else 1 end as change_type,
-1 as sendtocenterflag,
1 as process_result, --状态
COALESCE(l05.fee_fare, 0) as feefare,
l05.bill_no,
l05.sp_pay_type,
case when l05.icard_card_type = 6 then 99 else l05.lane_state end as lanestate,
l05.pay_subclass,
l05.ent_operator_id,
l05.ent_lane_no,
l05.ent_pay_type,
l05.ent_veh_type,
COALESCE(l05.multi_province, 0) multi_province,
l05.fee_version,
l05.trans_occur_time,
l05.mobile_trans_no,
l05.car_license,
case when COALESCE(l05.icard_net_id, '') = '' then '0' else icard_net_id end as icard_net_id,
1000079 as unit_id,
l05.pay_method
from xxxxxx mobile
inner join sssssss l05 on l05.mobile_trans_no = mobile.merchant_ordernum
left join xzxzxz.zzzzzz as j
on (case when length(l05.en_toll_lane_hex) = 10 then l05.en_toll_lane_hex else '' end) = j.organ_hex
left join xzxzxz.zzzzzz as l on l.tollorganid = substr(j.tollorganid,0,19)
left join (select organ_id, organ_hex, organ_character from xzxzxz.zzzzzz where organ_character = 2) as k
on (case when length(l05.en_toll_lane_hex) = 10 then substr(l05.en_toll_lane_hex,0,9) else '' end) = k.organ_hex;
慢SQL执行计划：
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Hash Left Join (cost=11133.03..629647165.98 rows=375674287 width=660) (actual time=4525.081..292064.633 rows=10872 loops=1) |
Hash Cond: (substr((j.tollorganid)::text, 0, 19) = (l.tollorganid)::text) |
Buffers: shared hit=56887978 read=44439 |
-> Merge Join (cost=1.70..12497084.51 rows=375674287 width=839) (actual time=4020.751..291265.665 rows=10872 loops=1) |
Merge Cond: ((mobile.merchant_ordernum)::text = (l05.mobile_trans_no)::text) |
Buffers: shared hit=56883478 read=44439 |
-> Index Scan using idx_mobile_temp_gid_syj on xxxxxx mobile (cost=0.29..1663.50 rows=10881 width=234) (actual time=0.065..37.447 rows=10881 loops=1) |
Buffers: shared hit=10104 read=79 |
-> Materialize (cost=1.42..6877542.09 rows=6905143 width=823) (actual time=27.938..274291.243 rows=6237042 loops=1) |
Buffers: shared hit=56873374 read=44360 |
-> Nested Loop Left Join (cost=1.42..6860279.24 rows=6905143 width=823) (actual time=27.926..261668.057 rows=6237042 loops=1) |
Buffers: shared hit=56873374 read=44360 |
-> Nested Loop Left Join (cost=0.99..3998300.66 rows=6237676 width=860) (actual time=27.889..147839.675 rows=6237042 loops=1) |
Buffers: shared hit=31861947 read=44359 |
-> Index Scan using idx_l05_ck_temp_gid_syj on sssssss l05 (cost=0.56..1105133.70 rows=6237676 width=852) (actual time=27.774..20991.611 rows=6237042 loops=1)|
Buffers: shared hit=4781666 read=44359 |
-> Index Scan using zzzzzz_organ_hex_idx on zzzzzz (cost=0.43..0.45 rows=1 width=18) (actual time=0.015..0.016 rows=1 loops=6237042) |
Index Cond: (CASE WHEN (length((l05.en_toll_lane_hex)::text) = 10) THEN substr((l05.en_toll_lane_hex)::text, 0, 9) ELSE ''::text END = (organ_hex)::text) |
Filter: (organ_character = 2) |
Rows Removed by Filter: 3 |
Buffers: shared hit=27080281 |
-> Index Scan using zzzzzz_organ_hex_idx on zzzzzz j (cost=0.43..0.45 rows=1 width=31) (actual time=0.014..0.015 rows=1 loops=6237042) |
Index Cond: ((CASE WHEN (length((l05.en_toll_lane_hex)::text) = 10) THEN l05.en_toll_lane_hex ELSE ''::character varying END)::text = (organ_hex)::text) |
Buffers: shared hit=25011427 read=1 |
-> Hash (cost=7338.37..7338.37 rows=303437 width=29) (actual time=501.269..501.271 rows=303437 loops=1) |
Buckets: 524288 Batches: 1 Memory Usage: 22244kB |
Buffers: shared hit=4304 |
-> Seq Scan on zzzzzz l (cost=0.00..7338.37 rows=303437 width=29) (actual time=0.029..227.902 rows=303437 loops=1) |
Buffers: shared hit=4304 |
Planning Time: 175.656 ms |
Execution Time: 292075.148 ms
慢SQL执行时间近300秒。
1、先加索引优化
-- 优化步骤1：加索引
CREATE INDEX idx_sssssss_mobile_a1_a2
ON sssssss (mobile_trans_no,
(CASE WHEN length(en_toll_lane_hex) = 10 THEN en_toll_lane_hex ELSE '' END),
(CASE WHEN length(en_toll_lane_hex) = 10 THEN substr(en_toll_lane_hex, 0, 9) ELSE '' END));
CREATE INDEX idx_zzzzzz_a1_organ_hex_character
ON xzxzxz.zzzzzz ((substr(tollorganid, 0, 19)), organ_hex, organ_character);
加索引后执行的SQL和计划
select count(1)
from xxxxxx mobile
inner join sssssss l05 on l05.mobile_trans_no = mobile.merchant_ordernum
left join xzxzxz.zzzzzz as j
on (case when length(l05.en_toll_lane_hex) = 10 then l05.en_toll_lane_hex else '' end) = j.organ_hex
left join xzxzxz.zzzzzz as l on l.tollorganid = substr(j.tollorganid,0,19)
left join (select organ_id, organ_hex, organ_character from xzxzxz.zzzzzz where organ_character = 2) as k
on (case when length(l05.en_toll_lane_hex) = 10 then substr(l05.en_toll_lane_hex,0,9) else '' end) = k.organ_hex;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=4011680.74..4011680.75 rows=1 width=8) (actual time=133480.601..133480.804 rows=1 loops=1)
Buffers: shared hit=234559 read=50
-> Gather (cost=4011680.52..4011680.73 rows=2 width=8) (actual time=133480.574..133480.788 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=234559 read=50
-> Partial Aggregate (cost=4010680.52..4010680.53 rows=1 width=8) (actual time=129523.399..129523.425 rows=1 loops=3)
Buffers: shared hit=234559 read=50
-> Merge Join (cost=1257211.55..3619382.75 rows=156519108 width=0) (actual time=123091.676..129521.333 rows=3624 loops=3)
Merge Cond: ((l05.mobile_trans_no)::text = (mobile.merchant_ordernum)::text)
Buffers: shared hit=234559 read=50
-> Sort (cost=1256078.20..1263270.51 rows=2876925 width=92) (actual time=122711.876..124326.524 rows=2079015 loops=3)
Sort Key: l05.mobile_trans_no
Sort Method: quicksort Memory: 263982kB
Worker 0: Sort Method: quicksort Memory: 211528kB
Worker 1: Sort Method: quicksort Memory: 208381kB
Buffers: shared hit=233674 read=50
-> Merge Left Join (cost=863913.45..947440.30 rows=2876925 width=92) (actual time=24753.691..31435.309 rows=2079068 loops=3)
Merge Cond: (((CASE WHEN (length((l05.en_toll_lane_hex)::text) = 10) THEN l05.en_toll_lane_hex ELSE ''::character varying END)::text) = (j.organ_hex)::text)
Buffers: shared hit=233659 read=50
-> Sort (cost=828945.57..835442.66 rows=2598835 width=150) (actual time=21526.156..22879.565 rows=2079068 loops=3)
Sort Key: ((CASE WHEN (length((l05.en_toll_lane_hex)::text) = 10) THEN l05.en_toll_lane_hex ELSE ''::character varying END)::text)
Sort Method: quicksort Memory: 373118kB
Worker 0: Sort Method: quicksort Memory: 341429kB
Worker 1: Sort Method: quicksort Memory: 335763kB
Buffers: shared hit=220747 read=50
-> Merge Left Join (cost=516564.62..552047.06 rows=2598835 width=150) (actual time=9103.137..15973.869 rows=2079068 loops=3)
Merge Cond: ((CASE WHEN (length((l05.en_toll_lane_hex)::text) = 10) THEN substr((l05.en_toll_lane_hex)::text, 0, 9) ELSE ''::text END) = (zzzzzz.organ_hex)::text)
Buffers: shared hit=220747 read=50
-> Sort (cost=510811.86..517308.95 rows=2598835 width=150) (actual time=8821.154..10404.795 rows=2079068 loops=3)
Sort Key: (CASE WHEN (length((l05.en_toll_lane_hex)::text) = 10) THEN substr((l05.en_toll_lane_hex)::text, 0, 9) ELSE ''::text END)
Sort Method: quicksort Memory: 373118kB
Worker 0: Sort Method: quicksort Memory: 341429kB
Worker 1: Sort Method: quicksort Memory: 335763kB
Buffers: shared hit=207925
-> Parallel Seq Scan on sssssss l05 (cost=0.00..233913.35 rows=2598835 width=150) (actual time=0.041..3501.640 rows=2079068 loops=3)
Buffers: shared hit=207925
-> Sort (cost=5752.76..5787.89 rows=14049 width=10) (actual time=281.955..1302.555 rows=2090282 loops=3)
Sort Key: zzzzzz.organ_hex
Sort Method: quicksort Memory: 1068kB
Worker 0: Sort Method: quicksort Memory: 1068kB
Worker 1: Sort Method: quicksort Memory: 1068kB
Buffers: shared hit=12822 read=50
-> Bitmap Heap Scan on zzzzzz (cost=305.30..4784.91 rows=14049 width=10) (actual time=131.570..179.561 rows=14585 loops=3)
Recheck Cond: (organ_character = 2)
Heap Blocks: exact=4236
Buffers: shared hit=12822 read=50
-> Bitmap Index Scan on zzzzzz_organ_character_idx (cost=0.00..301.79 rows=14049 width=0) (actual time=130.688..130.688 rows=14585 loops=3)
Index Cond: (organ_character = 2)
Buffers: shared hit=114 read=50
-> Sort (cost=34967.88..35726.48 rows=303437 width=31) (actual time=3221.223..4345.529 rows=2361547 loops=3)
Sort Key: j.organ_hex
Sort Method: quicksort Memory: 35992kB
Worker 0: Sort Method: quicksort Memory: 35992kB
Worker 1: Sort Method: quicksort Memory: 35992kB
Buffers: shared hit=12912
-> Seq Scan on zzzzzz j (cost=0.00..7338.37 rows=303437 width=31) (actual time=0.027..209.979 rows=303437 loops=3)
Buffers: shared hit=12912
-> Sort (cost=1133.36..1160.56 rows=10881 width=218) (actual time=293.065..301.372 rows=10881 loops=3)
Sort Key: mobile.merchant_ordernum
Sort Method: quicksort Memory: 1235kB
Worker 0: Sort Method: quicksort Memory: 1235kB
Worker 1: Sort Method: quicksort Memory: 1235kB
Buffers: shared hit=885
-> Seq Scan on xxxxxx mobile (cost=0.00..403.81 rows=10881 width=218) (actual time=0.066..8.521 rows=10881 loops=3)
Buffers: shared hit=885
Planning Time: 3.263 ms
Execution Time: 133520.586 ms
执行速度降低到133秒，但是发现走的是 Merge 计划，计划中每个节点内存消耗不少：
- Sort Method: quicksort Memory: 263,982kB
- Worker 0: Sort Method: quicksort Memory: 211,528kB
- Worker 1: Sort Method: quicksort Memory: 208,381kB
- Sort Method: quicksort Memory: 373,118kB
- Worker 0: Sort Method: quicksort Memory: 341,429kB
- Worker 1: Sort Method: quicksort Memory: 335,763kB
PG的 Merge 算法是真的鸡肋，个人认为完全可以直接干掉，只保留NL和HASH就行。
2、调整会话变量
-- 这两个参数是会话级别关闭的参数，让你们研发在每次跑这条SQL的时候，会话级别设置这两条参数。（这个步骤需要你们开发配合）
set enable_nestloop = off;
set enable_mergejoin = off;
set max_parallel_workers_per_gather = 8;
-- JAVA 代码设置案例
Statement stmt = conn.createStatement()
stmt.execute("SET enable_nestloop = off");
stmt.execute("SET enable_mergejoin = off");
stmt.execute("SET max_parallel_workers_per_gather = 8");
调整会话级变量后SQL和计划
select count(1)
from xxxxxx mobile
inner join sssssss l05 on l05.mobile_trans_no = mobile.merchant_ordernum
left join xzxzxz.zzzzzz as j
on (case when length(l05.en_toll_lane_hex) = 10 then l05.en_toll_lane_hex else '' end) = j.organ_hex
left join xzxzxz.zzzzzz as l on l.tollorganid = substr(j.tollorganid,0,19)
left join (select organ_id, organ_hex, organ_character from xzxzxz.zzzzzz where organ_character = 2) as k
on (case when length(l05.en_toll_lane_hex) = 10 then substr(l05.en_toll_lane_hex,0,9) else '' end) = k.organ_hex;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=4758955.60..4758955.61 rows=1 width=8) (actual time=13396.755..13473.827 rows=1 loops=1)
Buffers: shared hit=226781
-> Gather (cost=4758955.38..4758955.59 rows=2 width=8) (actual time=13396.491..13473.808 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=226781
-> Partial Aggregate (cost=4757955.38..4757955.39 rows=1 width=8) (actual time=13388.658..13388.676 rows=1 loops=3)
Buffers: shared hit=226781
-> Parallel Hash Join (cost=13603.08..4366657.61 rows=156519108 width=0) (actual time=12892.041..13386.561 rows=3624 loops=3)
Hash Cond: ((l05.mobile_trans_no)::text = (mobile.merchant_ordernum)::text)
Buffers: shared hit=226781
-> Parallel Hash Left Join (cost=11904.37..1135466.74 rows=2876925 width=92) (actual time=243.922..11280.639 rows=2079068 loops=3)
Hash Cond: ((CASE WHEN (length((l05.en_toll_lane_hex)::text) = 10) THEN l05.en_toll_lane_hex ELSE ''::character varying END)::text = (j.organ_hex)::text)
Buffers: shared hit=216516
-> Parallel Hash Left Join (cost=4755.65..739499.77 rows=2598835 width=150) (actual time=28.981..7557.126 rows=2079068 loops=3)
Hash Cond: (CASE WHEN (length((l05.en_toll_lane_hex)::text) = 10) THEN substr((l05.en_toll_lane_hex)::text, 0, 9) ELSE ''::text END = (zzzzzz.organ_hex)::text)
Buffers: shared hit=212212
-> Parallel Seq Scan on sssssss l05 (cost=0.00..233913.35 rows=2598835 width=150) (actual time=0.022..1849.682 rows=2079068 loops=3)
Buffers: shared hit=207925
-> Parallel Hash (cost=4682.47..4682.47 rows=5854 width=10) (actual time=28.844..28.847 rows=4862 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 864kB
Buffers: shared hit=4287
-> Parallel Bitmap Heap Scan on zzzzzz (cost=305.30..4682.47 rows=5854 width=10) (actual time=4.031..22.681 rows=4862 loops=3)
Recheck Cond: (organ_character = 2)
Heap Blocks: exact=1745
Buffers: shared hit=4287
-> Bitmap Index Scan on zzzzzz_organ_character_idx (cost=0.00..301.79 rows=14049 width=0) (actual time=3.074..3.074 rows=14585 loops=1)
Index Cond: (organ_character = 2)
Buffers: shared hit=51
-> Parallel Hash (cost=5568.32..5568.32 rows=126432 width=31) (actual time=214.125..214.127 rows=101146 loops=3)
Buckets: 524288 Batches: 1 Memory Usage: 24800kB
Buffers: shared hit=4304
-> Parallel Seq Scan on zzzzzz j (cost=0.00..5568.32 rows=126432 width=31) (actual time=0.039..81.506 rows=101146 loops=3)
Buffers: shared hit=4304
-> Parallel Hash (cost=1618.70..1618.70 rows=6401 width=218) (actual time=13.627..13.630 rows=3627 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 928kB
Buffers: shared hit=10187
-> Parallel Index Only Scan using idx_mobile_temp_gid_syj on xxxxxx mobile (cost=0.29..1618.70 rows=6401 width=218) (actual time=0.074..8.916 rows=3627 loops=3)
Heap Fetches: 10881
Buffers: shared hit=10187
Planning Time: 0.906 ms
Execution Time: 13474.008 ms
可以看到SQL执行时间从133秒降到13秒左右了，继续优化。
后面我了解到这条SQL执行次数不多，让客户加个 set max_parallel_workers_per_gather = 8，SQL可以6 秒跑出结果。
3、优化函数逻辑、将函数逻辑改成SQL逻辑
SQL优化到6秒，加上原来的函数跑，执行时间又到了60多秒，看了一下两个函数逻辑都比较简单，（函数代码就不放，不能泄露客户代码）：
1、func1：是求儒略日到今日是多少天。
2、func2：是个日期转换的函数，用于传入时间加减判断的函数。
两个函数都是 IMMUTABLE 状态，函数内逻辑无优化空间，SQL 返回 10872 行数据，应该每行数据的日期值都不一样，需要处理 10872 次，这里导致SQL整体时间消耗60秒。
评估了下是能将函数逻辑用SQL逻辑来代替，这块改写花了1个多小时。
最终SQL：
select l05.mid,
((EXTRACT(EPOCH FROM (
CASE
WHEN l05.shift_id = 1 AND extract(hour FROM l05.shift_begin_time) >
extract(hour FROM '2024-10-17'::timestamp + INTERVAL '-5 hours') THEN
(l05.shift_begin_time::date + INTERVAL '1 day')::timestamp
WHEN l05.shift_id = 4 AND extract(hour FROM l05.shift_begin_time) <
extract(hour FROM CASE WHEN endtime < starttime THEN
'2024-10-17'::timestamp + INTERVAL '1 day'
ELSE
'2024-10-17'::timestamp
END + INTERVAL '5 hours') THEN
(l05.shift_begin_time::date - INTERVAL '1 day')::timestamp
ELSE
l05.shift_begin_time::date::timestamp
END
) - '2000-01-01'::timestamp) / 86400)::BIGINT + 2451545) * 10 + l05.shift_id AS shift_index,
l05.plaza_id,
l05.lane_id,
l05.lane_type,
l05.operator_id,
l05.shift_begin_time,
0 as ls_type,
case
when l05.pay_type_new = 1 then 0
when l05.pay_type_new = 4 and l05.medium_type <> 13 then 2
when l05.pay_type_new = 4 and l05.medium_type = 13 then 1
when l05.pay_type_new not in (1, 4) then 7
end as data_source,
case
when char_length(coalesce(l05.icard_issuer_num, '')) >= 16 and
char_length(coalesce(l05.icard_license, '')) >= 7 and l05.bill_no = 0 and l05.pay_type_new <> 4
then 82
else l05.pay_type_new end as medium_type,
l05.veh_type,
l05.ex_vehicle_class,
(case
when l.organ_id > 0 then l.organ_id
when coalesce(l.organ_id, 0) = 0 then COALESCE(k.organ_id, 0)
else 0 end) as ent_plaza_id,
case
when l05.real_fare = mobile.order_fee * 100 then COALESCE(l05.real_fare, 0)
else COALESCE(mobile.order_fee * 100, 0) end as realfare,
l05.real_fare as l05fee,
mobile.order_fee as mobilefee,
l05.pass_id,
case when l05.real_fare = mobile.order_fee * 100 then 0 else 1 end as change_type,
-1 as sendtocenterflag,
1 as process_result, --状态
COALESCE(l05.fee_fare, 0) as feefare,
l05.bill_no,
l05.sp_pay_type,
case when l05.icard_card_type = 6 then 99 else l05.lane_state end as lanestate,
l05.pay_subclass,
l05.ent_operator_id,
l05.ent_lane_no,
l05.ent_pay_type,
l05.ent_veh_type,
COALESCE(l05.multi_province, 0) multi_province,
l05.fee_version,
l05.trans_occur_time,
l05.mobile_trans_no,
l05.car_license,
case when COALESCE(l05.icard_net_id, '') = '' then '0' else icard_net_id end as icard_net_id,
1000079 as unit_id,
l05.pay_method
from xxxxxx mobile
inner join sssssss l05 on l05.mobile_trans_no = mobile.merchant_ordernum
left join xzxzxz.zzzzzz as j
on (case when length(l05.en_toll_lane_hex) = 10 then l05.en_toll_lane_hex else '' end) = j.organ_hex
left join xzxzxz.zzzzzz as l on l.tollorganid = substr(j.tollorganid,0,19)
left join (select organ_id, organ_hex, organ_character from xzxzxz.zzzzzz where organ_character = 2) as k
on (case when length(l05.en_toll_lane_hex) = 10 then substr(l05.en_toll_lane_hex,0,9) else '' end) = k.organ_hex;
最终SQL执行计划：
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=20940.60..49505613.04 rows=375645860 width=664) (actual time=7241.698..7568.954 rows=10872 loops=1)
Workers Planned: 5
Workers Launched: 5
Buffers: shared hit=222874
-> Hash Join (cost=19940.60..11940027.04 rows=75129172 width=664) (actual time=7231.341..7507.608 rows=1812 loops=6)
Hash Cond: ((l05.mobile_trans_no)::text = (mobile.merchant_ordernum)::text)
Buffers: shared hit=222874
-> Parallel Hash Left Join (cost=19400.78..666831.78 rows=1380924 width=810) (actual time=320.764..6586.378 rows=1039534 loops=6)
Hash Cond: ((CASE WHEN (length((l05.en_toll_lane_hex)::text) = 10) THEN l05.en_toll_lane_hex ELSE ''::character varying END)::text = (j.organ_hex)::text)
Buffers: shared hit=220824
-> Parallel Hash Left Join (cost=4755.65..465553.86 rows=1247441 width=860) (actual time=19.774..4181.245 rows=1039534 loops=6)
Hash Cond: (CASE WHEN (length((l05.en_toll_lane_hex)::text) = 10) THEN substr((l05.en_toll_lane_hex)::text, 0, 9) ELSE ''::text END = (zzzzzz.organ_hex)::text)
Buffers: shared hit=212216
-> Parallel Seq Scan on sssssss l05 (cost=0.00..220399.41 rows=1247441 width=852) (actual time=0.022..926.338 rows=1039534 loops=6)
Buffers: shared hit=207925
-> Parallel Hash (cost=4682.47..4682.47 rows=5854 width=18) (actual time=19.637..19.640 rows=2431 loops=6)
Buckets: 16384 Batches: 1 Memory Usage: 1024kB
Buffers: shared hit=4291
-> Parallel Bitmap Heap Scan on zzzzzz (cost=305.30..4682.47 rows=5854 width=18) (actual time=3.669..16.259 rows=2431 loops=6)
Recheck Cond: (organ_character = 2)
Heap Blocks: exact=815
Buffers: shared hit=4291
-> Bitmap Index Scan on zzzzzz_organ_character_idx (cost=0.00..301.79 rows=14049 width=0) (actual time=2.760..2.761 rows=14585 loops=1)
Index Cond: (organ_character = 2)
Buffers: shared hit=55
-> Parallel Hash (cost=13064.73..13064.73 rows=126432 width=18) (actual time=300.526..300.536 rows=50573 loops=6)
Buckets: 524288 Batches: 1 Memory Usage: 18144kB
Buffers: shared hit=8608
-> Parallel Hash Left Join (cost=7148.72..13064.73 rows=126432 width=18) (actual time=106.734..234.768 rows=50573 loops=6)
Hash Cond: (substr((j.tollorganid)::text, 0, 19) = (l.tollorganid)::text)
Buffers: shared hit=8608
-> Parallel Seq Scan on zzzzzz j (cost=0.00..5568.32 rows=126432 width=31) (actual time=0.042..35.749 rows=50573 loops=6)
Buffers: shared hit=4304
-> Parallel Hash (cost=5568.32..5568.32 rows=126432 width=29) (actual time=106.207..106.210 rows=50573 loops=6)
Buckets: 524288 Batches: 1 Memory Usage: 23072kB
Buffers: shared hit=4304
-> Parallel Seq Scan on zzzzzz l (cost=0.00..5568.32 rows=126432 width=29) (actual time=0.041..40.437 rows=50573 loops=6)
Buffers: shared hit=4304
-> Hash (cost=403.81..403.81 rows=10881 width=234) (actual time=20.655..20.658 rows=10881 loops=6)
Buckets: 16384 Batches: 1 Memory Usage: 926kB
Buffers: shared hit=1770
-> Seq Scan on xxxxxx mobile (cost=0.00..403.81 rows=10881 width=234) (actual time=0.024..11.072 rows=10881 loops=6)
Buffers: shared hit=1770
Planning Time: 1.091 ms
Execution Time: 7574.289 ms
300多秒执行时间降到7秒完成此次的SQL优化。
这次优化将近搞了3小时，一方面是不能远程，我只能发信息要和客户打配合，还有就是函数改写那里花了太多时间。
如果是能远程的话估计1个小时就能搞掂。
标签：rows,..,鸡肋,hex,MergeJoin,cost,PG,time,l05 From： https://www.cnblogs.com/yuzhijian/p/18474020