Depends on the magnitude of what is being warned of.
"Warning, graphic gore"? Absolutely appreciated. "Contains scenes of actual combat, those with PTSD may wish to leave the room"? Yeah totally reasonable. "This book contains vivid descriptions of sexual abuse"? I can see why people would be squicked out by that.
But then we get into the absurd side of it. A film about the Holocaust, needing to warn its viewers that some contents may be distressing? Wow. You don't say. A memoir about a tragic death, needing to put a warning that... someone dies? "This politics discussion may discuss slavery, racism, and oppression"? Oh no, we have to think about upsetting things that happened!
And before someone suggests those are unrealistic hyperbole, those are all things I've seen. I don't feel those are helpful.
Many of your examples of "bad" moderation are more about site administration (including use of tech tools and appeals) than the degree of moderation. Like, yes - Reddit's moderation ecosystem, particularly in large subreddits, is fundamentally broken. Powermods, lack of accountability, malfunctioning digital filters, mods who lack of options for alternatives (or, where those alternatives exist, they are frequently overwhelmingly cesspools)... it's got issues. But this isn't about "more" or "less" moderation; it's about poorly-applied controls in the first place.
I'm not so sure Lemmy is so "perfect" either. I've seen plenty of moderation based on political views rather than actual misbehavior here, and conversely plenty of actual hatred and bigotry getting a pass because those in charge of a give space viewed it as aimed at the "correct" people. Likewise, while the Fediverse allegedly lets parallel communities develop, in reality it can be hard to overcome the inertia of people moving towards a popular community, unless the mods/staff there really screw up.
Okay, so what's the actual right amount in a given community?
My admittedly cop-out answer is "That depends on the community". There were some where extremely rigidly-enforced rules - particularly about quality or contents of answers or posts - helped to ensure communities retained a high degree of quality and reliability in what was posted. But others might want a more casual, relaxed space to goof around in - including in ways that others might not like - which require looser rules.
And that's really the rub: There's no absolute right answer. We can point to lots of wrong answers, but getting it right is a complex journey for each space. My personal focus is that whatever level is agreed on, it must be fairly applied for all users. You cannot be passing one user's slipup and coming down hard another. Be fair.
...and in the end, there will be people who simply cannot follow the rules, no matter how clearly they are explained.