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: David Rowley <david.rowley@2ndquadrant.com>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>, Jesper Pedersen <jesper.pedersen@redhat.com>, Amit Langote <amitlangote09@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Beena Emerson <memissemerson@gmail.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2018-03-05T05:52:58Z
Lists: pgsql-hackers 
On 2018/03/02 21:43, Robert Haas wrote:
> On Fri, Mar 2, 2018 at 1:22 AM, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> But I realized we don't need the coercion.  Earlier steps would have
>> determined that the clause from which the expression originated contains
>> an operator that is compatible with the partitioning operator family.  If
>> so, the type of the expression in question, even though different from the
>> partition key type, would be binary coercible with it.
> 
> That doesn't follow.  Knowing that two types are in the same operator
> family doesn't guarantee that the types are binary coercible. For
> example, int8 is not binary-coercible to int2.  Moreover, you'd better
> be pretty careful about trying to cast int8 to int2 because it might
> turn a query that would have returned no rows into one that fails
> outright; that's not OK.  Imagine that the user types:
> 
> SELECT * FROM partitioned_by_int2 WHERE a = 1000000000000;
> 
> I think what needs to happen with cross-type situations is that you
> look in the opfamily for a comparator that takes the types you want as
> input; if you can't find one, you have to give up on pruning.  If you
> do find one, then you use it.  For example in the above query, once
> you find btint28cmp, you can use that to compare the user-provided
> constant against the range bounds for the various partitions to see
> which one might contain it.  You'll end up selecting the partition
> with upper bound MAXVALUE if there is one, or no partition at all if
> every partition has a finite upper bound.  That's as well as we can do
> with current infrastructure, I think.

Hmm, yes.

So while the patch's previous approach to convert the query's constant
value to the desired type was wrong, this is wronger. :-(

I guess I'll need to change the patch such that the comparison function
used for comparing partition bounds with a query-specified constant will
change from the default one from the PartitionKey to the one that accepts
the latter.

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.