好久沒寫部落格，平時工作非常忙，而且現在對接的應用基本都是微服務架構。
微服務這種架構平時也很難遇到複雜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個小時就能搞掂。😁😁😁