You can spend a week crafting the most optimal query possible, making sure the plans are perfect and all the best index types are used

And then you push it to production and get completely different results

Statistics change everything. Queries which produced beautiful plans in local and staging environments breakdown when given production statistics, some users get the same plans you did, others get nasty nested loops, indexes don't get used, and debugging and fixing it requires poking around in prod statistics which is never a good place to be. Don't even get me started on how RLS impacts query optimisation

Postgres is great but the authors refusal to add plan hints is a decision that comes to bite me at least a few times a year