SQL如下，如果如何指定hint和设置优化器参数等都尝试过了，也走不了并行，执行计划如下
SET parallel_setup_cost to 0;
SET parallel_tuple_cost to 0;
SET min_parallel_table_scan_size to 0;
SET min_parallel_index_scan_size to 0;
explain analyze SELECT /*+parallel(fag 10 hard ) parallel(a 10 hard )*/ '01' ta_code,seq_no, CASE a.seller_share_class WHEN 'AB' THEN a.prd_code ELSE COALESCE(b.out_prd_code,.prd_code) END prd_code, CASE a.seller_share_class WHEN 'AB' THEN a.targ_prd_code ELSE COALESCE(c.out_prd_code,a.targ_prd_code) END targ_prd_code, targ_share_class,a.seller_code ,a.share_class,
COALESCE(d.cfm_busin,CONCAT('1',SUBSTR(a.out_busin_code,2,2))) busin_code, capital_type,individual_or_institution,get_fee_rate_method, min_amt,max_amt, min_hold_days,max_holddays,min_fee, max_fee,fee_rate rate_fee,fee_rate_flag,whole_flag, effect_date, 20220525 down_date, a.prd_code prd_code_chg, a.targ_prd_code targ_prd_code_chg, a.busin_code busin_code_chg
FROM tbfundprdsalelimit fag, tbfundc5navtmp3_2 a
LEFT JOIN tbfundprdcodechange b ON (a.prd_code = b.prd_code AND a.ori_share_class = b.share_class )
LEFT JOIN tbfundprdcodechange c ON (a.targ_prd_code = c.prd_code AND a.ori_targ_share_class = c.share_class )
LEFT JOIN tbbusincodechg d ON (a.busin_code = d.busin_code AND a.out_busin_code = d.req_busin and d.conv_dir = '0')
where (case when a.busin_code = '13' then a.targ_prd_code else a.prd_code end) = fag.prd_code and a.seller_code = fag.seller_code
and (case when a.busin_code = '13' then fag.sign_time else 20220525 end) <= 20220525 and instr(fag.seller_share_class, (case a.ori_targ_share_class when '*' then fag.seller_sare_class else a.ori_targ_share_class end)) > 0 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=15201.06..850490.44 rows=1650920 width=445) (actual time=307.445..98865.795 rows=11480206 loops=1)
Hash Cond: (((a.busin_code)::text = (d.busin_code)::text) AND ((a.out_busin_code)::text = (d.req_busin)::text))
-> Hash Left Join (cost=15196.12..794969.26 rows=1650920 width=105) (actual time=307.370..88197.929 rows=11480206 loops=1)
Hash Cond: (((a.targ_prd_code)::text = (c.prd_code)::text) AND ((a.ori_targ_share_class)::text = (c.share_class)::text))
-> Hash Left Join (cost=15194.42..776556.41 rows=1650920 width=100) (actual time=307.348..82985.140 rows=11480206 loops=1)
Hash Cond: (((a.prd_code)::text = (b.prd_code)::text) AND ((a.ori_share_class)::text = (b.share_class)::text))
-> Hash Join (cost=15192.72..757125.66 rows=1650920 width=95) (actual time=307.286..77009.596 rows=11480206 loops=1)
Hash Cond: (((CASE WHEN ((a.busin_code)::text = '13'::text) THEN a.targ_prd_code ELSE a.prd_code END)::text = (fag.prd_code)::text) AND ((a.seller_code)::text = (fag.seller_cod)::text))
Join Filter: ((CASE WHEN ((a.busin_code)::text = '13'::text) THEN fag.sign_time ELSE 20220525 END <= 20220525) AND (instr(fag.seller_share_class, CASE a.ori_targ_share_class WHEN '*'::text THEN fag.seller_share_class ELSE a.ori_targ_share_class END) > 0))
Rows Removed by Join Filter: 3413365
-> Seq Scan on tbfundc5navtmp3_2 a @"lt#0" (cost=0.00..661994.71 rows=14871871 width=95) (actual time=106.894..2670.870 rows=14893619 loops=1)
-> Hash (cost=11601.89..11601.89 rows=239389 width=17) (actual time=200.142..200.143 rows=239388 loops=1)
Buckets: 262144 Batches: 1 Memory Usage: 13741kB
-> Seq Scan on tbfundprdsalelimit fag @"lt#0" (cost=0.00..11601.89 rows=239389 width=17) (actual time=0.123..120.676 rows=239388 loops=1)
-> Hash (cost=1.28..1.28 rows=28 width=16) (actual time=0.049..0.050 rows=28 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on tbfundprdcodechange b @"lt#0" (cost=0.00..1.28 rows=28 width=16) (actual time=0.018..0.023 rows=28 loops=1)
-> Hash (cost=1.28..1.28 rows=28 width=16) (actual time=0.017..0.017 rows=28 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on tbfundprdcodechange c @"lt#0" (cost=0.00..1.28 rows=28 width=16) (actual time=0.003..0.008 rows=28 loops=1)
-> Hash (cost=3.69..3.69 rows=83 width=10) (actual time=0.065..0.065 rows=83 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on tbbusincodechg d @"lt#0" (cost=0.00..3.69 rows=83 width=10) (actual time=0.015..0.039 rows=83 loops=1)
Filter: ((conv_dir)::text = '0'::text)
Rows Removed by Filter: 52
Planning Time: 2.224 ms
Execution Time: 99822.615 ms
(27 rows)
究其原因是因为instr为unsafe函数
postgres.cn docs 13 when-can-parallel-query-be-used.html处有说明，要重建函数
CREATE or replace FUNCTION pg_catalog.instr(str text, patt text, start int, nth int) RETURNS int AS 'orafce','plvstr_instr4' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE ; CREATE or replace FUNCTION pg_catalog.instr(str text, patt text, start int) RETURNS int AS 'orafce','plvstr_instr3' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; CREATE or replace FUNCTION pg_catalog.instr(str text, patt text) RETURNS int AS 'orafce','plvstr_instr2' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
替换成功之后 可以正常走并行了