alt Hacker NewsYou 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
Mostly agree although you should probably spend a bit more time testing on a more realistic setup.
At least run some plans on the real database with real sized data and real statistics.
Similar to how mocks/stubs are great until they aren't.
That won't magic away statistics problems, but it's still good practice to acknowledge the lower app environments tend to not reflect performance of the production one.