Re: pruning disabled for array, enum, record, range type partition keys

On Thu, Apr 19, 2018 at 12:01 AM, Alvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> Amit Langote wrote:
>
>> On 2018/04/18 7:11, Alvaro Herrera wrote:
>>
>> @@ -1546,8 +1546,8 @@ match_clause_to_partition_key(RelOptInfo *rel,
>> case PARTITION_STRATEGY_HASH:
>> cmpfn = get_opfamily_proc(part_scheme->partopfamily[partkeyidx],
>> - op_righttype, op_righttype,
>> - HASHEXTENDED_PROC);
>> + part_scheme->partopcintype[partkeyidx],
>> + op_righttype, HASHEXTENDED_PROC);
>>
>> This change is not quite right, because it disables pruning. The above
>> returns InvalidOid as there are no hash AM procedures (in pg_amproc) whose
>> lefttype and righttype don't match.
>
> Makes sense. Still, I was expecting that pruning of hash partitioning
> would also work for pseudotypes, yet it doesn't.

It does?

+-- array type hash partition key
+create table pph_arrpart (a int[]) partition by hash (a);
+create table pph_arrpart1 partition of pph_arrpart for values with
(modulus 2, remainder 0);
+create table pph_arrpart2 partition of pph_arrpart for values with
(modulus 2, remainder 1);
+insert into pph_arrpart values ('{1}'), ('{1, 2}'), ('{4, 5}');
+select tableoid::regclass, * from pph_arrpart order by 1;
+ tableoid | a
+--------------+-------
+ pph_arrpart1 | {1,2}
+ pph_arrpart1 | {4,5}
+ pph_arrpart2 | {1}
+(3 rows)
+
+explain (costs off) select * from pph_arrpart where a = '{1}';
+ QUERY PLAN
+----------------------------------------
+ Append
+ -> Seq Scan on pph_arrpart2
+ Filter: (a = '{1}'::integer[])
+(3 rows)
+
+explain (costs off) select * from pph_arrpart where a = '{1, 2}';
+ QUERY PLAN
+------------------------------------------
+ Append
+ -> Seq Scan on pph_arrpart1
+ Filter: (a = '{1,2}'::integer[])
+(3 rows)

Thanks,
Amit