PG 的 MergeJoin 就是雞肋

小至尖尖發表於2024-10-18

好久沒寫部落格，平時工作非常忙，而且現在對接的應用基本都是微服務架構。

微服務這種架構平時也很難遇到複雜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個小時就能搞掂。😁😁😁

雞肋的Redis事務
2023-02-14
Redis
Uber三個雞肋漏洞的妙用
2020-08-19
Mybatis-聊一聊雞肋的快取體系
2019-04-24
MyBatis快取
當紅炸子雞圖資料庫商業化落地“雞肋”？
2022-05-31
資料庫
能把SAP系統玩成雞肋的公司，太有才了！
2022-06-23
（騷操作）Laravel 中一個鮮為人知的雞肋功能
2019-01-23
Laravel
Django認證系統並不雞肋反而很重要
2020-12-11
Django
冰鑑科技CEO顧凌雲： AI金融產品是「雞肋」還是「雞腿」？
2020-05-27
AI
為什麼有人說 Python 多執行緒是雞肋？
2018-05-04
Python執行緒
屢被吐槽的充電機器人，到底是不是雞肋
2021-11-25
機器人
智慧市場爭紅奪利，但產品雞肋依舊
2019-07-17
十多年了，介面自動化測試越來越雞肋？
2024-04-08
大家都覺得PSV這個功能雞肋，但是好多遊戲都用了
2021-05-27
遊戲
開發者談遊戲中過多的文字內容是否會淪為雞肋
2022-03-23
遊戲
監測資料可預測疾病？可穿戴裝置或將不再“雞肋”
2018-03-08
Mac便箋基本操作|便箋使用太雞肋？那是你不知道這幾個快捷鍵！
2020-11-29
Mac
誰說格鬥遊戲劇情模式雞肋？王者IP新遊《代號：破曉》PVE模式詳解
2022-12-16
遊戲模式
求用100文錢買100只雞公雞母雞小雞的只數
2020-12-14
pg升級(pg14-pg15)主從環境
2024-05-15
join()方法的神奇用處與Intern機制的軟肋
2018-12-01
【PG】PG在linux上的線上和離線安裝
2019-12-26
Linux
PG備份之pg_basebackup工具
2024-03-16
PostgreSQL DBA(40) - PG 12 pg_promote
2019-06-20
SQL
pg_resetwal pg_resetxlog 重整 pg資料庫 wal 與pg_controldata 。資料庫恢復。
2022-12-02
資料庫LDA
pg12中pg_dump/pg_dumpall新增選項介紹
2022-06-02
PG-pg資料庫安裝vector
2024-12-03
資料庫
PG備份恢復工具pg_probackup
2021-02-20
如何聊自己的軟肋，向面試官展示自己的潛能
2019-08-13
面試
pg_file_settings和pg_settings系統檢視的區別
2024-11-23
【PG資料庫】PG資料庫的安裝及連線方法
2022-05-19
資料庫
《自然》雙重磅：擊中癌症轉移軟肋！
2024-03-07
PG中級證書到手，PostgreSQL(PG)認證
2023-01-05
SQL
Quackerjack pg walkthrough
2024-12-05
PG: Utility queries
2023-02-13
PG double buffering的最佳化
2024-02-04
sqoop如何指定pg庫的模式
2022-01-22
OOP模式
百雞問題擴充套件-N雞問題N元錢買N只雞，公雞每隻5元，母雞每隻3元，小雞1元3只，N元錢必須剛好買N只雞，而且雞必須整隻買，不能劈開買。
2020-11-18
套件
炒雞好用的Markdown語法
2024-08-11

PG 的 MergeJoin 就是雞肋

相關文章