The example query provided here seems rather artificial. Surely few people write a join clause that references neither of the tables being joined. Is there a more realistic case where this makes a big difference?
Yes the given example query is not that convincing. I tried a query with plans as below (after some GUC setting) which might be more realistic in real world.