Prune partitions by ScalarArrayOpExpr with an array parameter (partkey = ANY($1))

Hi,

As I see, initial pruning doesn't work in the case when a
ScalarArrayOpExpr contains a parameter as the RHS of the expression,
like following:

partkey = ANY($1)

As colleagues say, it is quite typical to use stored procedures, pass an
array of IDs as a parameter, and use it in a SELECT clause.

So, here I propose a patch that extends pruning machinery. It is nothing
innovative or complicated, but I'm not sure it is fully operational so
far: it may need some discussion, review and polishing.

I intended to add it to the next commitfest if this feature makes sense.

--
regards, Andrei Lepikhov

po 17. 3. 2025 v 14:28 odesílatel Andrei Lepikhov <lepihov(at)gmail(dot)com>
napsal:

> Hi,
>
> As I see, initial pruning doesn't work in the case when a
> ScalarArrayOpExpr contains a parameter as the RHS of the expression,
> like following:
>
> partkey = ANY($1)
>
> As colleagues say, it is quite typical to use stored procedures, pass an
> array of IDs as a parameter, and use it in a SELECT clause.
>
> So, here I propose a patch that extends pruning machinery. It is nothing
> innovative or complicated, but I'm not sure it is fully operational so
> far: it may need some discussion, review and polishing.
>
> I intended to add it to the next commitfest if this feature makes sense.
>

+1

Pavel

> --
> regards, Andrei Lepikhov
>

On 3/17/25 14:28, Andrei Lepikhov wrote:
> Hi,
>
> As I see, initial pruning doesn't work in the case when a
> ScalarArrayOpExpr contains a parameter as the RHS of the expression,
> like following:
>
> partkey = ANY($1)
>
> As colleagues say, it is quite typical to use stored procedures, pass an
> array of IDs as a parameter, and use it in a SELECT clause.
>
> So, here I propose a patch that extends pruning machinery. It is nothing
> innovative or complicated, but I'm not sure it is fully operational so
> far: it may need some discussion, review and polishing.
>
> I intended to add it to the next commitfest if this feature makes sense.

For the record, here I want to resolve a bit more general issue, than
just pruning on a "key op ANY($1)" expression. For example, a hashed
InitPlan also may be used for this purpose:

CREATE TABLE part (a int)
PARTITION BY RANGE (a);

CREATE TABLE part1 PARTITION OF part FOR VALUES FROM (0) to (101);
CREATE TABLE part2 PARTITION OF part FOR VALUES FROM (101) to (200);
INSERT INTO part (a) SELECT value%200 FROM generate_series(1,1000) AS value;
CREATE TABLE other (x int, y int);
INSERT INTO other (x,y) VALUES (1,1);

EXPLAIN (VERBOSE, ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF)
SELECT * FROM part
WHERE a = ANY((SELECT array_agg(x) AS x
FROM other
WHERE y between 0 and 100)::integer[]);

I think subqueries is quite a common pattern and pruning in this case
makes sense.

--
regards, Andrei Lepikhov