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 по дате отправления: