Re: CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3

A customer reported a planning regression from 11.5 to 12.9. After
bisecting, it seems the fix for the thread subject here, commit
f230614da28, broke partition pruning in some cases. Here's a
reproducer:

drop table if exists test_pruning;
create table test_pruning (account_id character(16) primary key)
partition by hash(account_id);
create table p0_test_pruning partition of test_pruning
for values with (modulus 5, remainder 0);
create table p1_test_pruning partition of test_pruning
for values with (modulus 5, remainder 1);
create table p2_test_pruning partition of test_pruning
for values with (modulus 5, remainder 2);
create table p3_test_pruning partition of test_pruning
for values with (modulus 5, remainder 3);
create table p4_test_pruning partition of test_pruning
for values with (modulus 5, remainder 4);

insert into test_pruning select 'XY' || lpad(i::text, 14, '0') from
generate_series(1,1000000,1) as i;

-- explicit cast on both operands
EXPLAIN (ANALYZE)
SELECT COUNT(*) FROM test_pruning WHERE account_id::BPCHAR =
'XY99999999999999'::BPCHAR;

-- explicit cast only on const
EXPLAIN (ANALYZE)
SELECT COUNT(*) FROM test_pruning WHERE account_id = 'XY99999999999999'::BPCHAR;

These queries both allowed partition pruning before, but with this
commit only the latter does:

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=12159.67..12159.68 rows=1 width=8) (actual
time=66.891..68.641 rows=1 loops=1)
-> Gather (cost=12159.46..12159.67 rows=2 width=8) (actual
time=66.784..68.635 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=11159.46..11159.47 rows=1
width=8) (actual time=56.140..56.142 rows=1 loops=3)
-> Parallel Append (cost=0.00..11156.64 rows=1128
width=0) (actual time=56.137..56.138 rows=0 loops=3)
-> Parallel Seq Scan on p2_test_pruning
(cost=0.00..2238.25 rows=320 width=0) (actual time=35.421..35.421
rows=0 loops=1)
Filter: ((account_id)::bpchar =
'XY99999999999999'::bpchar)
Rows Removed by Filter: 200799
-> Parallel Seq Scan on p3_test_pruning
(cost=0.00..2236.50 rows=319 width=0) (actual time=39.589..39.589
rows=0 loops=1)
Filter: ((account_id)::bpchar =
'XY99999999999999'::bpchar)
Rows Removed by Filter: 200523
-> Parallel Seq Scan on p4_test_pruning
(cost=0.00..2227.75 rows=318 width=0) (actual time=10.156..10.156
rows=0 loops=3)
Filter: ((account_id)::bpchar =
'XY99999999999999'::bpchar)
Rows Removed by Filter: 66599
-> Parallel Seq Scan on p0_test_pruning
(cost=0.00..2224.25 rows=318 width=0) (actual time=17.785..17.785
rows=0 loops=2)
Filter: ((account_id)::bpchar =
'XY99999999999999'::bpchar)
Rows Removed by Filter: 99715
-> Parallel Seq Scan on p1_test_pruning
(cost=0.00..2224.25 rows=318 width=0) (actual time=27.336..27.336
rows=0 loops=1)
Filter: ((account_id)::bpchar =
'XY99999999999999'::bpchar)
Rows Removed by Filter: 199452
Planning Time: 0.476 ms
Execution Time: 68.716 ms
(23 rows)

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=8.44..8.45 rows=1 width=8) (actual time=0.016..0.016
rows=1 loops=1)
-> Index Only Scan using p2_test_pruning_pkey on p2_test_pruning
(cost=0.42..8.44 rows=1 width=0) (actual time=0.014..0.014 rows=0
loops=1)
Index Cond: (account_id = 'XY99999999999999'::bpchar)
Heap Fetches: 0
Planning Time: 0.071 ms
Execution Time: 0.038 ms
(6 rows)

--
John Naylor
EDB: http://www.enterprisedb.com