PostgreSQL優化案例之 反連線與外連線等價改寫

直奔主題，原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連線