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

Hi Baotiao,

I reproduced your case on 18.6, on 19devel at the commit you named
(f2d7570cdde), and on master; all three observations hold exactly --
1000 seq-scan children each carrying the full 1000-element array, the
706992-vs-1000 estimate, and ~976k rows examined.

The measurement that stood out is that the dominant cost is plan choice,
not the repeated array work. Keeping the full array but forcing the
index only drops execution (same 706992 estimate, 1000 rows read).
Giving each child only the one constant that maps to it takes the
estimate to 1, so the planner picks the index on its own, and the whole
query goes from ~185 ms to ~25 ms here (-87%).

> each child's ExprState builds its own hash table from the full array.
> This is repeated array processing across children

The hash tables are cheap to build (under 0.5% of the profile); the
execution cost is probing them once per row of the seq scan the estimate
caused. And reducing a singleton to id = C barely moves anything
(~0.45 ms over all 1000 children) -- what moves the plan is each child
seeing only its own constants.

On planning, scalararraysel is ~93% of it: one eqsel per element per
child, each redoing examine_variable / selectivity / aclcheck, so with
one id per partition it grows as children x constants. In a sweep here
the child plans flip from index to seq between 250 and 500 ids, which is
the estimate crossing over.

> Would it make sense to use the partition bounds to simplify constant
> ScalarArrayOpExpr quals for each child?

For the hash case the irrelevant constants cannot be dropped the way [2]
does it, since satisfies_hash_partition() is not something predtest can
refute. But pruning already maps each constant to its partition to reach
the 1000 live children, so that mapping exists at that point; reusing it
to restrict each child's array to the constants that reach it looks more
tractable than proving anything from the hash constraint. The range/list
case is the one that could instead ride the partition-constraint approach
in [2].

One caveat on the numbers: my "split" is a UNION ALL with a Subquery Scan
per child, so its execution is an upper bound on what an in-place rewrite
would achieve; and I measured on x86 with autovacuum off, while you were
on arm64 macOS.

Happy to share the full script and numbers if useful.

Regards,
Manu