public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedPotential partition pruning regression on PostgreSQL 18
5+ messages / 3 participants
[nested] [flat]
* Potential partition pruning regression on PostgreSQL 18
@ 2026-04-01 11:56 Cándido Antonio Martínez Descalzo <candido@ninehq.com>
2026-04-01 23:00 ` Re: Potential partition pruning regression on PostgreSQL 18 David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Cándido Antonio Martínez Descalzo @ 2026-04-01 11:56 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi all,
We noticed that one of our queries unexpectedly stopped applying partition
pruning on PG18, although it applies it on PG16 and PG17. The issue has
been replicated on Linux and macOS.
Failing to apply partition pruning significantly impacts the performance of
these queries.
We recreated the issue using a simplified schema and query. Details on the
schema, query and resulting plans in PG17 and PG18 are provided below. Some
changes in the query restore partition pruning in PG18, specifically:
- Replacing the view and date condition used with a sub-query or CTE
with the same condition restores partition pruning (updated query and plan
provided further below)
- Keeping the view and using a single "group by" instead of multiple
grouping sets restores partition pruning (updated query and plan provided
further below)
Does anybody know if there is a documented behaviour change in PG18 that
could explain this or if this is a known issue?
Many thanks,
Cándido Martínez
ninehq
This is the schema used:
create table entity (
id integer primary key,
name varchar(255) unique not null
);
insert into entity (id, name)
select i, 'Entity ' || i from generate_series(1, 1000, 1) g(i);
create table entity_tags (
entity_id integer not null references entity(id),
from_day date not null,
to_day date not null,
tag_1 text not null,
tag_2 text not null,
primary key (entity_id, from_day)
);
insert into entity_tags
select id, '2025-01-01'::date, '9999-12-31'::date, 'Tag 1-' ||
random(1,50), 'Tag 2-' || random(1, 10)
from entity where id % 3 = 0;
insert into entity_tags
select id, '2025-01-01'::date, '2026-01-31'::date, 'Tag 1-' ||
random(1,50), 'Tag 2-' || random(1, 10)
from entity where id % 3 = 1;
insert into entity_tags
select id, '2026-02-01'::date, '9999-12-31'::date, 'Tag 1-' ||
random(1,50), 'Tag 2-' || random(1, 10)
from entity where id % 3 = 1;
insert into entity_tags
select id, '2025-01-01'::date, '2026-02-28'::date, 'Tag 1-' ||
random(1,50), 'Tag 2-' || random(1, 10)
from entity where id % 3 = 2;
insert into entity_tags
select id, '2026-03-01'::date, '9999-12-31'::date, 'Tag 1-' ||
random(1,50), 'Tag 2-' || random(1, 10)
from entity where id % 3 = 2;
create table monthly_data (
month date not null,
external_ref text not null,
entity_id integer not null references entity(id),
duration integer not null,
counter integer not null,
amount integer not null
) partition by RANGE (month);
create index on monthly_data (external_ref);
create index on monthly_data (entity_id);
create view monthly_data_view as select * from monthly_data;
create table monthly_data_202601 partition of monthly_data for values from (
'2026-01-01') to ('2026-01-31');
create table monthly_data_202602 partition of monthly_data for values from (
'2026-02-01') to ('2026-02-28');
create table monthly_data_202603 partition of monthly_data for values from (
'2026-03-01') to ('2026-03-31');
insert into monthly_data
with m as (
select d::date as month from generate_series('2026-01-01'::date, '2026-03-31
'::date, '1 month') g(d)
)
select m.month, 'ext-' || random(1, 50000), random(1, 1000), random(1, 1000),
random(1, 1000), random(1, 100)
from generate_series(1, 3000000, 1) g(i), m;
analyze entity, entity_tags, monthly_data;
And this is the query:
select m.external_ref, t.tag_1, t.tag_2, sum(m.duration) as duration,
sum(m.counter) as counter, sum(m.amount) as amount
from monthly_data_view m
join entity_tags t on m.entity_id = t.entity_id and m.month between
t.from_day and t.to_day
where m.month between '2026-02-01'::date and '2026-02-28'::date
group by m.external_ref, grouping sets ((), t.tag_1, t.tag_2);
*PostgreSQL 17 Plan:*
GroupAggregate (cost=94584.40..253820.84 rows=1105572 width=49) (actual
time=642.913..2291.658 rows=2271176 loops=1)
Output: monthly_data.external_ref, t.tag_1, t.tag_2,
sum(monthly_data.duration), sum(monthly_data.counter),
sum(monthly_data.amount)
Group Key: monthly_data.external_ref, t.tag_1
Group Key: monthly_data.external_ref
Sort Key: monthly_data.external_ref, t.tag_2
Group Key: monthly_data.external_ref, t.tag_2
Buffers: shared hit=32066 read=13, temp read=36690 written=36703
I/O Timings: shared read=0.697, temp read=32.232 write=197.328
-> Gather Merge (cost=94584.40..159286.08 rows=555539 width=37) (actual
time=642.904..977.809 rows=3000000 loops=1)
Output: monthly_data.external_ref, t.tag_1, t.tag_2,
monthly_data.duration, monthly_data.counter, monthly_data.amount
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=32066 read=13, temp read=18345 written=18351
I/O Timings: shared read=0.697, temp read=18.407 write=130.461
-> Sort (cost=93584.38..94163.07 rows=231475 width=37) (actual
time=622.100..709.953 rows=1000000 loops=3)
Output: monthly_data.external_ref, t.tag_1, t.tag_2,
monthly_data.duration, monthly_data.counter, monthly_data.amount
Sort Key: monthly_data.external_ref, t.tag_1
Sort Method: external merge Disk: 52096kB
Buffers: shared hit=32066 read=13, temp read=18345
written=18351
I/O Timings: shared read=0.697, temp read=18.407 write=130.461
Worker 0: actual time=614.585..706.233 rows=976888 loops=1
Sort Method: external merge Disk: 47792kB
Buffers: shared hit=10526, temp read=5974 written=5976
I/O Timings: temp read=6.759 write=49.156
Worker 1: actual time=609.153..697.519 rows=958096 loops=1
Sort Method: external merge Disk: 46872kB
Buffers: shared hit=10388, temp read=5859 written=5861
I/O Timings: temp read=5.899 write=43.593
-> Nested Loop (cost=0.29..72959.38 rows=231475 width=37)
(actual time=0.139..248.122 rows=1000000 loops=3)
Output: monthly_data.external_ref, t.tag_1, t.tag_2,
monthly_data.duration, monthly_data.counter, monthly_data.amount
Buffers: shared hit=32050 read=13
I/O Timings: shared read=0.697
Worker 0: actual time=0.061..243.302 rows=976888
loops=1
Buffers: shared hit=10518
Worker 1: actual time=0.058..246.889 rows=958096
loops=1
Buffers: shared hit=10380
-> Parallel Seq Scan on public.monthly_data_202602
monthly_data (cost=0.00..40809.00 rows=1250000 width=29) (actual
time=0.014..64.695 rows=1000000 loops=3)
Output: monthly_data.external_ref,
monthly_data.duration, monthly_data.counter, monthly_data.amount,
monthly_data.entity_id, monthly_data.month
Filter: ((monthly_data.month >=
'2026-02-01'::date) AND (monthly_data.month <= '2026-02-28'::date))
Buffers: shared hit=22059
Worker 0: actual time=0.017..64.085 rows=976888
loops=1
Buffers: shared hit=7183
Worker 1: actual time=0.018..67.602 rows=958096
loops=1
Buffers: shared hit=7045
-> Memoize (cost=0.29..0.31 rows=1 width=28) (actual
time=0.000..0.000 rows=1 loops=3000000)
Output: t.tag_1, t.tag_2, t.entity_id,
t.from_day, t.to_day
Cache Key: monthly_data.month,
monthly_data.entity_id
Cache Mode: binary
Hits: 1064016 Misses: 1000 Evictions: 0
Overflows: 0 Memory Usage: 133kB
Buffers: shared hit=9991 read=13
I/O Timings: shared read=0.697
Worker 0: actual time=0.000..0.000 rows=1
loops=976888
Hits: 975888 Misses: 1000 Evictions: 0
Overflows: 0 Memory Usage: 133kB
Buffers: shared hit=3335
Worker 1: actual time=0.000..0.000 rows=1
loops=958096
Hits: 957096 Misses: 1000 Evictions: 0
Overflows: 0 Memory Usage: 133kB
Buffers: shared hit=3335
-> Index Scan using entity_tags_pkey on
public.entity_tags t (cost=0.28..0.30 rows=1 width=28) (actual
time=0.002..0.002 rows=1 loops=3000)
Output: t.tag_1, t.tag_2, t.entity_id,
t.from_day, t.to_day
Index Cond: ((t.entity_id =
monthly_data.entity_id) AND (t.from_day <= monthly_data.month))
Filter: (monthly_data.month <= t.to_day)
Rows Removed by Filter: 0
Buffers: shared hit=9991 read=13
I/O Timings: shared read=0.697
Worker 0: actual time=0.002..0.002 rows=1
loops=1000
Buffers: shared hit=3335
Worker 1: actual time=0.001..0.002 rows=1
loops=1000
Buffers: shared hit=3335
*PostgreSQL 18 plan (no partition pruning):*
HashAggregate (cost=229746.36..242370.87 rows=12200 width=72) (actual
time=1621.794..2508.533 rows=2262361.00 loops=1)
Output: monthly_data.external_ref, t.tag_1, t.tag_2,
sum(monthly_data.duration), sum(monthly_data.counter),
sum(monthly_data.amount)
Hash Key: monthly_data.external_ref, t.tag_1
Hash Key: monthly_data.external_ref
Hash Key: monthly_data.external_ref, t.tag_2
Batches: 13 Memory Usage: 54433kB Disk Usage: 250536kB
Buffers: shared hit=66216, temp read=31017 written=58146
I/O Timings: temp read=29.524 write=118.672
-> Gather (cost=1050.51..222800.52 rows=555667 width=60) (actual
time=93.721..192.443 rows=3000000.00 loops=1)
Output: monthly_data.external_ref, t.tag_1, t.tag_2,
monthly_data.duration, monthly_data.counter, monthly_data.amount
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=66216
-> Hash Join (cost=50.51..166233.82 rows=231528 width=60) (actual
time=63.866..320.145 rows=1000000.00 loops=3)
Output: monthly_data.external_ref, t.tag_1, t.tag_2,
monthly_data.duration, monthly_data.counter, monthly_data.amount
Hash Cond: (monthly_data.entity_id = t.entity_id)
Join Filter: ((monthly_data.month >= t.from_day) AND
(monthly_data.month <= t.to_day))
Rows Removed by Join Filter: 667154
Buffers: shared hit=66216
Worker 0: actual time=0.852..768.416 rows=2995648.00 loops=1
Buffers: shared hit=22040
Worker 1: actual time=97.229..97.847 rows=2176.00 loops=1
Buffers: shared hit=22088
-> Parallel Append (cost=0.00..128677.01 rows=1250002
width=52) (actual time=63.442..158.419 rows=1000000.00 loops=3)
Buffers: shared hit=66177
Worker 0: actual time=0.032..284.520 rows=2995648.00
loops=1
Buffers: shared hit=22027
Worker 1: actual time=96.963..97.184 rows=2176.00
loops=1
Buffers: shared hit=22075
-> Parallel Seq Scan on public.monthly_data_202601
monthly_data_1 (cost=0.00..40809.00 rows=1 width=52) (actual
time=96.957..96.957 rows=0.00 loops=1)
Output: monthly_data_1.external_ref,
monthly_data_1.duration, monthly_data_1.counter, monthly_data_1.amount,
monthly_data_1.entity_id, monthly_data_1.month
Filter: ((monthly_data_1.month >=
'2026-02-01'::date) AND (monthly_data_1.month <= '2026-02-28'::date))
Rows Removed by Filter: 3000000
Buffers: shared hit=22059
Worker 1: actual time=96.957..96.957 rows=0.00
loops=1
Buffers: shared hit=22059
-> Parallel Seq Scan on public.monthly_data_202602
monthly_data_2 (cost=0.00..40809.00 rows=1250000 width=52) (actual
time=0.013..62.957 rows=1000000.00 loops=3)
Output: monthly_data_2.external_ref,
monthly_data_2.duration, monthly_data_2.counter, monthly_data_2.amount,
monthly_data_2.entity_id, monthly_data_2.month
Filter: ((monthly_data_2.month >=
'2026-02-01'::date) AND (monthly_data_2.month <= '2026-02-28'::date))
Buffers: shared hit=22059
Worker 0: actual time=0.032..188.573
rows=2995648.00 loops=1
Buffers: shared hit=22027
Worker 1: actual time=0.005..0.153 rows=2176.00
loops=1
Buffers: shared hit=16
-> Parallel Seq Scan on public.monthly_data_202603
monthly_data_3 (cost=0.00..40809.00 rows=1 width=52) (actual
time=93.328..93.328 rows=0.00 loops=1)
Output: monthly_data_3.external_ref,
monthly_data_3.duration, monthly_data_3.counter, monthly_data_3.amount,
monthly_data_3.entity_id, monthly_data_3.month
Filter: ((monthly_data_3.month >=
'2026-02-01'::date) AND (monthly_data_3.month <= '2026-02-28'::date))
Rows Removed by Filter: 3000000
Buffers: shared hit=22059
-> Hash (cost=29.67..29.67 rows=1667 width=28) (actual
time=0.412..0.412 rows=1667.00 loops=3)
Output: t.tag_1, t.tag_2, t.entity_id, t.from_day,
t.to_day
Buckets: 2048 Batches: 1 Memory Usage: 120kB
Buffers: shared hit=39
Worker 0: actual time=0.807..0.807 rows=1667.00 loops=1
Buffers: shared hit=13
Worker 1: actual time=0.248..0.248 rows=1667.00 loops=1
Buffers: shared hit=13
-> Seq Scan on public.entity_tags t (cost=0.00..29.67
rows=1667 width=28) (actual time=0.058..0.222 rows=1667.00 loops=3)
Output: t.tag_1, t.tag_2, t.entity_id,
t.from_day, t.to_day
Buffers: shared hit=39
Worker 0: actual time=0.104..0.435 rows=1667.00
loops=1
Buffers: shared hit=13
Worker 1: actual time=0.058..0.137 rows=1667.00
loops=1
Buffers: shared hit=13
*On PG18, replacing the monthly_data_view and month condition with a
sub-query or CTE restores partition pruning:*
with m as (
select * from monthly_data where month between '2026-02-01'::date
and '2026-02-28'::date
)
select m.external_ref, t.tag_1, t.tag_2, sum(m.duration) as duration,
sum(m.counter) as counter, sum(m.amount) as amount
from m
join entity_tags t on m.entity_id = t.entity_id and m.month between
t.from_day and t.to_day
group by m.external_ref, grouping sets ((), t.tag_1, t.tag_2);
HashAggregate (cost=141878.30..154502.80 rows=12200 width=72) (actual
time=1583.549..2502.394 rows=2262361.00 loops=1)
Output: monthly_data.external_ref, t.tag_1, t.tag_2,
sum(monthly_data.duration), sum(monthly_data.counter),
sum(monthly_data.amount)
Hash Key: monthly_data.external_ref, t.tag_1
Hash Key: monthly_data.external_ref
Hash Key: monthly_data.external_ref, t.tag_2
Batches: 13 Memory Usage: 54433kB Disk Usage: 250552kB
Buffers: shared hit=22098, temp read=31016 written=58135
I/O Timings: temp read=27.912 write=116.172
-> Gather (cost=1050.51..134932.46 rows=555667 width=60) (actual
time=1.314..105.099 rows=3000000.00 loops=1)
Output: monthly_data.external_ref, t.tag_1, t.tag_2,
monthly_data.duration, monthly_data.counter, monthly_data.amount
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=22098
-> Hash Join (cost=50.51..78365.76 rows=231528 width=60) (actual
time=0.783..239.677 rows=1000000.00 loops=3)
Output: monthly_data.external_ref, t.tag_1, t.tag_2,
monthly_data.duration, monthly_data.counter, monthly_data.amount
Hash Cond: (monthly_data.entity_id = t.entity_id)
Join Filter: ((monthly_data.month >= t.from_day) AND
(monthly_data.month <= t.to_day))
Rows Removed by Join Filter: 667154
Buffers: shared hit=22098
Worker 0: actual time=0.726..698.352 rows=2969536.00 loops=1
Buffers: shared hit=21848
Worker 1: actual time=0.653..16.148 rows=26112.00 loops=1
Buffers: shared hit=205
-> Parallel Seq Scan on public.monthly_data_202602
monthly_data (cost=0.00..40809.00 rows=1250000 width=52) (actual
time=0.022..68.714 rows=1000000.00 loops=3)
Output: monthly_data.external_ref,
monthly_data.duration, monthly_data.counter, monthly_data.amount,
monthly_data.entity_id, monthly_data.month
Filter: ((monthly_data.month >= '2026-02-01'::date) AND
(monthly_data.month <= '2026-02-28'::date))
Buffers: shared hit=22059
Worker 0: actual time=0.030..199.783 rows=2969536.00
loops=1
Buffers: shared hit=21835
Worker 1: actual time=0.023..5.233 rows=26112.00
loops=1
Buffers: shared hit=192
-> Hash (cost=29.67..29.67 rows=1667 width=28) (actual
time=0.749..0.749 rows=1667.00 loops=3)
Output: t.tag_1, t.tag_2, t.entity_id, t.from_day,
t.to_day
Buckets: 2048 Batches: 1 Memory Usage: 120kB
Buffers: shared hit=39
Worker 0: actual time=0.679..0.679 rows=1667.00 loops=1
Buffers: shared hit=13
Worker 1: actual time=0.621..0.622 rows=1667.00 loops=1
Buffers: shared hit=13
-> Seq Scan on public.entity_tags t (cost=0.00..29.67
rows=1667 width=28) (actual time=0.058..0.388 rows=1667.00 loops=3)
Output: t.tag_1, t.tag_2, t.entity_id,
t.from_day, t.to_day
Buffers: shared hit=39
Worker 0: actual time=0.092..0.420 rows=1667.00
loops=1
Buffers: shared hit=13
Worker 1: actual time=0.072..0.321 rows=1667.00
loops=1
Buffers: shared hit=13
*On PG18 pruning is also restored keeping the view but performing a single
"group by" instead of multiple grouping sets:*
select t.tag_1, sum(m.duration) as duration, sum(m.counter) as counter, sum(
m.amount) as amount
from monthly_data_view m
join entity_tags t on m.entity_id = t.entity_id and m.month between t.from_day
and t.to_day
where m.month between '2026-02-01'::date and '2026-02-28'::date
group by t.tag_1;
Finalize GroupAggregate (cost=81682.97..81698.65 rows=50 width=32) (actual
time=356.116..358.029 rows=50.00 loops=1)
Output: t.tag_1, sum(monthly_data.duration), sum(monthly_data.counter),
sum(monthly_data.amount)
Group Key: t.tag_1
Buffers: shared hit=22114
-> Gather Merge (cost=81682.97..81696.95 rows=120 width=32) (actual
time=356.111..358.009 rows=150.00 loops=1)
Output: t.tag_1, (PARTIAL sum(monthly_data.duration)), (PARTIAL
sum(monthly_data.counter)), (PARTIAL sum(monthly_data.amount))
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=22114
-> Sort (cost=80682.95..80683.07 rows=50 width=32) (actual
time=349.568..349.570 rows=50.00 loops=3)
Output: t.tag_1, (PARTIAL sum(monthly_data.duration)),
(PARTIAL sum(monthly_data.counter)), (PARTIAL sum(monthly_data.amount))
Sort Key: t.tag_1
Sort Method: quicksort Memory: 27kB
Buffers: shared hit=22114
Worker 0: actual time=346.658..346.660 rows=50.00 loops=1
Sort Method: quicksort Memory: 27kB
Buffers: shared hit=7385
Worker 1: actual time=346.663..346.665 rows=50.00 loops=1
Sort Method: quicksort Memory: 27kB
Buffers: shared hit=7235
-> Partial HashAggregate (cost=80681.04..80681.54 rows=50
width=32) (actual time=349.530..349.533 rows=50.00 loops=3)
Output: t.tag_1, PARTIAL sum(monthly_data.duration),
PARTIAL sum(monthly_data.counter), PARTIAL sum(monthly_data.amount)
Group Key: t.tag_1
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=22098
Worker 0: actual time=346.608..346.611 rows=50.00
loops=1
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=7377
Worker 1: actual time=346.615..346.618 rows=50.00
loops=1
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=7227
-> Hash Join (cost=50.51..78365.76 rows=231528
width=20) (actual time=0.936..260.236 rows=1000000.00 loops=3)
Output: t.tag_1, monthly_data.duration,
monthly_data.counter, monthly_data.amount
Hash Cond: (monthly_data.entity_id = t.entity_id)
Join Filter: ((monthly_data.month >= t.from_day)
AND (monthly_data.month <= t.to_day))
Rows Removed by Join Filter: 667154
Buffers: shared hit=22098
Worker 0: actual time=1.031..261.125
rows=1001480.00 loops=1
Buffers: shared hit=7377
Worker 1: actual time=0.947..259.326
rows=981104.00 loops=1
Buffers: shared hit=7227
-> Parallel Seq Scan on
public.monthly_data_202602 monthly_data (cost=0.00..40809.00 rows=1250000
width=20) (actual time=0.027..79.622 rows=1000000.00 loops=3)
Output: monthly_data.duration,
monthly_data.counter, monthly_data.amount, monthly_data.entity_id,
monthly_data.month
Filter: ((monthly_data.month >=
'2026-02-01'::date) AND (monthly_data.month <= '2026-02-28'::date))
Buffers: shared hit=22059
Worker 0: actual time=0.030..80.173
rows=1001480.00 loops=1
Buffers: shared hit=7364
Worker 1: actual time=0.031..82.531
rows=981104.00 loops=1
Buffers: shared hit=7214
-> Hash (cost=29.67..29.67 rows=1667 width=20)
(actual time=0.895..0.895 rows=1667.00 loops=3)
Output: t.tag_1, t.entity_id, t.from_day,
t.to_day
Buckets: 2048 Batches: 1 Memory Usage:
106kB
Buffers: shared hit=39
Worker 0: actual time=0.983..0.983
rows=1667.00 loops=1
Buffers: shared hit=13
Worker 1: actual time=0.898..0.898
rows=1667.00 loops=1
Buffers: shared hit=13
-> Seq Scan on public.entity_tags t
(cost=0.00..29.67 rows=1667 width=20) (actual time=0.081..0.542
rows=1667.00 loops=3)
Output: t.tag_1, t.entity_id,
t.from_day, t.to_day
Buffers: shared hit=39
Worker 0: actual time=0.118..0.540
rows=1667.00 loops=1
Buffers: shared hit=13
Worker 1: actual time=0.117..0.483
rows=1667.00 loops=1
Buffers: shared hit=13
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Potential partition pruning regression on PostgreSQL 18
2026-04-01 11:56 Potential partition pruning regression on PostgreSQL 18 Cándido Antonio Martínez Descalzo <candido@ninehq.com>
@ 2026-04-01 23:00 ` David Rowley <dgrowleyml@gmail.com>
2026-04-02 07:34 ` Re: Potential partition pruning regression on PostgreSQL 18 Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: David Rowley @ 2026-04-01 23:00 UTC (permalink / raw)
To: Cándido Antonio Martínez Descalzo <candido@ninehq.com>; Richard Guo <guofenglinux@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Thu, 2 Apr 2026 at 00:57, Cándido Antonio Martínez Descalzo
<candido@ninehq.com> wrote:
> We noticed that one of our queries unexpectedly stopped applying partition pruning on PG18, although it applies it on PG16 and PG17. The issue has been replicated on Linux and macOS.
>
> Failing to apply partition pruning significantly impacts the performance of these queries.
>
> We recreated the issue using a simplified schema and query. Details on the schema, query and resulting plans in PG17 and PG18 are provided below. Some changes in the query restore partition pruning in PG18, specifically:
>
> Replacing the view and date condition used with a sub-query or CTE with the same condition restores partition pruning (updated query and plan provided further below)
> Keeping the view and using a single "group by" instead of multiple grouping sets restores partition pruning (updated query and plan provided further below)
>
>
> Does anybody know if there is a documented behaviour change in PG18 that could explain this or if this is a known issue?
It relates to the "This release also fixes some GROUPING SETS queries
that used to return incorrect results." mentioned in [1]. Basically,
match_clause_to_partition_key() now sees a PlaceHolderVar rather than
the Var, which is the partition key column.
The question is, can we do the same thing in
match_clause_to_partition_key() as we did for index clauses in
ad66f705f. The PlaceHolderVar's phnullingrels are empty for this
query, so I expect we just need to give the same treatment to
partition key columns as was done for indexes columns in
fix_indexqual_operand().
Richard, any thoughts?
David
[1] https://www.postgresql.org/docs/release/18.0/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Potential partition pruning regression on PostgreSQL 18
2026-04-01 11:56 Potential partition pruning regression on PostgreSQL 18 Cándido Antonio Martínez Descalzo <candido@ninehq.com>
2026-04-01 23:00 ` Re: Potential partition pruning regression on PostgreSQL 18 David Rowley <dgrowleyml@gmail.com>
@ 2026-04-02 07:34 ` Richard Guo <guofenglinux@gmail.com>
2026-04-07 08:00 ` Re: Potential partition pruning regression on PostgreSQL 18 Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Guo @ 2026-04-02 07:34 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Cándido Antonio Martínez Descalzo <candido@ninehq.com>; Pg Hackers <pgsql-hackers@lists.postgresql.org>
On Thu, Apr 2, 2026 at 8:01 AM David Rowley <dgrowleyml@gmail.com> wrote:
> The question is, can we do the same thing in
> match_clause_to_partition_key() as we did for index clauses in
> ad66f705f. The PlaceHolderVar's phnullingrels are empty for this
> query, so I expect we just need to give the same treatment to
> partition key columns as was done for indexes columns in
> fix_indexqual_operand().
Agreed. The clauses in match_clause_to_partition_key() are always
relation-scan-level expressions, where a PHV with an empty
phnullingrels is effectively a no-op. Therefore, we can safely strip
such PHVs.
Attached is a draft patch for the fix.
Regarding backpatching, I'm inclined to only back-patch this down to
v18. This issue actually predates v18, for example, when the
partition key is a non-Var expression. A non-Var target item will be
wrapped in a PHV, causing us to fail the partition key match.
However, the changes in v18 seem to have made the issue common enough
to notice. This is very similar to the index matching case.
- Richard
Attachments:
[application/octet-stream] v1-0001-Strip-PlaceHolderVars-from-partition-pruning-oper.patch (20.9K, 2-v1-0001-Strip-PlaceHolderVars-from-partition-pruning-oper.patch)
download | inline diff:
From bd483b74311ad7992141103beb9585c0f70d4dba Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Thu, 2 Apr 2026 14:54:44 +0900
Subject: [PATCH v1] Strip PlaceHolderVars from partition pruning operands
When pulling up a subquery, its targetlist items may be wrapped in
PlaceHolderVars to enforce separate identity or as a result of outer
joins. This causes any upper-level WHERE clauses referencing these
outputs to contain PlaceHolderVars, which prevents partprune.c from
recognizing that they match partition key columns, defeating partition
pruning.
To fix, strip PlaceHolderVars from operands before comparing them to
partition keys. A PlaceHolderVar with empty phnullingrels appearing
in a relation-scan-level expression is effectively a no-op, so
stripping it is safe. This parallels the existing treatment in
indxpath.c for index matching.
In passing, rename strip_phvs_in_index_operand() to strip_noop_phvs()
and move it from indxpath.c to placeholder.c, since it is now a
general-purpose utility used by both index matching and partition
pruning code.
---
src/backend/optimizer/path/indxpath.c | 91 +-----------
src/backend/optimizer/plan/createplan.c | 2 +-
src/backend/optimizer/util/placeholder.c | 91 ++++++++++++
src/backend/partitioning/partprune.c | 28 +++-
src/include/optimizer/paths.h | 1 -
src/include/optimizer/placeholder.h | 1 +
src/test/regress/expected/partition_prune.out | 132 ++++++++++++++++++
src/test/regress/sql/partition_prune.sql | 71 ++++++++++
8 files changed, 320 insertions(+), 97 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 67d9dc35f44..430e06dcaaa 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -30,6 +30,7 @@
#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
+#include "optimizer/placeholder.h"
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
#include "utils/lsyscache.h"
@@ -195,8 +196,6 @@ static Expr *match_clause_to_ordering_op(IndexOptInfo *index,
static bool ec_member_matches_indexcol(PlannerInfo *root, RelOptInfo *rel,
EquivalenceClass *ec, EquivalenceMember *em,
void *arg);
-static bool contain_strippable_phv_walker(Node *node, void *context);
-static Node *strip_phvs_in_index_operand_mutator(Node *node, void *context);
/*
@@ -4363,7 +4362,7 @@ match_index_to_operand(Node *operand,
* a subtree) has been wrapped in PlaceHolderVars to enforce separate
* identity or as a result of outer joins.
*/
- operand = strip_phvs_in_index_operand(operand);
+ operand = strip_noop_phvs(operand);
/*
* Ignore any RelabelType node above the operand. This is needed to be
@@ -4427,92 +4426,6 @@ match_index_to_operand(Node *operand,
return false;
}
-/*
- * strip_phvs_in_index_operand
- * Strip PlaceHolderVar nodes from the given operand expression to
- * facilitate matching against an index's key.
- *
- * A PlaceHolderVar appearing in a relation-scan-level expression is
- * effectively a no-op. Nevertheless, to play it safe, we strip only
- * PlaceHolderVars that are not marked nullable.
- *
- * The removal is performed recursively because PlaceHolderVars can be nested
- * or interleaved with other node types. We must peel back all layers to
- * expose the base operand.
- *
- * As a performance optimization, we first use a lightweight walker to check
- * for the presence of strippable PlaceHolderVars. The expensive mutator is
- * invoked only if a candidate is found, avoiding unnecessary memory allocation
- * and tree copying in the common case where no PlaceHolderVars are present.
- */
-Node *
-strip_phvs_in_index_operand(Node *operand)
-{
- /* Don't mutate/copy if no target PHVs exist */
- if (!contain_strippable_phv_walker(operand, NULL))
- return operand;
-
- return strip_phvs_in_index_operand_mutator(operand, NULL);
-}
-
-/*
- * contain_strippable_phv_walker
- * Detect if there are any PlaceHolderVars in the tree that are candidates
- * for stripping.
- *
- * We identify a PlaceHolderVar as strippable only if its phnullingrels is
- * empty.
- */
-static bool
-contain_strippable_phv_walker(Node *node, void *context)
-{
- if (node == NULL)
- return false;
-
- if (IsA(node, PlaceHolderVar))
- {
- PlaceHolderVar *phv = (PlaceHolderVar *) node;
-
- if (bms_is_empty(phv->phnullingrels))
- return true;
- }
-
- return expression_tree_walker(node, contain_strippable_phv_walker,
- context);
-}
-
-/*
- * strip_phvs_in_index_operand_mutator
- * Recursively remove PlaceHolderVars in the tree that match the criteria.
- *
- * We strip a PlaceHolderVar only if its phnullingrels is empty, replacing it
- * with its contained expression.
- */
-static Node *
-strip_phvs_in_index_operand_mutator(Node *node, void *context)
-{
- if (node == NULL)
- return NULL;
-
- if (IsA(node, PlaceHolderVar))
- {
- PlaceHolderVar *phv = (PlaceHolderVar *) node;
-
- /* If matches the criteria, strip it */
- if (bms_is_empty(phv->phnullingrels))
- {
- /* Recurse on its contained expression */
- return strip_phvs_in_index_operand_mutator((Node *) phv->phexpr,
- context);
- }
-
- /* Otherwise, keep this PHV but check its contained expression */
- }
-
- return expression_tree_mutator(node, strip_phvs_in_index_operand_mutator,
- context);
-}
-
/*
* is_pseudo_constant_for_index()
* Test whether the given expression can be used as an indexscan
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index c7bc41c30d7..de6a183da79 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -5117,7 +5117,7 @@ fix_indexqual_operand(Node *node, IndexOptInfo *index, int indexcol)
/*
* Remove any PlaceHolderVar wrapping of the indexkey
*/
- node = strip_phvs_in_index_operand(node);
+ node = strip_noop_phvs(node);
/*
* Remove any binary-compatible relabeling of the indexkey
diff --git a/src/backend/optimizer/util/placeholder.c b/src/backend/optimizer/util/placeholder.c
index e1706363c88..f05ac7e05c2 100644
--- a/src/backend/optimizer/util/placeholder.c
+++ b/src/backend/optimizer/util/placeholder.c
@@ -35,6 +35,8 @@ static void find_placeholders_recurse(PlannerInfo *root, Node *jtnode);
static void find_placeholders_in_expr(PlannerInfo *root, Node *expr);
static bool contain_placeholder_references_walker(Node *node,
contain_placeholder_references_context *context);
+static bool contain_noop_phv_walker(Node *node, void *context);
+static Node *strip_noop_phvs_mutator(Node *node, void *context);
/*
@@ -585,3 +587,92 @@ get_placeholder_nulling_relids(PlannerInfo *root, PlaceHolderInfo *phinfo)
result = bms_del_members(result, phinfo->ph_eval_at);
return result;
}
+
+/*
+ * strip_noop_phvs
+ * Strip PlaceHolderVar nodes from the given expression tree.
+ *
+ * A PlaceHolderVar that is not marked as nullable (i.e., its phnullingrels
+ * is empty) is effectively a no-op when it appears in a relation-scan-level
+ * expression. This function strips such PlaceHolderVars, which is useful
+ * for matching expressions to index keys or partition keys in cases where
+ * the expression has been wrapped in PlaceHolderVars during subquery pullup.
+ *
+ * IMPORTANT: the caller must ensure that the expression is a scan-level
+ * expression, so that non-nullable PlaceHolderVars in it are indeed no-ops.
+ *
+ * The removal is performed recursively because PlaceHolderVars can be nested
+ * or interleaved with other node types. We must peel back all layers to
+ * expose the base expression.
+ *
+ * As a performance optimization, we first use a lightweight walker to check
+ * for the presence of strippable PlaceHolderVars. The expensive mutator is
+ * invoked only if a candidate is found, avoiding unnecessary memory allocation
+ * and tree copying in the common case where no PlaceHolderVars are present.
+ */
+Node *
+strip_noop_phvs(Node *node)
+{
+ /* Don't mutate/copy if no target PHVs exist */
+ if (!contain_noop_phv_walker(node, NULL))
+ return node;
+
+ return strip_noop_phvs_mutator(node, NULL);
+}
+
+/*
+ * contain_noop_phv_walker
+ * Detect if there are any PlaceHolderVars in the tree that are candidates
+ * for stripping.
+ *
+ * We identify a PlaceHolderVar as strippable only if its phnullingrels is
+ * empty.
+ */
+static bool
+contain_noop_phv_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, PlaceHolderVar))
+ {
+ PlaceHolderVar *phv = (PlaceHolderVar *) node;
+
+ if (bms_is_empty(phv->phnullingrels))
+ return true;
+ }
+
+ return expression_tree_walker(node, contain_noop_phv_walker,
+ context);
+}
+
+/*
+ * strip_noop_phvs_mutator
+ * Recursively remove PlaceHolderVars that are not marked nullable.
+ *
+ * We strip a PlaceHolderVar only if its phnullingrels is empty, replacing it
+ * with its contained expression.
+ */
+static Node *
+strip_noop_phvs_mutator(Node *node, void *context)
+{
+ if (node == NULL)
+ return NULL;
+
+ if (IsA(node, PlaceHolderVar))
+ {
+ PlaceHolderVar *phv = (PlaceHolderVar *) node;
+
+ if (bms_is_empty(phv->phnullingrels))
+ {
+ /* Recurse on its contained expression */
+ return strip_noop_phvs_mutator((Node *) phv->phexpr,
+ context);
+ }
+
+ /* Otherwise, keep this PHV but check its contained expression */
+ }
+
+ return expression_tree_mutator(node, strip_noop_phvs_mutator,
+ context);
+}
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c
index 2901cd348a9..e7c318bbcac 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -49,6 +49,7 @@
#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
+#include "optimizer/placeholder.h"
#include "parser/parsetree.h"
#include "partitioning/partbounds.h"
#include "partitioning/partprune.h"
@@ -1813,6 +1814,15 @@ gen_prune_steps_from_opexps(GeneratePruningStepsContext *context,
* and couldn't possibly match any other one either, due to its form or
* properties (such as containing a volatile function).
* Output arguments: none set.
+ *
+ * Note that when pulling up a subquery, the clause operands may get wrapped
+ * in PlaceHolderVars to enforce separate identity or as a result of outer
+ * joins. We must strip such no-op PlaceHolderVars before comparing operands
+ * to the partition key, otherwise the equal() checks will fail to recognize
+ * valid matches. This is safe because the clauses here are always
+ * relation-scan-level expressions, where a PlaceHolderVar with empty
+ * phnullingrels is effectively a no-op. Stripping may also bring separate
+ * RelabelType nodes into adjacency, so we must loop when peeling those.
*/
static PartClauseMatchStatus
match_clause_to_partition_key(GeneratePruningStepsContext *context,
@@ -1928,10 +1938,12 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
PartClauseInfo *partclause;
leftop = (Expr *) get_leftop(clause);
- if (IsA(leftop, RelabelType))
+ leftop = (Expr *) strip_noop_phvs((Node *) leftop);
+ while (IsA(leftop, RelabelType))
leftop = ((RelabelType *) leftop)->arg;
rightop = (Expr *) get_rightop(clause);
- if (IsA(rightop, RelabelType))
+ rightop = (Expr *) strip_noop_phvs((Node *) rightop);
+ while (IsA(rightop, RelabelType))
rightop = ((RelabelType *) rightop)->arg;
opno = opclause->opno;
@@ -2179,7 +2191,8 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
*elem_clauses;
ListCell *lc1;
- if (IsA(leftop, RelabelType))
+ leftop = (Expr *) strip_noop_phvs((Node *) leftop);
+ while (IsA(leftop, RelabelType))
leftop = ((RelabelType *) leftop)->arg;
/* check if the LHS matches this partition key */
@@ -2405,7 +2418,8 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
NullTest *nulltest = (NullTest *) clause;
Expr *arg = nulltest->arg;
- if (IsA(arg, RelabelType))
+ arg = (Expr *) strip_noop_phvs((Node *) arg);
+ while (IsA(arg, RelabelType))
arg = ((RelabelType *) arg)->arg;
/* Does arg match with this partition key column? */
@@ -3717,7 +3731,8 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, const Expr *partk
BooleanTest *btest = (BooleanTest *) clause;
leftop = btest->arg;
- if (IsA(leftop, RelabelType))
+ leftop = (Expr *) strip_noop_phvs((Node *) leftop);
+ while (IsA(leftop, RelabelType))
leftop = ((RelabelType *) leftop)->arg;
if (equal(leftop, partkey))
@@ -3754,7 +3769,8 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, const Expr *partk
leftop = is_not_clause ? get_notclausearg(clause) : clause;
- if (IsA(leftop, RelabelType))
+ leftop = (Expr *) strip_noop_phvs((Node *) leftop);
+ while (IsA(leftop, RelabelType))
leftop = ((RelabelType *) leftop)->arg;
/* Compare to the partition key, and make up a clause ... */
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 8751ad7381c..17f2099ec3b 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -88,7 +88,6 @@ extern bool indexcol_is_bool_constant_for_query(PlannerInfo *root,
int indexcol);
extern bool match_index_to_operand(Node *operand, int indexcol,
IndexOptInfo *index);
-extern Node *strip_phvs_in_index_operand(Node *operand);
extern void check_index_predicates(PlannerInfo *root, RelOptInfo *rel);
/*
diff --git a/src/include/optimizer/placeholder.h b/src/include/optimizer/placeholder.h
index 28e4da645fb..60798281090 100644
--- a/src/include/optimizer/placeholder.h
+++ b/src/include/optimizer/placeholder.h
@@ -32,5 +32,6 @@ extern bool contain_placeholder_references_to(PlannerInfo *root, Node *clause,
int relid);
extern Relids get_placeholder_nulling_relids(PlannerInfo *root,
PlaceHolderInfo *phinfo);
+extern Node *strip_noop_phvs(Node *node);
#endif /* PLACEHOLDER_H */
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index deacdd75807..849049f9c51 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -4824,3 +4824,135 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
drop view part_abc_view;
drop table part_abc;
+--
+-- Check that operands wrapped in PlaceHolderVars are matched to partition
+-- keys, allowing partition pruning to occur. PlaceHolderVars can be
+-- introduced when a subquery's output is used with grouping sets.
+--
+create table phv_part (a int, b text) partition by list (a);
+create table phv_part_1 partition of phv_part for values in (1);
+create table phv_part_2 partition of phv_part for values in (2);
+create table phv_part_null partition of phv_part for values in (null);
+insert into phv_part values (1, 'one'), (2, 'two'), (null, 'null');
+-- OpExpr: PHV-wrapped operand matched via equal()
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a = 1
+ group by grouping sets (a, b);
+ QUERY PLAN
+---------------------------------------
+ MixedAggregate
+ Hash Key: phv_part.b
+ Group Key: phv_part.a
+ -> Seq Scan on phv_part_1 phv_part
+ Filter: (a = 1)
+(5 rows)
+
+select * from (select a, b from phv_part) t
+ where a = 1
+ group by grouping sets (a, b);
+ a | b
+---+-----
+ 1 |
+ | one
+(2 rows)
+
+-- OpExpr with RelabelType: PHV wrapped around a casted column
+explain (costs off)
+select * from (select a::oid as x, b from phv_part) t
+ where x::int = 1
+ group by grouping sets (x, b);
+ QUERY PLAN
+-------------------------------------------
+ HashAggregate
+ Hash Key: (phv_part.a)::oid
+ Hash Key: phv_part.b
+ -> Seq Scan on phv_part_1 phv_part
+ Filter: (((a)::oid)::integer = 1)
+(5 rows)
+
+select * from (select a::oid as x, b from phv_part) t
+ where x::int = 1
+ group by grouping sets (x, b);
+ x | b
+---+-----
+ 1 |
+ | one
+(2 rows)
+
+-- ScalarArrayOpExpr: IN clause with PHV-wrapped operand
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a in (1, null)
+ group by grouping sets (a, b);
+ QUERY PLAN
+---------------------------------------------------
+ HashAggregate
+ Hash Key: phv_part.a
+ Hash Key: phv_part.b
+ -> Seq Scan on phv_part_1 phv_part
+ Filter: (a = ANY ('{1,NULL}'::integer[]))
+(5 rows)
+
+select * from (select a, b from phv_part) t
+ where a in (1, null)
+ group by grouping sets (a, b);
+ a | b
+---+-----
+ 1 |
+ | one
+(2 rows)
+
+-- NullTest: IS NULL with PHV-wrapped operand
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a is null
+ group by grouping sets (a, b);
+ QUERY PLAN
+------------------------------------------
+ HashAggregate
+ Hash Key: phv_part.a
+ Hash Key: phv_part.b
+ -> Seq Scan on phv_part_null phv_part
+ Filter: (a IS NULL)
+(5 rows)
+
+select * from (select a, b from phv_part) t
+ where a is null
+ group by grouping sets (a, b);
+ a | b
+---+------
+ |
+ | null
+(2 rows)
+
+drop table phv_part;
+-- BooleanTest: IS TRUE with PHV-wrapped boolean partition key
+create table phv_boolpart (a bool, b text) partition by list (a);
+create table phv_boolpart_t partition of phv_boolpart for values in (true);
+create table phv_boolpart_f partition of phv_boolpart for values in (false);
+create table phv_boolpart_null partition of phv_boolpart default;
+insert into phv_boolpart values (true, 'yes'), (false, 'no'), (null, 'unknown');
+explain (costs off)
+select * from (select a, b from phv_boolpart) t
+ where a is true
+ group by grouping sets (a, b);
+ QUERY PLAN
+-----------------------------------------------
+ HashAggregate
+ Hash Key: phv_boolpart.a
+ Hash Key: phv_boolpart.b
+ -> Seq Scan on phv_boolpart_t phv_boolpart
+ Filter: (a IS TRUE)
+(5 rows)
+
+select * from (select a, b from phv_boolpart) t
+ where a is true
+ group by grouping sets (a, b);
+ a | b
+---+-----
+ t |
+ | yes
+(2 rows)
+
+drop table phv_boolpart;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index d93c0c03bab..359a9208056 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -1447,3 +1447,74 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
drop view part_abc_view;
drop table part_abc;
+
+--
+-- Check that operands wrapped in PlaceHolderVars are matched to partition
+-- keys, allowing partition pruning to occur. PlaceHolderVars can be
+-- introduced when a subquery's output is used with grouping sets.
+--
+create table phv_part (a int, b text) partition by list (a);
+create table phv_part_1 partition of phv_part for values in (1);
+create table phv_part_2 partition of phv_part for values in (2);
+create table phv_part_null partition of phv_part for values in (null);
+insert into phv_part values (1, 'one'), (2, 'two'), (null, 'null');
+
+-- OpExpr: PHV-wrapped operand matched via equal()
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a = 1
+ group by grouping sets (a, b);
+
+select * from (select a, b from phv_part) t
+ where a = 1
+ group by grouping sets (a, b);
+
+-- OpExpr with RelabelType: PHV wrapped around a casted column
+explain (costs off)
+select * from (select a::oid as x, b from phv_part) t
+ where x::int = 1
+ group by grouping sets (x, b);
+
+select * from (select a::oid as x, b from phv_part) t
+ where x::int = 1
+ group by grouping sets (x, b);
+
+-- ScalarArrayOpExpr: IN clause with PHV-wrapped operand
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a in (1, null)
+ group by grouping sets (a, b);
+
+select * from (select a, b from phv_part) t
+ where a in (1, null)
+ group by grouping sets (a, b);
+
+-- NullTest: IS NULL with PHV-wrapped operand
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a is null
+ group by grouping sets (a, b);
+
+select * from (select a, b from phv_part) t
+ where a is null
+ group by grouping sets (a, b);
+
+drop table phv_part;
+
+-- BooleanTest: IS TRUE with PHV-wrapped boolean partition key
+create table phv_boolpart (a bool, b text) partition by list (a);
+create table phv_boolpart_t partition of phv_boolpart for values in (true);
+create table phv_boolpart_f partition of phv_boolpart for values in (false);
+create table phv_boolpart_null partition of phv_boolpart default;
+insert into phv_boolpart values (true, 'yes'), (false, 'no'), (null, 'unknown');
+
+explain (costs off)
+select * from (select a, b from phv_boolpart) t
+ where a is true
+ group by grouping sets (a, b);
+
+select * from (select a, b from phv_boolpart) t
+ where a is true
+ group by grouping sets (a, b);
+
+drop table phv_boolpart;
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Potential partition pruning regression on PostgreSQL 18
2026-04-01 11:56 Potential partition pruning regression on PostgreSQL 18 Cándido Antonio Martínez Descalzo <candido@ninehq.com>
2026-04-01 23:00 ` Re: Potential partition pruning regression on PostgreSQL 18 David Rowley <dgrowleyml@gmail.com>
2026-04-02 07:34 ` Re: Potential partition pruning regression on PostgreSQL 18 Richard Guo <guofenglinux@gmail.com>
@ 2026-04-07 08:00 ` Richard Guo <guofenglinux@gmail.com>
2026-04-09 08:05 ` Re: Potential partition pruning regression on PostgreSQL 18 Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Guo @ 2026-04-07 08:00 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Cándido Antonio Martínez Descalzo <candido@ninehq.com>; Pg Hackers <pgsql-hackers@lists.postgresql.org>
On Thu, Apr 2, 2026 at 4:34 PM Richard Guo <guofenglinux@gmail.com> wrote:
> Attached is a draft patch for the fix.
>
> Regarding backpatching, I'm inclined to only back-patch this down to
> v18. This issue actually predates v18, for example, when the
> partition key is a non-Var expression. A non-Var target item will be
> wrapped in a PHV, causing us to fail the partition key match.
> However, the changes in v18 seem to have made the issue common enough
> to notice. This is very similar to the index matching case.
Here are the more formal patches for HEAD and for v18.
In the HEAD patch, I renamed strip_phvs_in_index_operand() to
strip_noop_phvs() and moved it from indxpath.c to placeholder.c, since
it is now a general-purpose utility used by both index matching and
partition pruning code.
However, since strip_phvs_in_index_operand() is an extern function
declared in a public header, I'm worried that third-party extensions
may have started calling it after it was introduced in ad66f705f. So
for the v18 back-patch, I retained strip_phvs_in_index_operand() in
indxpath.c as a thin wrapper around the new strip_noop_phvs(), to
avoid breaking such extensions in a minor release.
Does this seem like reasonable caution, or is it overkill given how
recently the function was introduced?
- Richard
Attachments:
[application/octet-stream] v2-HEAD-0001-Strip-PlaceHolderVars-from-partition-pruning-oper.patch (21.6K, 2-v2-HEAD-0001-Strip-PlaceHolderVars-from-partition-pruning-oper.patch)
download | inline diff:
From 2d28d08b0b0e0827497e2857c67abac5b1613a22 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Thu, 2 Apr 2026 14:54:44 +0900
Subject: [PATCH v2] Strip PlaceHolderVars from partition pruning operands
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
When pulling up a subquery, its targetlist items may be wrapped in
PlaceHolderVars to enforce separate identity or as a result of outer
joins. This causes any upper-level WHERE clauses referencing these
outputs to contain PlaceHolderVars, which prevents partprune.c from
recognizing that they match partition key columns, defeating partition
pruning.
To fix, strip PlaceHolderVars from operands before comparing them to
partition keys. A PlaceHolderVar with empty phnullingrels appearing
in a relation-scan-level expression is effectively a no-op, so
stripping it is safe. This parallels the existing treatment in
indxpath.c for index matching.
In passing, rename strip_phvs_in_index_operand() to strip_noop_phvs()
and move it from indxpath.c to placeholder.c, since it is now a
general-purpose utility used by both index matching and partition
pruning code.
Back-patch to v18. Although this issue exists before that, changes in
that version made it common enough to notice. Given the lack of field
reports for older versions, I am not back-patching further. In the
v18 back-patch, strip_phvs_in_index_operand() is retained as a thin
wrapper around the new strip_noop_phvs() to avoid breaking third-party
extensions that may reference it.
Reported-by: Cándido Antonio Martínez Descalzo <candido@ninehq.com>
Diagnosed-by: David Rowley <dgrowleyml@gmail.com>
Author: Richard Guo <guofenglinux@gmail.com>
Discussion: https://postgr.es/m/CAH5YaUwVUWETTyVECTnhs7C=CVwi+uMSQH=cOkwAUqMdvXdwWA@mail.gmail.com
Backpatch-through: 18
---
src/backend/optimizer/path/indxpath.c | 91 +-----------
src/backend/optimizer/plan/createplan.c | 2 +-
src/backend/optimizer/util/placeholder.c | 91 ++++++++++++
src/backend/partitioning/partprune.c | 28 +++-
src/include/optimizer/paths.h | 1 -
src/include/optimizer/placeholder.h | 1 +
src/test/regress/expected/partition_prune.out | 132 ++++++++++++++++++
src/test/regress/sql/partition_prune.sql | 71 ++++++++++
8 files changed, 320 insertions(+), 97 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 67d9dc35f44..430e06dcaaa 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -30,6 +30,7 @@
#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
+#include "optimizer/placeholder.h"
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
#include "utils/lsyscache.h"
@@ -195,8 +196,6 @@ static Expr *match_clause_to_ordering_op(IndexOptInfo *index,
static bool ec_member_matches_indexcol(PlannerInfo *root, RelOptInfo *rel,
EquivalenceClass *ec, EquivalenceMember *em,
void *arg);
-static bool contain_strippable_phv_walker(Node *node, void *context);
-static Node *strip_phvs_in_index_operand_mutator(Node *node, void *context);
/*
@@ -4363,7 +4362,7 @@ match_index_to_operand(Node *operand,
* a subtree) has been wrapped in PlaceHolderVars to enforce separate
* identity or as a result of outer joins.
*/
- operand = strip_phvs_in_index_operand(operand);
+ operand = strip_noop_phvs(operand);
/*
* Ignore any RelabelType node above the operand. This is needed to be
@@ -4427,92 +4426,6 @@ match_index_to_operand(Node *operand,
return false;
}
-/*
- * strip_phvs_in_index_operand
- * Strip PlaceHolderVar nodes from the given operand expression to
- * facilitate matching against an index's key.
- *
- * A PlaceHolderVar appearing in a relation-scan-level expression is
- * effectively a no-op. Nevertheless, to play it safe, we strip only
- * PlaceHolderVars that are not marked nullable.
- *
- * The removal is performed recursively because PlaceHolderVars can be nested
- * or interleaved with other node types. We must peel back all layers to
- * expose the base operand.
- *
- * As a performance optimization, we first use a lightweight walker to check
- * for the presence of strippable PlaceHolderVars. The expensive mutator is
- * invoked only if a candidate is found, avoiding unnecessary memory allocation
- * and tree copying in the common case where no PlaceHolderVars are present.
- */
-Node *
-strip_phvs_in_index_operand(Node *operand)
-{
- /* Don't mutate/copy if no target PHVs exist */
- if (!contain_strippable_phv_walker(operand, NULL))
- return operand;
-
- return strip_phvs_in_index_operand_mutator(operand, NULL);
-}
-
-/*
- * contain_strippable_phv_walker
- * Detect if there are any PlaceHolderVars in the tree that are candidates
- * for stripping.
- *
- * We identify a PlaceHolderVar as strippable only if its phnullingrels is
- * empty.
- */
-static bool
-contain_strippable_phv_walker(Node *node, void *context)
-{
- if (node == NULL)
- return false;
-
- if (IsA(node, PlaceHolderVar))
- {
- PlaceHolderVar *phv = (PlaceHolderVar *) node;
-
- if (bms_is_empty(phv->phnullingrels))
- return true;
- }
-
- return expression_tree_walker(node, contain_strippable_phv_walker,
- context);
-}
-
-/*
- * strip_phvs_in_index_operand_mutator
- * Recursively remove PlaceHolderVars in the tree that match the criteria.
- *
- * We strip a PlaceHolderVar only if its phnullingrels is empty, replacing it
- * with its contained expression.
- */
-static Node *
-strip_phvs_in_index_operand_mutator(Node *node, void *context)
-{
- if (node == NULL)
- return NULL;
-
- if (IsA(node, PlaceHolderVar))
- {
- PlaceHolderVar *phv = (PlaceHolderVar *) node;
-
- /* If matches the criteria, strip it */
- if (bms_is_empty(phv->phnullingrels))
- {
- /* Recurse on its contained expression */
- return strip_phvs_in_index_operand_mutator((Node *) phv->phexpr,
- context);
- }
-
- /* Otherwise, keep this PHV but check its contained expression */
- }
-
- return expression_tree_mutator(node, strip_phvs_in_index_operand_mutator,
- context);
-}
-
/*
* is_pseudo_constant_for_index()
* Test whether the given expression can be used as an indexscan
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index c7bc41c30d7..de6a183da79 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -5117,7 +5117,7 @@ fix_indexqual_operand(Node *node, IndexOptInfo *index, int indexcol)
/*
* Remove any PlaceHolderVar wrapping of the indexkey
*/
- node = strip_phvs_in_index_operand(node);
+ node = strip_noop_phvs(node);
/*
* Remove any binary-compatible relabeling of the indexkey
diff --git a/src/backend/optimizer/util/placeholder.c b/src/backend/optimizer/util/placeholder.c
index e1706363c88..dd9b11885af 100644
--- a/src/backend/optimizer/util/placeholder.c
+++ b/src/backend/optimizer/util/placeholder.c
@@ -35,6 +35,8 @@ static void find_placeholders_recurse(PlannerInfo *root, Node *jtnode);
static void find_placeholders_in_expr(PlannerInfo *root, Node *expr);
static bool contain_placeholder_references_walker(Node *node,
contain_placeholder_references_context *context);
+static bool contain_noop_phv_walker(Node *node, void *context);
+static Node *strip_noop_phvs_mutator(Node *node, void *context);
/*
@@ -585,3 +587,92 @@ get_placeholder_nulling_relids(PlannerInfo *root, PlaceHolderInfo *phinfo)
result = bms_del_members(result, phinfo->ph_eval_at);
return result;
}
+
+/*
+ * strip_noop_phvs
+ * Strip no-op PlaceHolderVar nodes from the given expression tree.
+ *
+ * A PlaceHolderVar that is not marked as nullable (i.e., its phnullingrels
+ * is empty) is effectively a no-op when it appears in a relation-scan-level
+ * expression. This function strips such PlaceHolderVars, which is useful
+ * for matching expressions to index keys or partition keys in cases where
+ * the expression has been wrapped in PlaceHolderVars during subquery pullup.
+ *
+ * IMPORTANT: the caller must ensure that the expression is a scan-level
+ * expression, so that non-nullable PlaceHolderVars in it are indeed no-ops.
+ *
+ * The removal is performed recursively because PlaceHolderVars can be nested
+ * or interleaved with other node types. We must peel back all layers to
+ * expose the base expression.
+ *
+ * As a performance optimization, we first use a lightweight walker to check
+ * for the presence of strippable PlaceHolderVars. The expensive mutator is
+ * invoked only if a candidate is found, avoiding unnecessary memory allocation
+ * and tree copying in the common case where no PlaceHolderVars are present.
+ */
+Node *
+strip_noop_phvs(Node *node)
+{
+ /* Don't mutate/copy if no target PHVs exist */
+ if (!contain_noop_phv_walker(node, NULL))
+ return node;
+
+ return strip_noop_phvs_mutator(node, NULL);
+}
+
+/*
+ * contain_noop_phv_walker
+ * Detect if there are any PlaceHolderVars in the tree that are candidates
+ * for stripping.
+ *
+ * We identify a PlaceHolderVar as strippable only if its phnullingrels is
+ * empty.
+ */
+static bool
+contain_noop_phv_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, PlaceHolderVar))
+ {
+ PlaceHolderVar *phv = (PlaceHolderVar *) node;
+
+ if (bms_is_empty(phv->phnullingrels))
+ return true;
+ }
+
+ return expression_tree_walker(node, contain_noop_phv_walker,
+ context);
+}
+
+/*
+ * strip_noop_phvs_mutator
+ * Recursively remove PlaceHolderVars that are not marked nullable.
+ *
+ * We strip a PlaceHolderVar only if its phnullingrels is empty, replacing it
+ * with its contained expression.
+ */
+static Node *
+strip_noop_phvs_mutator(Node *node, void *context)
+{
+ if (node == NULL)
+ return NULL;
+
+ if (IsA(node, PlaceHolderVar))
+ {
+ PlaceHolderVar *phv = (PlaceHolderVar *) node;
+
+ if (bms_is_empty(phv->phnullingrels))
+ {
+ /* Recurse on its contained expression */
+ return strip_noop_phvs_mutator((Node *) phv->phexpr,
+ context);
+ }
+
+ /* Otherwise, keep this PHV but check its contained expression */
+ }
+
+ return expression_tree_mutator(node, strip_noop_phvs_mutator,
+ context);
+}
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c
index 2901cd348a9..e7c318bbcac 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -49,6 +49,7 @@
#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
+#include "optimizer/placeholder.h"
#include "parser/parsetree.h"
#include "partitioning/partbounds.h"
#include "partitioning/partprune.h"
@@ -1813,6 +1814,15 @@ gen_prune_steps_from_opexps(GeneratePruningStepsContext *context,
* and couldn't possibly match any other one either, due to its form or
* properties (such as containing a volatile function).
* Output arguments: none set.
+ *
+ * Note that when pulling up a subquery, the clause operands may get wrapped
+ * in PlaceHolderVars to enforce separate identity or as a result of outer
+ * joins. We must strip such no-op PlaceHolderVars before comparing operands
+ * to the partition key, otherwise the equal() checks will fail to recognize
+ * valid matches. This is safe because the clauses here are always
+ * relation-scan-level expressions, where a PlaceHolderVar with empty
+ * phnullingrels is effectively a no-op. Stripping may also bring separate
+ * RelabelType nodes into adjacency, so we must loop when peeling those.
*/
static PartClauseMatchStatus
match_clause_to_partition_key(GeneratePruningStepsContext *context,
@@ -1928,10 +1938,12 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
PartClauseInfo *partclause;
leftop = (Expr *) get_leftop(clause);
- if (IsA(leftop, RelabelType))
+ leftop = (Expr *) strip_noop_phvs((Node *) leftop);
+ while (IsA(leftop, RelabelType))
leftop = ((RelabelType *) leftop)->arg;
rightop = (Expr *) get_rightop(clause);
- if (IsA(rightop, RelabelType))
+ rightop = (Expr *) strip_noop_phvs((Node *) rightop);
+ while (IsA(rightop, RelabelType))
rightop = ((RelabelType *) rightop)->arg;
opno = opclause->opno;
@@ -2179,7 +2191,8 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
*elem_clauses;
ListCell *lc1;
- if (IsA(leftop, RelabelType))
+ leftop = (Expr *) strip_noop_phvs((Node *) leftop);
+ while (IsA(leftop, RelabelType))
leftop = ((RelabelType *) leftop)->arg;
/* check if the LHS matches this partition key */
@@ -2405,7 +2418,8 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
NullTest *nulltest = (NullTest *) clause;
Expr *arg = nulltest->arg;
- if (IsA(arg, RelabelType))
+ arg = (Expr *) strip_noop_phvs((Node *) arg);
+ while (IsA(arg, RelabelType))
arg = ((RelabelType *) arg)->arg;
/* Does arg match with this partition key column? */
@@ -3717,7 +3731,8 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, const Expr *partk
BooleanTest *btest = (BooleanTest *) clause;
leftop = btest->arg;
- if (IsA(leftop, RelabelType))
+ leftop = (Expr *) strip_noop_phvs((Node *) leftop);
+ while (IsA(leftop, RelabelType))
leftop = ((RelabelType *) leftop)->arg;
if (equal(leftop, partkey))
@@ -3754,7 +3769,8 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, const Expr *partk
leftop = is_not_clause ? get_notclausearg(clause) : clause;
- if (IsA(leftop, RelabelType))
+ leftop = (Expr *) strip_noop_phvs((Node *) leftop);
+ while (IsA(leftop, RelabelType))
leftop = ((RelabelType *) leftop)->arg;
/* Compare to the partition key, and make up a clause ... */
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 8751ad7381c..17f2099ec3b 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -88,7 +88,6 @@ extern bool indexcol_is_bool_constant_for_query(PlannerInfo *root,
int indexcol);
extern bool match_index_to_operand(Node *operand, int indexcol,
IndexOptInfo *index);
-extern Node *strip_phvs_in_index_operand(Node *operand);
extern void check_index_predicates(PlannerInfo *root, RelOptInfo *rel);
/*
diff --git a/src/include/optimizer/placeholder.h b/src/include/optimizer/placeholder.h
index 28e4da645fb..60798281090 100644
--- a/src/include/optimizer/placeholder.h
+++ b/src/include/optimizer/placeholder.h
@@ -32,5 +32,6 @@ extern bool contain_placeholder_references_to(PlannerInfo *root, Node *clause,
int relid);
extern Relids get_placeholder_nulling_relids(PlannerInfo *root,
PlaceHolderInfo *phinfo);
+extern Node *strip_noop_phvs(Node *node);
#endif /* PLACEHOLDER_H */
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index deacdd75807..849049f9c51 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -4824,3 +4824,135 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
drop view part_abc_view;
drop table part_abc;
+--
+-- Check that operands wrapped in PlaceHolderVars are matched to partition
+-- keys, allowing partition pruning to occur. PlaceHolderVars can be
+-- introduced when a subquery's output is used with grouping sets.
+--
+create table phv_part (a int, b text) partition by list (a);
+create table phv_part_1 partition of phv_part for values in (1);
+create table phv_part_2 partition of phv_part for values in (2);
+create table phv_part_null partition of phv_part for values in (null);
+insert into phv_part values (1, 'one'), (2, 'two'), (null, 'null');
+-- OpExpr: PHV-wrapped operand matched via equal()
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a = 1
+ group by grouping sets (a, b);
+ QUERY PLAN
+---------------------------------------
+ MixedAggregate
+ Hash Key: phv_part.b
+ Group Key: phv_part.a
+ -> Seq Scan on phv_part_1 phv_part
+ Filter: (a = 1)
+(5 rows)
+
+select * from (select a, b from phv_part) t
+ where a = 1
+ group by grouping sets (a, b);
+ a | b
+---+-----
+ 1 |
+ | one
+(2 rows)
+
+-- OpExpr with RelabelType: PHV wrapped around a casted column
+explain (costs off)
+select * from (select a::oid as x, b from phv_part) t
+ where x::int = 1
+ group by grouping sets (x, b);
+ QUERY PLAN
+-------------------------------------------
+ HashAggregate
+ Hash Key: (phv_part.a)::oid
+ Hash Key: phv_part.b
+ -> Seq Scan on phv_part_1 phv_part
+ Filter: (((a)::oid)::integer = 1)
+(5 rows)
+
+select * from (select a::oid as x, b from phv_part) t
+ where x::int = 1
+ group by grouping sets (x, b);
+ x | b
+---+-----
+ 1 |
+ | one
+(2 rows)
+
+-- ScalarArrayOpExpr: IN clause with PHV-wrapped operand
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a in (1, null)
+ group by grouping sets (a, b);
+ QUERY PLAN
+---------------------------------------------------
+ HashAggregate
+ Hash Key: phv_part.a
+ Hash Key: phv_part.b
+ -> Seq Scan on phv_part_1 phv_part
+ Filter: (a = ANY ('{1,NULL}'::integer[]))
+(5 rows)
+
+select * from (select a, b from phv_part) t
+ where a in (1, null)
+ group by grouping sets (a, b);
+ a | b
+---+-----
+ 1 |
+ | one
+(2 rows)
+
+-- NullTest: IS NULL with PHV-wrapped operand
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a is null
+ group by grouping sets (a, b);
+ QUERY PLAN
+------------------------------------------
+ HashAggregate
+ Hash Key: phv_part.a
+ Hash Key: phv_part.b
+ -> Seq Scan on phv_part_null phv_part
+ Filter: (a IS NULL)
+(5 rows)
+
+select * from (select a, b from phv_part) t
+ where a is null
+ group by grouping sets (a, b);
+ a | b
+---+------
+ |
+ | null
+(2 rows)
+
+drop table phv_part;
+-- BooleanTest: IS TRUE with PHV-wrapped boolean partition key
+create table phv_boolpart (a bool, b text) partition by list (a);
+create table phv_boolpart_t partition of phv_boolpart for values in (true);
+create table phv_boolpart_f partition of phv_boolpart for values in (false);
+create table phv_boolpart_null partition of phv_boolpart default;
+insert into phv_boolpart values (true, 'yes'), (false, 'no'), (null, 'unknown');
+explain (costs off)
+select * from (select a, b from phv_boolpart) t
+ where a is true
+ group by grouping sets (a, b);
+ QUERY PLAN
+-----------------------------------------------
+ HashAggregate
+ Hash Key: phv_boolpart.a
+ Hash Key: phv_boolpart.b
+ -> Seq Scan on phv_boolpart_t phv_boolpart
+ Filter: (a IS TRUE)
+(5 rows)
+
+select * from (select a, b from phv_boolpart) t
+ where a is true
+ group by grouping sets (a, b);
+ a | b
+---+-----
+ t |
+ | yes
+(2 rows)
+
+drop table phv_boolpart;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index d93c0c03bab..359a9208056 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -1447,3 +1447,74 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
drop view part_abc_view;
drop table part_abc;
+
+--
+-- Check that operands wrapped in PlaceHolderVars are matched to partition
+-- keys, allowing partition pruning to occur. PlaceHolderVars can be
+-- introduced when a subquery's output is used with grouping sets.
+--
+create table phv_part (a int, b text) partition by list (a);
+create table phv_part_1 partition of phv_part for values in (1);
+create table phv_part_2 partition of phv_part for values in (2);
+create table phv_part_null partition of phv_part for values in (null);
+insert into phv_part values (1, 'one'), (2, 'two'), (null, 'null');
+
+-- OpExpr: PHV-wrapped operand matched via equal()
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a = 1
+ group by grouping sets (a, b);
+
+select * from (select a, b from phv_part) t
+ where a = 1
+ group by grouping sets (a, b);
+
+-- OpExpr with RelabelType: PHV wrapped around a casted column
+explain (costs off)
+select * from (select a::oid as x, b from phv_part) t
+ where x::int = 1
+ group by grouping sets (x, b);
+
+select * from (select a::oid as x, b from phv_part) t
+ where x::int = 1
+ group by grouping sets (x, b);
+
+-- ScalarArrayOpExpr: IN clause with PHV-wrapped operand
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a in (1, null)
+ group by grouping sets (a, b);
+
+select * from (select a, b from phv_part) t
+ where a in (1, null)
+ group by grouping sets (a, b);
+
+-- NullTest: IS NULL with PHV-wrapped operand
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a is null
+ group by grouping sets (a, b);
+
+select * from (select a, b from phv_part) t
+ where a is null
+ group by grouping sets (a, b);
+
+drop table phv_part;
+
+-- BooleanTest: IS TRUE with PHV-wrapped boolean partition key
+create table phv_boolpart (a bool, b text) partition by list (a);
+create table phv_boolpart_t partition of phv_boolpart for values in (true);
+create table phv_boolpart_f partition of phv_boolpart for values in (false);
+create table phv_boolpart_null partition of phv_boolpart default;
+insert into phv_boolpart values (true, 'yes'), (false, 'no'), (null, 'unknown');
+
+explain (costs off)
+select * from (select a, b from phv_boolpart) t
+ where a is true
+ group by grouping sets (a, b);
+
+select * from (select a, b from phv_boolpart) t
+ where a is true
+ group by grouping sets (a, b);
+
+drop table phv_boolpart;
--
2.39.5 (Apple Git-154)
[application/octet-stream] v2-V18-0001-Strip-PlaceHolderVars-from-partition-pruning-oper.patch (21.2K, 3-v2-V18-0001-Strip-PlaceHolderVars-from-partition-pruning-oper.patch)
download | inline diff:
From 115a98832ddcb71b6b8e7d02e569b3aba3afcb9f Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Thu, 2 Apr 2026 14:54:44 +0900
Subject: [PATCH v2] Strip PlaceHolderVars from partition pruning operands
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
When pulling up a subquery, its targetlist items may be wrapped in
PlaceHolderVars to enforce separate identity or as a result of outer
joins. This causes any upper-level WHERE clauses referencing these
outputs to contain PlaceHolderVars, which prevents partprune.c from
recognizing that they match partition key columns, defeating partition
pruning.
To fix, strip PlaceHolderVars from operands before comparing them to
partition keys. A PlaceHolderVar with empty phnullingrels appearing
in a relation-scan-level expression is effectively a no-op, so
stripping it is safe. This parallels the existing treatment in
indxpath.c for index matching.
In passing, rename strip_phvs_in_index_operand() to strip_noop_phvs()
and move it from indxpath.c to placeholder.c, since it is now a
general-purpose utility used by both index matching and partition
pruning code.
Back-patch to v18. Although this issue exists before that, changes in
that version made it common enough to notice. Given the lack of field
reports for older versions, I am not back-patching further. In the
v18 back-patch, strip_phvs_in_index_operand() is retained as a thin
wrapper around the new strip_noop_phvs() to avoid breaking third-party
extensions that may reference it.
Reported-by: Cándido Antonio Martínez Descalzo <candido@ninehq.com>
Diagnosed-by: David Rowley <dgrowleyml@gmail.com>
Author: Richard Guo <guofenglinux@gmail.com>
Discussion: https://postgr.es/m/CAH5YaUwVUWETTyVECTnhs7C=CVwi+uMSQH=cOkwAUqMdvXdwWA@mail.gmail.com
Backpatch-through: 18
---
src/backend/optimizer/path/indxpath.c | 86 +-----------
src/backend/optimizer/plan/createplan.c | 2 +-
src/backend/optimizer/util/placeholder.c | 91 ++++++++++++
src/backend/partitioning/partprune.c | 28 +++-
src/include/optimizer/placeholder.h | 1 +
src/test/regress/expected/partition_prune.out | 132 ++++++++++++++++++
src/test/regress/sql/partition_prune.sql | 71 ++++++++++
7 files changed, 324 insertions(+), 87 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 4fe8e5164c7..99caadb72b4 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -31,6 +31,7 @@
#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
+#include "optimizer/placeholder.h"
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
#include "utils/lsyscache.h"
@@ -196,8 +197,6 @@ static Expr *match_clause_to_ordering_op(IndexOptInfo *index,
static bool ec_member_matches_indexcol(PlannerInfo *root, RelOptInfo *rel,
EquivalenceClass *ec, EquivalenceMember *em,
void *arg);
-static bool contain_strippable_phv_walker(Node *node, void *context);
-static Node *strip_phvs_in_index_operand_mutator(Node *node, void *context);
/*
@@ -4422,7 +4421,7 @@ match_index_to_operand(Node *operand,
* a subtree) has been wrapped in PlaceHolderVars to enforce separate
* identity or as a result of outer joins.
*/
- operand = strip_phvs_in_index_operand(operand);
+ operand = strip_noop_phvs(operand);
/*
* Ignore any RelabelType node above the operand. This is needed to be
@@ -4488,88 +4487,15 @@ match_index_to_operand(Node *operand,
/*
* strip_phvs_in_index_operand
- * Strip PlaceHolderVar nodes from the given operand expression to
- * facilitate matching against an index's key.
*
- * A PlaceHolderVar appearing in a relation-scan-level expression is
- * effectively a no-op. Nevertheless, to play it safe, we strip only
- * PlaceHolderVars that are not marked nullable.
- *
- * The removal is performed recursively because PlaceHolderVars can be nested
- * or interleaved with other node types. We must peel back all layers to
- * expose the base operand.
- *
- * As a performance optimization, we first use a lightweight walker to check
- * for the presence of strippable PlaceHolderVars. The expensive mutator is
- * invoked only if a candidate is found, avoiding unnecessary memory allocation
- * and tree copying in the common case where no PlaceHolderVars are present.
+ * Retained as a backward-compatibility wrapper around strip_noop_phvs() to
+ * avoid breaking third-party extensions that may reference this function. New
+ * code should call strip_noop_phvs() directly.
*/
Node *
strip_phvs_in_index_operand(Node *operand)
{
- /* Don't mutate/copy if no target PHVs exist */
- if (!contain_strippable_phv_walker(operand, NULL))
- return operand;
-
- return strip_phvs_in_index_operand_mutator(operand, NULL);
-}
-
-/*
- * contain_strippable_phv_walker
- * Detect if there are any PlaceHolderVars in the tree that are candidates
- * for stripping.
- *
- * We identify a PlaceHolderVar as strippable only if its phnullingrels is
- * empty.
- */
-static bool
-contain_strippable_phv_walker(Node *node, void *context)
-{
- if (node == NULL)
- return false;
-
- if (IsA(node, PlaceHolderVar))
- {
- PlaceHolderVar *phv = (PlaceHolderVar *) node;
-
- if (bms_is_empty(phv->phnullingrels))
- return true;
- }
-
- return expression_tree_walker(node, contain_strippable_phv_walker,
- context);
-}
-
-/*
- * strip_phvs_in_index_operand_mutator
- * Recursively remove PlaceHolderVars in the tree that match the criteria.
- *
- * We strip a PlaceHolderVar only if its phnullingrels is empty, replacing it
- * with its contained expression.
- */
-static Node *
-strip_phvs_in_index_operand_mutator(Node *node, void *context)
-{
- if (node == NULL)
- return NULL;
-
- if (IsA(node, PlaceHolderVar))
- {
- PlaceHolderVar *phv = (PlaceHolderVar *) node;
-
- /* If matches the criteria, strip it */
- if (bms_is_empty(phv->phnullingrels))
- {
- /* Recurse on its contained expression */
- return strip_phvs_in_index_operand_mutator((Node *) phv->phexpr,
- context);
- }
-
- /* Otherwise, keep this PHV but check its contained expression */
- }
-
- return expression_tree_mutator(node, strip_phvs_in_index_operand_mutator,
- context);
+ return strip_noop_phvs(operand);
}
/*
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 06128a9f243..10b7358c28b 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -5271,7 +5271,7 @@ fix_indexqual_operand(Node *node, IndexOptInfo *index, int indexcol)
/*
* Remove any PlaceHolderVar wrapping of the indexkey
*/
- node = strip_phvs_in_index_operand(node);
+ node = strip_noop_phvs(node);
/*
* Remove any binary-compatible relabeling of the indexkey
diff --git a/src/backend/optimizer/util/placeholder.c b/src/backend/optimizer/util/placeholder.c
index e1cd00a72fb..86e3c1e751f 100644
--- a/src/backend/optimizer/util/placeholder.c
+++ b/src/backend/optimizer/util/placeholder.c
@@ -35,6 +35,8 @@ static void find_placeholders_recurse(PlannerInfo *root, Node *jtnode);
static void find_placeholders_in_expr(PlannerInfo *root, Node *expr);
static bool contain_placeholder_references_walker(Node *node,
contain_placeholder_references_context *context);
+static bool contain_noop_phv_walker(Node *node, void *context);
+static Node *strip_noop_phvs_mutator(Node *node, void *context);
/*
@@ -585,3 +587,92 @@ get_placeholder_nulling_relids(PlannerInfo *root, PlaceHolderInfo *phinfo)
result = bms_del_members(result, phinfo->ph_eval_at);
return result;
}
+
+/*
+ * strip_noop_phvs
+ * Strip no-op PlaceHolderVar nodes from the given expression tree.
+ *
+ * A PlaceHolderVar that is not marked as nullable (i.e., its phnullingrels
+ * is empty) is effectively a no-op when it appears in a relation-scan-level
+ * expression. This function strips such PlaceHolderVars, which is useful
+ * for matching expressions to index keys or partition keys in cases where
+ * the expression has been wrapped in PlaceHolderVars during subquery pullup.
+ *
+ * IMPORTANT: the caller must ensure that the expression is a scan-level
+ * expression, so that non-nullable PlaceHolderVars in it are indeed no-ops.
+ *
+ * The removal is performed recursively because PlaceHolderVars can be nested
+ * or interleaved with other node types. We must peel back all layers to
+ * expose the base expression.
+ *
+ * As a performance optimization, we first use a lightweight walker to check
+ * for the presence of strippable PlaceHolderVars. The expensive mutator is
+ * invoked only if a candidate is found, avoiding unnecessary memory allocation
+ * and tree copying in the common case where no PlaceHolderVars are present.
+ */
+Node *
+strip_noop_phvs(Node *node)
+{
+ /* Don't mutate/copy if no target PHVs exist */
+ if (!contain_noop_phv_walker(node, NULL))
+ return node;
+
+ return strip_noop_phvs_mutator(node, NULL);
+}
+
+/*
+ * contain_noop_phv_walker
+ * Detect if there are any PlaceHolderVars in the tree that are candidates
+ * for stripping.
+ *
+ * We identify a PlaceHolderVar as strippable only if its phnullingrels is
+ * empty.
+ */
+static bool
+contain_noop_phv_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, PlaceHolderVar))
+ {
+ PlaceHolderVar *phv = (PlaceHolderVar *) node;
+
+ if (bms_is_empty(phv->phnullingrels))
+ return true;
+ }
+
+ return expression_tree_walker(node, contain_noop_phv_walker,
+ context);
+}
+
+/*
+ * strip_noop_phvs_mutator
+ * Recursively remove PlaceHolderVars that are not marked nullable.
+ *
+ * We strip a PlaceHolderVar only if its phnullingrels is empty, replacing it
+ * with its contained expression.
+ */
+static Node *
+strip_noop_phvs_mutator(Node *node, void *context)
+{
+ if (node == NULL)
+ return NULL;
+
+ if (IsA(node, PlaceHolderVar))
+ {
+ PlaceHolderVar *phv = (PlaceHolderVar *) node;
+
+ if (bms_is_empty(phv->phnullingrels))
+ {
+ /* Recurse on its contained expression */
+ return strip_noop_phvs_mutator((Node *) phv->phexpr,
+ context);
+ }
+
+ /* Otherwise, keep this PHV but check its contained expression */
+ }
+
+ return expression_tree_mutator(node, strip_noop_phvs_mutator,
+ context);
+}
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c
index 48a35f763e9..2ef98d6ad6e 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -49,6 +49,7 @@
#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
+#include "optimizer/placeholder.h"
#include "parser/parsetree.h"
#include "partitioning/partbounds.h"
#include "partitioning/partprune.h"
@@ -1814,6 +1815,15 @@ gen_prune_steps_from_opexps(GeneratePruningStepsContext *context,
* and couldn't possibly match any other one either, due to its form or
* properties (such as containing a volatile function).
* Output arguments: none set.
+ *
+ * Note that when pulling up a subquery, the clause operands may get wrapped
+ * in PlaceHolderVars to enforce separate identity or as a result of outer
+ * joins. We must strip such no-op PlaceHolderVars before comparing operands
+ * to the partition key, otherwise the equal() checks will fail to recognize
+ * valid matches. This is safe because the clauses here are always
+ * relation-scan-level expressions, where a PlaceHolderVar with empty
+ * phnullingrels is effectively a no-op. Stripping may also bring separate
+ * RelabelType nodes into adjacency, so we must loop when peeling those.
*/
static PartClauseMatchStatus
match_clause_to_partition_key(GeneratePruningStepsContext *context,
@@ -1929,10 +1939,12 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
PartClauseInfo *partclause;
leftop = (Expr *) get_leftop(clause);
- if (IsA(leftop, RelabelType))
+ leftop = (Expr *) strip_noop_phvs((Node *) leftop);
+ while (IsA(leftop, RelabelType))
leftop = ((RelabelType *) leftop)->arg;
rightop = (Expr *) get_rightop(clause);
- if (IsA(rightop, RelabelType))
+ rightop = (Expr *) strip_noop_phvs((Node *) rightop);
+ while (IsA(rightop, RelabelType))
rightop = ((RelabelType *) rightop)->arg;
opno = opclause->opno;
@@ -2180,7 +2192,8 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
*elem_clauses;
ListCell *lc1;
- if (IsA(leftop, RelabelType))
+ leftop = (Expr *) strip_noop_phvs((Node *) leftop);
+ while (IsA(leftop, RelabelType))
leftop = ((RelabelType *) leftop)->arg;
/* check if the LHS matches this partition key */
@@ -2406,7 +2419,8 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
NullTest *nulltest = (NullTest *) clause;
Expr *arg = nulltest->arg;
- if (IsA(arg, RelabelType))
+ arg = (Expr *) strip_noop_phvs((Node *) arg);
+ while (IsA(arg, RelabelType))
arg = ((RelabelType *) arg)->arg;
/* Does arg match with this partition key column? */
@@ -3718,7 +3732,8 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, Expr *partkey,
BooleanTest *btest = (BooleanTest *) clause;
leftop = btest->arg;
- if (IsA(leftop, RelabelType))
+ leftop = (Expr *) strip_noop_phvs((Node *) leftop);
+ while (IsA(leftop, RelabelType))
leftop = ((RelabelType *) leftop)->arg;
if (equal(leftop, partkey))
@@ -3755,7 +3770,8 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, Expr *partkey,
leftop = is_not_clause ? get_notclausearg(clause) : clause;
- if (IsA(leftop, RelabelType))
+ leftop = (Expr *) strip_noop_phvs((Node *) leftop);
+ while (IsA(leftop, RelabelType))
leftop = ((RelabelType *) leftop)->arg;
/* Compare to the partition key, and make up a clause ... */
diff --git a/src/include/optimizer/placeholder.h b/src/include/optimizer/placeholder.h
index db92d8861ba..0186f18bb06 100644
--- a/src/include/optimizer/placeholder.h
+++ b/src/include/optimizer/placeholder.h
@@ -32,5 +32,6 @@ extern bool contain_placeholder_references_to(PlannerInfo *root, Node *clause,
int relid);
extern Relids get_placeholder_nulling_relids(PlannerInfo *root,
PlaceHolderInfo *phinfo);
+extern Node *strip_noop_phvs(Node *node);
#endif /* PLACEHOLDER_H */
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index d1966cd7d82..7aa3e5cbaf9 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -4801,3 +4801,135 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
drop view part_abc_view;
drop table part_abc;
+--
+-- Check that operands wrapped in PlaceHolderVars are matched to partition
+-- keys, allowing partition pruning to occur. PlaceHolderVars can be
+-- introduced when a subquery's output is used with grouping sets.
+--
+create table phv_part (a int, b text) partition by list (a);
+create table phv_part_1 partition of phv_part for values in (1);
+create table phv_part_2 partition of phv_part for values in (2);
+create table phv_part_null partition of phv_part for values in (null);
+insert into phv_part values (1, 'one'), (2, 'two'), (null, 'null');
+-- OpExpr: PHV-wrapped operand matched via equal()
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a = 1
+ group by grouping sets (a, b);
+ QUERY PLAN
+---------------------------------------
+ MixedAggregate
+ Hash Key: phv_part.b
+ Group Key: phv_part.a
+ -> Seq Scan on phv_part_1 phv_part
+ Filter: (a = 1)
+(5 rows)
+
+select * from (select a, b from phv_part) t
+ where a = 1
+ group by grouping sets (a, b);
+ a | b
+---+-----
+ 1 |
+ | one
+(2 rows)
+
+-- OpExpr with RelabelType: PHV wrapped around a casted column
+explain (costs off)
+select * from (select a::oid as x, b from phv_part) t
+ where x::int = 1
+ group by grouping sets (x, b);
+ QUERY PLAN
+-------------------------------------------
+ HashAggregate
+ Hash Key: (phv_part.a)::oid
+ Hash Key: phv_part.b
+ -> Seq Scan on phv_part_1 phv_part
+ Filter: (((a)::oid)::integer = 1)
+(5 rows)
+
+select * from (select a::oid as x, b from phv_part) t
+ where x::int = 1
+ group by grouping sets (x, b);
+ x | b
+---+-----
+ 1 |
+ | one
+(2 rows)
+
+-- ScalarArrayOpExpr: IN clause with PHV-wrapped operand
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a in (1, null)
+ group by grouping sets (a, b);
+ QUERY PLAN
+---------------------------------------------------
+ HashAggregate
+ Hash Key: phv_part.a
+ Hash Key: phv_part.b
+ -> Seq Scan on phv_part_1 phv_part
+ Filter: (a = ANY ('{1,NULL}'::integer[]))
+(5 rows)
+
+select * from (select a, b from phv_part) t
+ where a in (1, null)
+ group by grouping sets (a, b);
+ a | b
+---+-----
+ 1 |
+ | one
+(2 rows)
+
+-- NullTest: IS NULL with PHV-wrapped operand
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a is null
+ group by grouping sets (a, b);
+ QUERY PLAN
+------------------------------------------
+ HashAggregate
+ Hash Key: phv_part.a
+ Hash Key: phv_part.b
+ -> Seq Scan on phv_part_null phv_part
+ Filter: (a IS NULL)
+(5 rows)
+
+select * from (select a, b from phv_part) t
+ where a is null
+ group by grouping sets (a, b);
+ a | b
+---+------
+ |
+ | null
+(2 rows)
+
+drop table phv_part;
+-- BooleanTest: IS TRUE with PHV-wrapped boolean partition key
+create table phv_boolpart (a bool, b text) partition by list (a);
+create table phv_boolpart_t partition of phv_boolpart for values in (true);
+create table phv_boolpart_f partition of phv_boolpart for values in (false);
+create table phv_boolpart_null partition of phv_boolpart default;
+insert into phv_boolpart values (true, 'yes'), (false, 'no'), (null, 'unknown');
+explain (costs off)
+select * from (select a, b from phv_boolpart) t
+ where a is true
+ group by grouping sets (a, b);
+ QUERY PLAN
+-----------------------------------------------
+ HashAggregate
+ Hash Key: phv_boolpart.a
+ Hash Key: phv_boolpart.b
+ -> Seq Scan on phv_boolpart_t phv_boolpart
+ Filter: (a IS TRUE)
+(5 rows)
+
+select * from (select a, b from phv_boolpart) t
+ where a is true
+ group by grouping sets (a, b);
+ a | b
+---+-----
+ t |
+ | yes
+(2 rows)
+
+drop table phv_boolpart;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index d93c0c03bab..359a9208056 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -1447,3 +1447,74 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
drop view part_abc_view;
drop table part_abc;
+
+--
+-- Check that operands wrapped in PlaceHolderVars are matched to partition
+-- keys, allowing partition pruning to occur. PlaceHolderVars can be
+-- introduced when a subquery's output is used with grouping sets.
+--
+create table phv_part (a int, b text) partition by list (a);
+create table phv_part_1 partition of phv_part for values in (1);
+create table phv_part_2 partition of phv_part for values in (2);
+create table phv_part_null partition of phv_part for values in (null);
+insert into phv_part values (1, 'one'), (2, 'two'), (null, 'null');
+
+-- OpExpr: PHV-wrapped operand matched via equal()
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a = 1
+ group by grouping sets (a, b);
+
+select * from (select a, b from phv_part) t
+ where a = 1
+ group by grouping sets (a, b);
+
+-- OpExpr with RelabelType: PHV wrapped around a casted column
+explain (costs off)
+select * from (select a::oid as x, b from phv_part) t
+ where x::int = 1
+ group by grouping sets (x, b);
+
+select * from (select a::oid as x, b from phv_part) t
+ where x::int = 1
+ group by grouping sets (x, b);
+
+-- ScalarArrayOpExpr: IN clause with PHV-wrapped operand
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a in (1, null)
+ group by grouping sets (a, b);
+
+select * from (select a, b from phv_part) t
+ where a in (1, null)
+ group by grouping sets (a, b);
+
+-- NullTest: IS NULL with PHV-wrapped operand
+explain (costs off)
+select * from (select a, b from phv_part) t
+ where a is null
+ group by grouping sets (a, b);
+
+select * from (select a, b from phv_part) t
+ where a is null
+ group by grouping sets (a, b);
+
+drop table phv_part;
+
+-- BooleanTest: IS TRUE with PHV-wrapped boolean partition key
+create table phv_boolpart (a bool, b text) partition by list (a);
+create table phv_boolpart_t partition of phv_boolpart for values in (true);
+create table phv_boolpart_f partition of phv_boolpart for values in (false);
+create table phv_boolpart_null partition of phv_boolpart default;
+insert into phv_boolpart values (true, 'yes'), (false, 'no'), (null, 'unknown');
+
+explain (costs off)
+select * from (select a, b from phv_boolpart) t
+ where a is true
+ group by grouping sets (a, b);
+
+select * from (select a, b from phv_boolpart) t
+ where a is true
+ group by grouping sets (a, b);
+
+drop table phv_boolpart;
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Potential partition pruning regression on PostgreSQL 18
2026-04-01 11:56 Potential partition pruning regression on PostgreSQL 18 Cándido Antonio Martínez Descalzo <candido@ninehq.com>
2026-04-01 23:00 ` Re: Potential partition pruning regression on PostgreSQL 18 David Rowley <dgrowleyml@gmail.com>
2026-04-02 07:34 ` Re: Potential partition pruning regression on PostgreSQL 18 Richard Guo <guofenglinux@gmail.com>
2026-04-07 08:00 ` Re: Potential partition pruning regression on PostgreSQL 18 Richard Guo <guofenglinux@gmail.com>
@ 2026-04-09 08:05 ` Richard Guo <guofenglinux@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Richard Guo @ 2026-04-09 08:05 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Cándido Antonio Martínez Descalzo <candido@ninehq.com>; Pg Hackers <pgsql-hackers@lists.postgresql.org>
On Tue, Apr 7, 2026 at 5:00 PM Richard Guo <guofenglinux@gmail.com> wrote:
> Here are the more formal patches for HEAD and for v18.
>
> In the HEAD patch, I renamed strip_phvs_in_index_operand() to
> strip_noop_phvs() and moved it from indxpath.c to placeholder.c, since
> it is now a general-purpose utility used by both index matching and
> partition pruning code.
>
> However, since strip_phvs_in_index_operand() is an extern function
> declared in a public header, I'm worried that third-party extensions
> may have started calling it after it was introduced in ad66f705f. So
> for the v18 back-patch, I retained strip_phvs_in_index_operand() in
> indxpath.c as a thin wrapper around the new strip_noop_phvs(), to
> avoid breaking such extensions in a minor release.
>
> Does this seem like reasonable caution, or is it overkill given how
> recently the function was introduced?
I prefer to be cautious, so I've committed the patches as-is.
- Richard
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-04-09 08:05 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-01 11:56 Potential partition pruning regression on PostgreSQL 18 Cándido Antonio Martínez Descalzo <candido@ninehq.com>
2026-04-01 23:00 ` David Rowley <dgrowleyml@gmail.com>
2026-04-02 07:34 ` Richard Guo <guofenglinux@gmail.com>
2026-04-07 08:00 ` Richard Guo <guofenglinux@gmail.com>
2026-04-09 08:05 ` Richard Guo <guofenglinux@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox