Re: [HACKERS] path toward faster partition pruning

On 2018/03/02 21:43, Robert Haas wrote:
> On Fri, Mar 2, 2018 at 1:22 AM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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