no partition pruning when partitioning using array type

Поиск
Список
Период
Сортировка
От Amit Langote
Тема no partition pruning when partitioning using array type
Дата
Msg-id 54745d13-7ed4-54ac-97d8-ea1eec95ae25@lab.ntt.co.jp
обсуждение исходный текст
Ответы Re: no partition pruning when partitioning using array type
Список pgsql-hackers
Hi.

I noticed that if you partition using a array type column, partition
pruning using constraint exclusion fails to work due to a minor problem.

Example:

create table p (a int[]) partition by list (a);
create table p1 partition of p for values in ('{1}');
create table p1 partition of p for values in ('{2, 3}', '{4, 5}');

explain select a from p where a = '{1}';
                        QUERY PLAN
|---------------------------------------------------------
 Append  (cost=0.00..54.00 rows=14 width=32)
   ->  Seq Scan on p1  (cost=0.00..27.00 rows=7 width=32)
         Filter: (a = '{1}'::integer[])
   ->  Seq Scan on p2  (cost=0.00..27.00 rows=7 width=32)
         Filter: (a = '{1}'::integer[])

explain select a from p where a = '{2, 3}';
                        QUERY PLAN
|---------------------------------------------------------
 Append  (cost=0.00..54.00 rows=14 width=32)
   ->  Seq Scan on p1  (cost=0.00..27.00 rows=7 width=32)
         Filter: (a = '{2,3}'::integer[])
   ->  Seq Scan on p2  (cost=0.00..27.00 rows=7 width=32)
         Filter: (a = '{2,3}'::integer[])
(5 rows)


In the case of array type partition key, make_partition_op_expr() will
have to put a RelabelType node on top of the partition key Var, after
having selected an = operator from the array_ops family.  The RelabelType
causes operator_predicate_proof() to fail to consider predicate leftop and
clause leftop as equal, because only one of them ends up having the
RelabelType attached to it.

As a simple measure, the attached patch teaches operator_predicate_proof()
to strip RelabelType nodes from all the nodes it compares using equal().
I also added a relevant test in partition_prune.sql.

Thoughts?

Thanks,
Amit

Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Amit Kapila
Дата:
Сообщение: Re: explain analyze output with parallel workers - question aboutmeaning of information for explain.depesz.com
Следующее
От: Everaldo Canuto
Дата:
Сообщение: Re: proposal: alternative psql commands quit and exit