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

On Thu, Apr 19, 2018 at 12:01 AM, Alvaro Herrera
<alvherre@alvh.no-ip.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