Horton

Re: [HACKERS] path toward faster partition pruning

Amit Langote <langote_amit_f8@lab.ntt.co.jp>

View thread View on postgr.es
From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>, Jesper Pedersen <jesper.pedersen@redhat.com>, David Rowley <david.rowley@2ndquadrant.com>, Amit Langote <amitlangote09@gmail.com>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, Dilip Kumar <dilipbalaut@gmail.com>, Beena Emerson <memissemerson@gmail.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2018-02-08T07:55:33Z
Lists: pgsql-hackers 
On 2018/02/07 1:36, Robert Haas wrote:
> On Tue, Feb 6, 2018 at 4:55 AM, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.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@sss.pgh.pa.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

Commits

  1. Fix assorted partition pruning bugs

  2. Make gen_partprune_steps static

  3. Remove useless 'default' clause

  4. Reorganize partitioning code

  5. Use custom hash opclass for hash partition pruning

  6. Blindly attempt to fix sepgsql tests broken due to 9fdb675fc5.

  7. Attempt to fix endianess issues in new hash partition test.

  8. Faster partition pruning

  9. For partitionwise join, match on partcollation, not parttypcoll.

  10. Revise API for partition bound search functions.

  11. Revise API for partition_rbound_cmp/partition_rbound_datum_cmp.

  12. Fix possible crash in partition-wise join.

  13. Refactor code for partition bound searching

  14. New C function: bms_add_range

  15. Add extensive tests for partition pruning.

  16. Add null test to partition constraint for default range partitions.

  17. Remove BufFile's isTemp flag.

  18. Make OWNER TO subcommand mention consistent

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