Partition pruning on parameters grouped into an array does not prune properly
От | Andrei Lepikhov |
---|---|
Тема | Partition pruning on parameters grouped into an array does not prune properly |
Дата | |
Msg-id | a808f8aa-3cac-4f6a-993e-ff77680979d6@gmail.com обсуждение исходный текст |
Ответы |
Re: Partition pruning on parameters grouped into an array does not prune properly
|
Список | 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
Вложения
В списке pgsql-hackers по дате отправления: