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-dae864b7b...@gmail.com -- regards, Andrei Lepikhov
correct-pruning-example.sql
Description: application/sql
incorrect-pruning-example.sql
Description: application/sql
