This honor definitely goes to Trail of Cthulhu, even though it isn’t terribly “horrific” in the sense of “you are up against something that will destroy you, no matter what.” In Trail, there’s a decent chance you can “win” against the eldritch nightmare. Nonetheless, I like to run it– I have the Ritual Pursuits demo down to a neat 90-minute session that is very satisfying, although I kind of want to streamline the pre-generated character sheets (remove anything that isn’t strictly needed for the demo).
I ran it this weekend at a convention, introducing 4 new players to Trail and Gumshoe, and feel pretty good about that.
If you don’t mind spoilers, here’s a video from when I ran this demo a few months ago at the Contessa online convention: