On Tue, Sep 19, 2017 at 3:17 PM, Ashutosh Bapat <ashutosh.ba...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
