Re: [HACKERS] path toward faster partition pruning

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company