And that's why I listed a bunch of other options as well - simplifying code is probably the best one, but there will always be gaps in their heuristics.
In my experience, you have one of three scenarios:
You know ahead of time exactly the problem you are going to solve and so probably shouldnt choose SQL from a performance perspective.
Your problem size changes and you get to pick a simple system that you understand well and has predictable performance but might be slower than other options.
Or a complex system that's less well understood, and generally has great performance and productivity up to a tipping point, where the abstraction has leaked too much.
In your case - your query sounds exactly like those heuristics worked up to a point, and then finally failed. Is that the optimizers "fault"? It's mostly a trade off between the time spent making the plan and the time spent getting your data back - and again, sometimes those tradeoffs are wrong too.
In my experience, you have one of three scenarios:
You know ahead of time exactly the problem you are going to solve and so probably shouldnt choose SQL from a performance perspective.
Your problem size changes and you get to pick a simple system that you understand well and has predictable performance but might be slower than other options.
Or a complex system that's less well understood, and generally has great performance and productivity up to a tipping point, where the abstraction has leaked too much.
In your case - your query sounds exactly like those heuristics worked up to a point, and then finally failed. Is that the optimizers "fault"? It's mostly a trade off between the time spent making the plan and the time spent getting your data back - and again, sometimes those tradeoffs are wrong too.