One good example is joining to a foreign data wrapper (FDW). Oftentimes, PostgreSQL can freak out about this because the statistics might be off/vague. So instead of making a small query to the FDW, it will do a full table scan :S
A subquery explicitly tells the database "hey, don't get the whole FDW, just this part that I want"
A subquery explicitly tells the database "hey, don't get the whole FDW, just this part that I want"