Re: no partition pruning when partitioning using array type

On Fri, Dec 8, 2017 at 5:40 AM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> 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.

I guess the question is whether that's guaranteed to be safe. I spent
a little bit of time thinking about it and I don't see a problem. The
function is careful to check that the opclasses and collations of the
OpExprs are compatible, and it is the behavior of the operator that is
in question here, not the column type, so your change seems OK to me.
But I hope somebody else will also study this, because this stuff is
fairly subtle and I would not like to be the one who breaks it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company