Postgres has done an incredible job optimizing both the overall performance of queries and the query planner - but these things make mistakes still and being able to fix those mistakes can make the difference between a two minute query and a twenty milisecond query - this comes with the fact that complex database operations can usually make or break overall response times, and these optimizations usually depend on statistic accuracy which can be hard to ensure.

If SQL optimization was as good as compilers I'd be all on this train, but I just don't think we're there yet.