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

From: Andrei Lepikhov <lepihov(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Prune partitions by ScalarArrayOpExpr with an array parameter (partkey = ANY($1))
Date: 2025-03-17 13:28:02
Message-ID: b8cdd20f-b34b-42b9-8c7c-dae864b7b3b2@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0001-Enhance-partition-pruning-for-an-array-parameter.patch text/x-patch 13.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Steven Niu 2025-03-17 13:28:50 Re: Forbid to DROP temp tables of other sessions
Previous Message Tomas Vondra 2025-03-17 13:27:11 Re: Snapshot related assert failure on skink