Partition-aware simplification of constant IN lists after partition pruning

Hi hackers,

A colleague constructed a case with a bigint primary key, 1024 HASH
partitions, and one million rows. I reproduced a performance issue:
partition pruning determines the correct target partition for each
constant in an IN list, but each retained child still receives the
entire list, including IDs that cannot belong to that child.

For example, when 1000 IDs map to 1000 different partitions, each child
has only one relevant ID. Nevertheless, all 1000 child scans have the
same 1000-element ScalarArrayOpExpr. This leads to repeated processing
of the full array during planning and execution.

The attached psql script contains the original constant lists and runs
VACUUM ANALYZE before the queries. It uses ordinary IN (...) clauses,
not an IN (VALUES ...) subquery or an array parameter. The setup is:

CREATE TABLE prune_repro (
id bigint NOT NULL PRIMARY KEY
) PARTITION BY HASH (id);

-- Create 1024 partitions, modulus 1024, remainders 0..1023.
-- Insert generate_series(1, 1000000).

I tested the attachment on PostgreSQL 18.6, arm64 macOS, with
shared_buffers=128MB, enable_partition_pruning=on,
max_parallel_workers_per_gather=0, and jit=off. One run of
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) gave:

Case IDs Partitions Planning ms Execution ms
All IDs in one partition 953 1 0.576 0.051
One ID per partition 1000 1000 116.780 55.116

These are illustrative timings, not a benchmark of the cost of IN
processing alone. The queries return 953 and 1000 rows respectively;
the script also checks count(DISTINCT tableoid). The second plan has
exactly 1000 child scans, so the remaining 24 partitions are pruned.
I also observed the same behavior in a local 19devel checkout at
f2d7570cdde75fd67acb279063b2701806662035.

A representative child from the second query is shown below, with the
array elided for readability:

Seq Scan on prune_repro_p0
(cost=2.50..22.02 rows=624 width=0)
(actual rows=1.00 loops=1)
Filter: (id = ANY ('{all 1000 IDs}'::bigint[]))
Rows Removed by Filter: 967

Across the 1000 child scans, the sum of estimated rows is 706992,
whereas the actual output is 1000 rows. The plan chooses sequential
scans and examines 976466 rows. VACUUM ANALYZE does not resolve this.

Looking at the implementation, scalararraysel() processes the full
constant array independently for each retained child. For this bigint
IN list, execution uses hashed ScalarArrayOpExpr evaluation, but each
child's ExprState builds its own hash table from the full array. This
is repeated array processing across children, rather than a linear
search through all 1000 IDs for every scanned row.

Would it make sense to use the partition bounds to simplify constant
ScalarArrayOpExpr quals for each child? For this single-column HASH
key case, each constant can already be mapped to its partition during
pruning. Passing only the relevant constants to each child, and
reducing a singleton list to an equality, could both reduce repeated
work and improve the child row estimates and scan choices. I have not
implemented a patch yet; I would appreciate feedback on the approach
and where such simplification should fit in the planner.

I found two related discussions:

[1] Optimize planner memory consumption for huge arrays
https://www.postgresql.org/message-id/CAExHW5sUuN7JGp1rdGhg2B_SLvcRAVPc3cM-hOV0mf8K_HfQhQ%40mail.gmail.com

This discusses array selectivity estimation work being repeated
for each partition, primarily from the memory-consumption angle.

[2] avoid bitmapOR-ing indexes with scan condition inconsistent with
partition constraint
https://www.postgresql.org/message-id/228337.1605212074%40sss.pgh.pa.us

This discusses simplifying child restrictions using partition
constraints and using the result in row estimates, but also notes
the inability to prove clauses from satisfies_hash_partition().

Regards,
Baotiao