See, when a query is big and complicated to you, there’s a pretty good chance you’re gonna get a big and complicated query plan, because it’s big and complicated to the optimizer, too.
This isn’t to say the optimizer is dumb or bad or ugly; it’s just that there’s only so long it’s willing to spend coming up with a plan.
Remember, cheap plan fast. Not perfect, not great, maybe good enough.
It’s a good operating philosophy: if you have a query which has gone off the rails, one of the best things you can do is try to turn the query into several small steps. It’s possible to reduce complexity that way…though you may also gain complexity in the process if you do it wrong.