Re: [HACKERS] path toward faster partition pruning

On 2018/02/07 1:36, Robert Haas wrote:
> On Tue, Feb 6, 2018 at 4:55 AM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>> I understand why COLLATION_MATCH think that a collation OID match is
>>> OK, but why is InvalidOid also OK? Can you add a comment? Maybe some
>>> test cases, too?
>>
>> partcollid == InvalidOid means the partition key is of uncollatable type,
>> so further checking the collation is unnecessary.
>
> Yeah, but in that case wouldn't BOTH OIDs be InvalidOid, and thus the
> equality test would mach anyway?

It seems that that's not necessarily true. I remember to have copied that
logic from the following macro in indxpath.c:

#define IndexCollMatchesExprColl(idxcollation, exprcollation) \
((idxcollation) == InvalidOid || (idxcollation) == (exprcollation))

which was added by the following commit:

commit cb37c291060dd13b1a8ff61fceee09efcfbc34e1
Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Thu Sep 29 00:43:42 2011 -0400

Fix index matching for operators with mixed collatable/noncollatable inputs.

If an indexable operator for a non-collatable indexed datatype has a
collatable right-hand input type, any OpExpr for it will be marked with a
nonzero inputcollid (since having one collatable input is sufficient to
make that happen). However, an index on a non-collatable column certainly
doesn't have any collation. This caused us to fail to match such
operators to their indexes, because indxpath.c required an exact match of
index collation and clause collation. It seems correct to allow a match
when the index is collation-less regardless of the clause's inputcollid:
an operator with both noncollatable and collatable inputs could perhaps
depend on the collation of the collatable input, but it could hardly
expect the index for the noncollatable input to have that same collation.

[ ... ]

+ * If the index has a collation, the clause must have the same collation.
+ * For collation-less indexes, we assume it doesn't matter; this is
+ * necessary for cases like "hstore ? text", wherein hstore's operators
+ * don't care about collation but the clause will get marked with a
+ * collation anyway because of the text argument. (This logic is
+ * embodied in the macro IndexCollMatchesExprColl.)
+ *

Discussion leading to the above commit occurred here:

https://www.postgresql.org/message-id/flat/201109282050.p8SKoA4O084649%40wwwmaster.postgresql.org

It seems that we can think similarly for partitioning and the let the
partition pruning proceed with a clause even if the partition key is
non-collatable whereas the clause's other argument is collatable. Even
though it seems we don't yet allow the kind of partitioning that would
lead to such a situation.

Thanks,
Amit