Re: Partition-wise join for join between (declaratively) partitioned tables

On Tue, Sep 19, 2017 at 3:17 PM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
>>
>>>> - I'm not entirely sure whether maintaining partexprs and
>>>> nullable_partexprs is the right design.  If I understand correctly,
>>>> whether or not a partexpr is nullable is really a per-RTI property,
>>>> not a per-expression property.  You could consider something like
>>>> "Relids nullable_rels".
>>>
>>> That's true. However in order to decide whether an expression falls on
>>> nullable side of a join, we will need to call pull_varnos() on it and
>>> check the output against nullable_rels. Separating the expressions
>>> themselves avoids that step.
>>
>> Good point.  Also, I'm not sure about cases like this:
>>
>> SELECT * FROM (SELECT b.x, b.y FROM a LEFT JOIN b ON a.x = b.x WHERE
>> a.y = b.y) w LEFT JOIN c ON w.x = c.x AND w.y = c.y;
>>
>> Suppose the relations are all partitioned by (x, y) but that the =
>> operator is not strict.  A partition-wise join is valid between a and
>> b, but we can't regard w as partitioned any more, because w.x might
>> contain nulls in partitions where the partitioning scheme wouldn't
>> allow them.  On the other hand, if the subquery were to select a.x,
>> a.y then clearly it would be fine: there would be no possibility of a
>> NULL having been substituted for a proper value.
>>
>> What if the subquery selected a.x, b.y?  Initially, I thought that
>> would be OK too, because of the fact that the a.y = b.y clause is in
>> the WHERE clause rather than the join condition.  But on further
>> thought I think that probably doesn't work, because with = being a
>> non-strict operator there's no guarantee that it would remove any
>> nulls introduced by the left join.  Of course, if the subselect had a
>> WHERE clause saying that b.x/b.y IS NOT NULL then having the SELECT
>> list mention those columns would be fine.
>>
>

In my previous reply to this, I probably didn't answer your question
while I explained the restriction on where equality conditions on
partition keys can appear. Here's answer to your questions assuming
those restrictions don't exist. Actually in the example you have
given, optimizer flattens w as a LJ b which kind of makes the
explanations below a bit complicated.

1. SELECT * FROM (SELECT b.x, b.y FROM a LEFT JOIN b ON a.x = b.x
WHERE a.y = b.y) w LEFT JOIN c ON w.x = c.x AND w.y = c.y;
partition-wise join will be possible between a and b but not between w
and c for the reasons you have explained above.
2. SELECT * FROM (SELECT a.x, a.y FROM a LEFT JOIN b ON a.x = b.x
WHERE a.y = b.y) w LEFT JOIN c ON w.x = c.x AND w.y = c.y;
partition-wise join will be possible between a and b and also between
w and c for the reasons you have explained above.
3. SELECT * FROM (SELECT a.x, b.y FROM a LEFT JOIN b ON a.x = b.x
WHERE a.y = b.y) w LEFT JOIN c ON w.x = c.x AND w.y = c.y;
partition-wise join will be possible between a and b but not w and c
as you have explained.

In this case b.x and b.y will appear as nullable_partexprs in w
(represented as a LJ b in optimizer) and a.x and a.y will appear in
partexprs. Depending upon what gets projected out of w, the join
between w and c will use corresponding keys for equality conditions.
Since the operator is non-strict, any expression which is part of
nullable_partexprs will be discarded in
match_expr_to_partition_keys().

Hope that helps.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company