直奔主题，原SQL如下
ta6_pub11@fund60perf=> explain ANALYZE select ta_code,row_number() over( ) as seq_no , ta6_pub11@fund60perf-> prd_code, targ_prd_code,targ_share_class,seller_code,share_class, busin_code,capital_type,individual_or_institution, ta6_pub11@fund60perf-> get_fee_rate_method, min_amt,max_amt,min_hold_days,max_hold_days,min_fee, max_fee,rate_fee,fee_rate_flag, ta6_pub11@fund60perf-> whole_flag,effect_date,down_date ta6_pub11@fund60perf-> from tbfund_c5navtmp3 a ta6_pub11@fund60perf-> where 1=1 and not exists ( select /*+ parallel(a 8 hard) hashjoin(a b) indexscan(a i_tbfund_c5navtmp3_5) */ 1 ta6_pub11@fund60perf(> from tbfund_c5navtmp4 b ta6_pub11@fund60perf(> where a.busin_code_chg = b.busin_code_chg ta6_pub11@fund60perf(> and a.prd_code_chg = b.prd_code_chg ta6_pub11@fund60perf(> and a.share_class = b.share_class ta6_pub11@fund60perf(> and a.seller_code = b.seller_code ta6_pub11@fund60perf(> and a.targ_prd_code_chg = b.targ_prd_code_chg ta6_pub11@fund60perf(> and a.targ_share_class = b.targ_share_class ); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- WindowAgg (cost=57904.53..937429.67 rows=3428899 width=97) (actual time=2461.619..38397.945 rows=11030176 loops=1) -> Merge Anti Join (cost=57904.53..894568.43 rows=3428899 width=89) (actual time=2461.605..26421.565 rows=11030176 loops=1) Merge Cond: (((a.busin_code_chg)::text = (b.busin_code_chg)::text) AND ((a.prd_code_chg)::text = (b.prd_code_chg)::text) AND ((a.share_class)::text = (b.share_class)::text) AND ((a.seller_code)::text = (b.seller_code)::text) AND ((a.targ_prd_code_chg)::text = (b.targ_prd_code_chg)::text) AND ((a.targ_share_class)::text = (b.targ_share_class)::text)) -> Index Scan using i_tbfund_c5navtmp3_5 on tbfund_c5navtmp3 a @"lt#1" (cost=0.43..635373.06 rows=11498375 width=105) (actual time=0.033..13857.823 rows=11480208 loops=1) -> Sort (cost=57904.10..59061.00 rows=462760 width=25) (actual time=2461.558..3249.734 rows=462760 loops=1) Sort Key: b.busin_code_chg, b.prd_code_chg, b.share_class, b.seller_code, b.targ_prd_code_chg, b.targ_share_class Sort Method: external merge Disk: 15920kB -> Seq Scan on tbfund_c5navtmp4 b @"lt#0" (cost=0.00..8030.60 rows=462760 width=25) (actual time=0.008..52.361 rows=462760 loops=1) Planning Time: 2.479 ms Execution Time: 39237.777 ms (10 rows)
执行39秒
改写如下
ta6_pub11@fund60perf=> explain analyze select /*+parallel(a 16 hard) parallel(b 16 hard) hashjoin(a b)*/ a.ta_code,row_number() over( ) as seq_no , prd_code, targ_prd_code,a.targ_share_class,a.seller_code,a.share_class, a.busin_code,a.capital_type,a.individual_or_institution, a.get_fee_rate_method, a.min_amt,a.max_amt,a.min_hold_days,a.max_hold_days,a.min_fee, a.max_fee,a.rate_fee,a.fee_rate_flag, a.whole_flag,a.effect_date,a.down_date from tbfund_c5navtmp3 a left join tbfund_c5navtmp4 b on (a.busin_code_chg = b.busin_code_chg and a.prd_code_chg = b.prd_code_chg and a.share_class = b.share_class and a.seller_code = b.seller_code and a.targ_prd_code_chg = b.targ_prd_code_chg and a.targ_share_class = b.targ_share_class ) where a.busin_code_chg is not null and a.prd_code_chg is not null and a.share_class is not null and a.seller_code is not null and a.targ_prd_code_chg is not null and a.targ_share_class is not null and b.busin_code_chg is null and b.prd_code_chg is null and b.share_class is null and b.seller_code is null and b.targ_prd_code_chg is null and b.targ_share_class is null; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ WindowAgg (cost=10723.05..2236536.59 rows=3428899 width=97) (actual time=55.415..9255.268 rows=11030176 loops=1) -> Gather (cost=10723.05..2193675.35 rows=3428899 width=89) (actual time=55.399..2634.608 rows=11030176 loops=1) Workers Planned: 16 Workers Launched: 16 -> Parallel Hash Anti Join (cost=723.05..1840785.45 rows=214306 width=89) (actual time=34.350..1062.279 rows=648834 loops=17) Hash Cond: (((a.busin_code_chg)::text = (b.busin_code_chg)::text) AND ((a.prd_code_chg)::text = (b.prd_code_chg)::text) AND ((a.share_class)::text = (b.share_class)::text) AND ((a.seller_code)::text = (b.seller_code)::text) AND ((a.targ_prd_code_chg)::text = (b.targ_prd_code_chg)::text) AND ((a.targ_share_class)::text = (b.targ_share_class)::text)) -> Parallel Seq Scan on tbfund_c5navtmp3 a @"lt#0" (cost=0.00..0.00 rows=718648 width=105) (actual time=1.425..459.686 rows=675306 loops=17) Filter: ((busin_code_chg IS NOT NULL) AND (prd_code_chg IS NOT NULL) AND (share_class IS NOT NULL) AND (seller_code IS NOT NULL) AND (targ_prd_code_chg IS NOT NULL) AND (targ_share_class IS NOT NULL)) -> Parallel Hash (cost=0.00..0.00 rows=28922 width=25) (actual time=32.498..32.502 rows=27221 loops=17) Buckets: 524288 Batches: 1 Memory Usage: 33344kB -> Parallel Seq Scan on tbfund_c5navtmp4 b @"lt#0" (cost=0.00..0.00 rows=28922 width=25) (actual time=0.126..4.394 rows=27221 loops=17) Planning Time: 1.859 ms Execution Time: 9973.605 ms (13 rows)
走hash join连接