I wrote: > Anyway, what I'm inclined to do is flesh out the attached by updating > the comments for remove_useless_results() and then push it. Later > on we can look for why it's not finding the better join order; that's > a separable issue, and if it is about avoid-clauseless-joins then we > might choose to live with it rather than incur a lot of planner cost > to fix it.
I couldn't resist poking into that, and it seems there's less here than meets the eye. I'd been guessing that the presence or absence of a t2 reference in the WHERE clause was affecting this, but no: the SpecialJoinInfos look exactly the same for both queries, and the set of joins considered is the same in both. What is causing the different plan shape is that the selectivity estimates for these WHERE clauses are a lot different:
I was also wondering why the plan changes for this existing query after applying this fix. After some investigation I came to the same conclusion: it's all about different size estimates.
For this query, we'd form joinrel that includes {t1, t2, t3}, with or without the fix. However, without the fix we form the joinrel with {t1/t2} and {t3}, and thus treat 't2.f1 > 0' and 't3.f1 IS NULL' as restrict clauses, and with that we calculate the joinrel size as 1. After applying this fix, the t1/t2 join is not legal anymore (which I think is right), and we form joinrel {t1/t2/t3} with {t1} and {t2/t3}. This time we treat 't2.f1 = t1.f1' as restrict clause and calculate the joinrel size as 5. I manually changed its size to 1 with gdb and then the final plan changed back to the previous one, ie, the one that joins t4 last, with exactly the same cost as previously.
