Location>code7788 >text

pg's MergeJoin is just a piece of shit.

Popularity:728 ℃/2024-10-18 13:52:08

It's been a while since I've written a blog, I'm usually very busy at work and the applications I'm interfacing with now are basically microservice architectures.

Microservices architecture is usually very difficult to encounter complex SQL, the architectural level also restricts the complex SQL is not allowed, usually deal with a simple batch of point-check SQL.

Basically, the content of the optimization is business, architectural changes and development tug-of-war, each SQL deduction milliseconds to engage in this way, concurrency to achieve the overall RT to reduce the optimization targets, it is not interesting.

To be honest, it is still the traditional industry complex SQL fun, last night came to a traditional industry PG slow SQL, just have a case to write a blog, this CASE to get more than two hours or so, is also considered complex SQL.

Slow SQL data volume:

-- -- volume of data
SELECT COUNT(1) FROM xxxxxx  -- 10881
UNION ALL 
SELECT COUNT(1) FROM sssssss   -- 6237204
UNION ALL
SELECT COUNT(1) FROM ;  -- 303437

Slow SQL:

select ,
       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, --state of affairs
       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  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  as l on  = substr(,0,19)
    left join (select organ_id, organ_hex, organ_character from  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;  

Slow SQL execution plan:

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(()::text, 0, 19) = ()::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             

The slow SQL execution time is nearly 300 seconds.

Β 

1, first add index optimization

-- Optimization Step 1: Add Index
  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  ((substr(tollorganid, 0, 19)), organ_hex, organ_character);

SQL and plans executed after indexing

select count(1)
from xxxxxx mobile
         inner join sssssss l05 on l05.mobile_trans_no = mobile.merchant_ordernum
         left join  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  as l on  = substr(,0,19)
    left join (select organ_id, organ_hex, organ_character from  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

The execution speed was reduced to 133 seconds, but it was found that the Merge plan was being followed, which consumes quite a bit of memory per node in the plan:

  • 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's Merge algorithm is a real chicken ribs, personally I think it can be killed, just keep NL and HASH.

Β 

2. Adjustment of session variables

-- These two parameters are session level shutdown parameters, so that you developers can set these two parameters at the session level every time you run this SQL. (This step requires the cooperation of your developer)
set enable_nestloop  = off;
set enable_mergejoin = off;   
set max_parallel_workers_per_gather = 8;

-- Java Code Setting Example
Statement stmt = ()
stmt.execute("SET enable_nestloop = off");
stmt.execute("SET enable_mergejoin = off");
stmt.execute("SET max_parallel_workers_per_gather = 8");

Adjusting session-level variables after SQL and plan

select count(1)
from xxxxxx mobile
         inner join sssssss l05 on l05.mobile_trans_no = mobile.merchant_ordernum
         left join  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  as l on  = substr(,0,19)
    left join (select organ_id, organ_hex, organ_character from  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

You can see that the SQL execution time has dropped from 133 seconds to about 13 seconds, so keep optimizing.

Later I learned that this SQL does not execute many times, so I asked the customer to add a set max_parallel_workers_per_gather = 8, and the SQL can run out of results in 6 seconds.

Β 

3, optimize function logic, function logic into SQL logic

SQL optimization to 6 seconds, coupled with the original function run, the execution time again to more than 60 seconds, looked at the two function logic are relatively simple, (function code will not be put, can not leak the customer code):

1, func1: is to find Julian day to today is how many days.

2, func2: is a date conversion function for passing time plus or minus judgment function.

Both functions are IMMUTABLE state, the logic within the function no optimization space, SQL returns 10872 rows of data, should be different for each row of data date value, need to process 10872 times, here lead to SQL overall time consumption of 60 seconds.

The evaluation was able to replace the function logic with SQL logic, and this piece of rewriting took over an hour.

Final SQL:

select ,                        
    ((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, --state of affairs
       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  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  as l on  = substr(,0,19)
    left join (select organ_id, organ_hex, organ_character from  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;  

Final SQL execution plan:

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(()::text, 0, 19) = ()::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+ seconds execution time down to 7 seconds to complete this SQL optimization.

This optimization took almost 3 hours, partly because I couldn't do it remotely, I had to send messages to have to fight with the client, and partly because the function rewriting took too much time there.

If it's something that can be done remotely I guess it's a 1 hour job. 😁😁😁😁😁😁😁😁

Β