| From: | Andrei Lepikhov <lepihov(at)gmail(dot)com> | 
|---|---|
| To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> | 
| Subject: | Partition pruning on parameters grouped into an array does not prune properly | 
| Date: | 2025-03-26 15:19:15 | 
| Message-ID: | a808f8aa-3cac-4f6a-993e-ff77680979d6@gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-hackers | 
Hi,
Working on improving partition pruning [1] I found a case that I may 
treat like a bug. I mean the case with expression on a partitioning 
column like:
id = ARRAY[$1,$2]
Basically, pruning on ARRAY[$1,$2] works in the case of single column 
(see correct-pruning-example.sql in attachment):
PREPARE test (int, int) AS
   SELECT * FROM array_prune WHERE id = ANY(ARRAY[$1,$2]);
EXPLAIN (COSTS OFF) EXECUTE test(1,2);
  Append
    Subplans Removed: 1
    ->  Seq Scan on array_prune_t0 array_prune_1
          Filter: (id = ANY (ARRAY[$1, $2]))
But if we partition on HASH(x,y) it is not working (see 
incorrect-pruning-example.sql):
PREPARE test2 (int,int) AS
  SELECT 1 FROM array_prune
  WHERE id1 = ANY(ARRAY[$1]) AND id2 = ANY(ARRAY[$2]);
EXPLAIN (COSTS OFF) EXECUTE test2(1,-1);
  Append
    ->  Seq Scan on array_prune_t0 array_prune_1
          Filter: ((id1 = ANY (ARRAY[$1])) AND (id2 = ANY (ARRAY[$2])))
    ->  Seq Scan on array_prune_t1 array_prune_2
          Filter: ((id1 = ANY (ARRAY[$1])) AND (id2 = ANY (ARRAY[$2])))
Although its analogue works nice:
PREPARE test3 (int,int) AS
  SELECT 1 FROM array_prune
  WHERE id1 = $1 AND id2 = $2;
EXPLAIN (COSTS OFF) EXECUTE test3(1,-1);
  Append
    Subplans Removed: 1
    ->  Seq Scan on array_prune_t0 array_prune_1
          Filter: ((id1 = $1) AND (id2 = $2))
So, before diving into the partitioning depths, someone may quickly say 
it is not a bug, but I am missing something. Some hidden semantics?
[1] Prune partitions by ScalarArrayOpExpr with an array parameter 
(partkey = ANY($1))
https://www.postgresql.org/message-id/b8cdd20f-b34b-42b9-8c7c-dae864b7b3b2@gmail.com
-- 
regards, Andrei Lepikhov
| Attachment | Content-Type | Size | 
|---|---|---|
| correct-pruning-example.sql | application/sql | 1.1 KB | 
| incorrect-pruning-example.sql | application/sql | 1.1 KB | 
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jasper Smit | 2025-03-26 15:26:14 | Assertion with aborted UPDATE in subtransaction | 
| Previous Message | Tom Lane | 2025-03-26 15:15:08 | Re: Add Postgres module info |