On 7 October 2017 at 14:48, Andres Freund <and...@anarazel.de> wrote: > 3. JOIN Elimination > > There's been a lot of discussion and several patches. There's a bunch of > problems here, one being that there's cases (during trigger firing, > before the constraint checks) where foreign keys don't hold true, so we > can't quite generally make these optimization. Another concern is > whether the added plan time is actually worthwhile.
I looked over this and it seems there's some pretty low hanging fruit in there that we can add with just a handful of lines of new code. This is the case for LEFT JOINs with a DISTINCT clause. Normally we can only remove an unused LEFT JOINed relation if there are some unique properties that ensure that the join does not duplicate any outer row. We don't need to worry about this when there's a DISTINCT clause, as the DISTINCT would remove any duplicates anyway. If I'm not mistaken, we also don't need to bother looking at the actual distinct clause's exprs since we'll already know that nothing is in there regarding the relation being removed. The benefit to this could be two-fold, as 1) we don't need to join to the unused relation and 2) we don't need to remove any row duplication caused by the join. While someone might argue that this is not going to be that common a case to hit, if we consider how cheap this is to implement, it does seem to be worth doing a couple of NULL checks in the planner for it. The only slight downside I can see to this is that we're letting a few more cases through rel_supports_distinctness() which is also used for unique joins, and these proofs are not valid in those. However, it may not be worth the trouble doing anything about that as relations without unique indexes are pretty uncommon (at least in my world). Thoughts? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
remove_left_join_distinct.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers