Re: CAST from numeric(18,3) to numeric doesnt work, posgresql 13.3 - Mailing list pgsql-bugs

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