Mastering PostgreSQL 9.6

Processing outer joins

After verifying that your queries are actually correct from a business point of view, it makes sense to check, what the optimizer can do to speedup your outer joins. The most important thing is that PostgreSQL can in many cases reorder inner joins to speedup things dramatically. However, in the case of outer joins, this is not always possible. Only a handful Pac of reordering operations are actually allowed:

(A leftjoin B on (Pab)) innerjoin C on (Pac) = (A innerjoin C on (Pac)) leftjoin B on (Pab)

Pac is a predicate referencing A and C, and so on (in this case, clearly Pac cannot reference B, or the transformation is nonsensical):

(A leftjoin B on (Pab)) leftjoin C on (Pac) = (A leftjoin C on (Pac)) leftjoin B on (Pab) ...