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. ππππππππ
Β