the key principle to query optimization is understanding your data, considerate indexing, and keeping your statistics up to date. not avoiding slow JOINs and do

the key principle to query optimization is understanding your data, considerate indexing, and keeping your statistics up to date. not avoiding slow JOINs and doing denormalization. super, super, super bright people who have been working on this stuff for longer than some devs have been alive write the query planner logic. the query planner relies on good statistics to run optimally. indexes on stuff you might care to query about help it even more. but truly understanding your data helps you reason and think like the planner might. how many rows will this subquery return on average? how many will the planner think there is? if these two answers are really off, then planner might choose a bad plan. so maybe you can rewrite your query a little bit - rethink an approach to getting the same result, but in a way that the planner will choose a better plan. maybe use an EXIST vs an IN. anecdotally, I've seen some devs struggle with queries and data because they think that one approach will always work, because they think that it's always the algorithm that matters. but when it comes to data - it's the data that matters.