Re: Partition-aware simplification of constant IN lists after partition pruning

Hi zthhh,

> In my test, the original IN query already uses Parallel Index Only Scans.
> Each of the 1000 scanned partitions returns one row, with zero heap
> fetches, and all buffer accesses are hits. Nevertheless, execution takes
> about 987 ms.

You're right that the cost isn't just the seq-scan choice, and it isn't the
index work either. I reproduced your plan shape on 18.6, on 19devel
(f2d7570) and on master; all three agree.

Each child reports "Index Searches: 1" -- one descent per child, not one per
array element. The time is in shipping the plan to the parallel workers.
Under perf, the leader spends ~41% in nodeToString -> outDatum, printing each
child's 1000-element bigint array byte by byte, and the workers spend ~37% in
stringToNode -> readDatum parsing them back; the index probes are ~2.6%. In
your plan the Gather returns its first row at 651 of 973 ms; in the same shape
here, 287 of 418 ms, while the 1000 child scans together take 17.7 ms.

Toggling only parallelism on the same full-array index plan, execution goes
from 14.0 ms to 419 ms. The same plan with the Gather but 0 workers launched
is still 290 ms: the leader serializes the plan anyway, and the 1000-element
array is in every child.

Even serially, the full array still costs at execution with the index: 14.0 ms
vs 5.9 ms when each child carries only its own constant, because
_bt_preprocess_keys sorts and de-dups the 1000 keys per child before its
single descent.

So the restriction helps both cases, for different reasons: it takes the
estimate to 1 (which is what picked the seq scan in the original report) and
it shrinks what each child preprocesses and what the Gather ships. Measured
against today's IN, same binary: the serial seq-scan case is 186.8 ms wall
(planning 129.5, execution 42.9); your VALUES rewrite is 25.4 ms (planning
10.5, execution 13.5); and restricting each child's array in place is 29.0 ms
(planning 21.5, execution 5.9). That is an 84% wall drop in the serial case
and 94% in your parallel shape; with the estimate corrected the planner no
longer parallelizes at all. The VALUES rewrite is a good workaround that
needs no planner change.

For where a fix would live: pruning already maps each constant to its
partition once (prune_append_rel_partitions is ~1% here), and that mapping is
in hand in expand_partitioned_rtentry, where restricting each live child to
its own constants gives the right 1000 rows. Range and list partitioning
could instead refute the irrelevant constants through the partition
constraint, but hash cannot, since satisfies_hash_partition() isn't something
predtest can refute -- reusing pruning's mapping is the route that also covers
hash.

I couldn't match your exact cost settings (your child index-only scan is
priced at 352 vs 25.9 here), so I reproduced both of your plan shapes with
explicit knobs rather than inferring them.

Attached are the measurements (scenarios, timings with 95% CIs, and the perf
breakdown), the raw median/CI summary, and the throwaway prototype I used to
bound the benefit -- measurement-only, single-column hash, not a proposal.
The full logs and repro harness are available if useful.

Regards,
Manu