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
