Re: Partition pruning on parameters grouped into an array does not prune properly

On 3/27/25 01:58, David Rowley wrote:
> I wonder if we need to redesign this to not do that recursive
> processing and instead have it so match_clause_to_partition_key() can
> generate multiple PartClauseInfos. If we've matched to the
> ScalarArrayOpExpr then I think each generated PartClauseInfo should
> have the same PartClauseMatchStatus. That would also get rid of the
> (kinda silly) overhead we have of having to match the
> ScalarArrayOpExpr to the partition key, then generating OpExprs and
> having to match those again, even though we know they will match.
>
> I suspect the fix for this might be a bit invasive to backpatch. Maybe
> it's something we can give a bit more clear thought to after the
> freeze is over.
Thank you for the explanation!

Why does the pruning machinery only include the OpExpr pruning
operation? Often, when preparing for pruning steps, we don’t know the
exact number of values we will encounter during the initial or execution
pruning stages. I believe it would be beneficial to have an iterator -
something similar to the predicate_implied_by function - that can
iteratively extract values from an array. This would allow pruning in
practical scenarios, such as the following:

CREATE OR REPLACE FUNCTION some_business_logic(val integer)
RETURNS integer[] AS $$
BEGIN
IF txid_current() % 2 = 0 THEN
RETURN ARRAY[val];
ELSE
RETURN ARRAY[val + 1];
END IF;
END;
$$ LANGUAGE plpgsql STRICT STABLE;

PREPARE test (int) AS
SELECT * FROM array_prune
WHERE id = ANY (some_business_logic($1));
EXPLAIN (ANALYZE, COSTS OFF) EXECUTE test(1);

Also in that case we wouldn't need to decompose a ScalarArrayOpExpr to
the list of OpExpr clauses to prune partitions.
--
regards, Andrei Lepikhov