What do you mean exactly by "horror show"?
The short version is that the supreme god, Ao, elevates extremely flawed mortals into gods then demands that people worship these ascended mortals on pain of eternal torment. Note: as written this includes every child who dies before the age of reason.
So the true god of the setting demands you worship lesser false gods (they only have power because Ao wills it) and even the supposedly good-aligned gods are okay with throwing children and those who refuse to worship ascended mortals as gods into a wall of eternal torment.
Throw in on top of that is that, best case, if you do go to your ascended human’s divine realm your soul and identity lasts maybe a hundred years before your essence dissolves into the realm (so your essence becomes a tree… or a brick in the god’s new palace wing) or, if you’re really “lucky” gets used to make a new celestial servant (which does not carry your memories or identity… it’s just been assembled out of your spirit-stuff).
Throw in on top of that that the wicked; murderers, rapists, etc.; just need to pick the right ascended mortal as their patron god and they get the same rewards as the virtuous who pick the ascended mortal with the portfolio of mercy and compassion. So do whatever the frak you want because there’s a patron for it who will reward you in the afterlife for doing it (until you end up as a lawn chair just like the virtuous man).
And yet this setting is supposed to run on the same basic echoes of Christian morality that the West has been running on for the last hundred years (though we’re finally seeing the Left embrace the logical end points of abandoning it… i.e. if there is no God, then might makes right… instead of pretending that Western morality is somehow intrinsic to humanity).
So it’s not only a cosmic horror show, it’s an illogical cosmic horror show.
So, basically, your default choices are eternal torment in the Wall of the Faithless or complete annihilation so your essence can be used as raw material for some ascended mortal’s whims. If you are someone who seeks truth and refuses to worship another flawed and limited being as your deity, you are punished for it.
It is thus a setting where becoming undead and converting as many willing mortals as possible into undead to evade either option is actually one of the more ethical paths and escaping via Spelljammer with every family member and friend they can convince to a more sane universe ASAP (stear clear of Eberron, it’s almost as bad, but another topic) is what any sane being who has studied the Realms’ metaphysics should be doing at the earliest opportunity.
Now it should be stressed that it’s likely none of this was intentional.* The whole thing proceeded in stages and it’s basically the unfortunate implications of a system of metaphysics set up piecemeal by multiple writers with basically no understanding of actual metaphysics and only a surface-level understanding of religion… but nevertheless for those who do have a deeper grounding in those things; the Forgotten Realms are basically a Hell Dimension.
At least that’s my take on it.
* Eberron’s while not piecemeal is basically the result of the same type of failures… specifically, in trying to devise an afterlife based pre-Christian understandings they didn’t look beyond the grey mists of Hades and so didn’t bother to include the Elysian Fields or Tartarus in the mix… everyone just ends up a disembodied spirit wandering without memories through an endless grey dimension… so again, zero moral structure to encourage goodness among mortals instead of indulging your every whim while you live (and every horror you commit to extend your life rewarding you with no metaphysical downside for your eternal soul) other than the generally proved false by human history belief that most people are intrinsically good (some surely are, but again… the logical end point of any moral system without just rewards the righteous and punishme for the wicked is going to end up with the majority following an ethos of “might makes right/whatever I can get away with”).