The discussion got a bit heated I see, but since Dayy pinged me yesterday (was drunk) I'll chime in for my 2c.
Silva your concept of an "horror" game is seriously limited buddy, I cannot properly vouch for the last edition of Delta Green, but methinks you're confusing a couple example scenarios written in the core game as all there is to it. First of all, let me make a distinction here. You started a thread about "horror" in gaming but what you're discussing here is "terror" in games. Horror is nothing more than an evocative and fear-inducing image, it's more about being apt at descriptions and you can definitely use it in any kind of pnp system.
Eg take a Troll in Pathfinder (CR 5), apply some demetrial customization package on him ("starving" is perfect for this), then when you have the chance to play the encounter, instead of telling your player "with a bone chilling roar a green humanoid, 7 feet tall, charges from the wood", have them find a lonley cabin with clear sign of devastation on their route, go in detail about the smell of blood and bowels mixed to cinders and a curious mulching sound they can hear as they approach. As they turn the corner and enter what you know is just a combat with a sub-par troll, you hit them with the full gore-y description of the troll consuming the previoius occupants. At this point the Troll stands up and proceeds in beating the living shit out of your low level PCs using one of the previous victims body as a maul.
Let's dissect this a bit. First of all, you want to be fast while describing all that.
Less words
more details is better when it comes to horror imho. Second, what did I just do? I took a CR 5 encounter, gave him some penalties (CR--->4) then gave him a sub-par weapon (Fleshy meatclub -1 bab -2 Dam, CR--->3). I basically
nerfedi it into a level 2-3 encounter. Do the players know that? Nope. Will they shit their pants
mechanically speaking when you hit them with what they think is a CR 5 encounter at level 2? Yes, probably. Some will also whine and call you a bad GM for not using Challenge Rating properly but why should you care? You know you did your homework when designing the encounter and, in the end, all will be fun and games.
So what I did was simply take a couple of details from this particular enemy (starving, armed with a sub-par weapon) and turned those into gory detail thay I hope will scare the player into doing something stupid. Not that you need to
scare players into that, usually...
I hope the example suffices at explaining how "horror" is just a trapping you can add on any scene or detail of your adventure and/or campaigns. Even proper sword and sorcery epics can have great horror moment, if the GM wishes and he manages to pull it off.
Now, let's talk about the topic you have been really discussing all along, Terror. While horror is an image and usually a very specific one you try to convey, terror is the unknown, it's your mind filling in the blanks and a serie of specific tropes and ancestral fears you try to awake inside your audience. Horror is an image you
give them while terror has been inside them all along, you just help it surface for a while. Horror is a bloody hand suddenly reaching for you from a stained curtain, terror is the weird sound you hear during exploration, a strange smell that becomes stronger as you approach or the sudden realization that something you have been inspecting hasn't been built for human proportion and anathomy. While horror is a trope you can use to spice up any kind of story, terror relies on mistery and partial knoweledge, as I said, the mind filling in the blanks is an important part of it all. This means you need to plan for it beforehand and tha you can't half-ass it. I mean, you can stumble forward in many kind of adventures if you make a mistake and the players find a plot hole or a clever way to bypass most of your adventure, not so easy with a mistery game, for those your preparation need to be top notch.
I feel many of your statements come from a lack of understanding, Silva. First of all let's discuss this:
Most supernatural creatures are immune or heavy resistant to bullets both in CoC and Delta Green. Thus, forcing the players to find their weakness (= pixel hunting for the info as planned by the GM). There's even a recurrent joke in the DG community that says something like "just give every gun your players ask, those will be useless against the monsters anyway!".
This is blatantly false. Even in base Coc, the deadliest of the three system I will consider (CoC, Pulp Cthulhu and Delta Green), I'd say of all the supernatural creatures published, about 10-15% are immune or heavily resistant to conventional firearms. Sure, you have your Dark Youngs, Starspawns and Shoggoths and those clearly stand out as "impossible" fights but so is trying to fight a Dragon in D&D when you're underlevelled (it also requires rare magic weapons to be hit in the first place, notice a pattern?). The majority of the statted monsters are your much more manageable ghouls, mi-go, Serpent People, Zombies. You have medium-sized threats like Gnoph-keh or formless spawn that take a whole group focusing fire on them or "unconventional" monsters like Cthonians which can't be killed by mundane weapons, true, but can be incapacitated or harmed via mundane means (drop a crane on it as it surfaces, the adventure relies upon the players setting up a plan for that "one hit wonder" of an attack, very fun, I used this multiple times and in different situations/games).
Pulp Cthulhu and base CoC also give you magic, bretty powerful if used correctly. One of my favourites to give players options in base CoC is exactly to provide both mundane (if rare) and magical means of defeating a strong foe during an adventure and then have the players live with the consequences in both cases (but this is a CoC thing, not necessarily true about all horror games or any and each CoC adventure). In addition to that, Pulp Cthulhu gives you Luck-based powers, Pulp Talents and Mad Talents to go above and beyond and perform feats no Lovecraftian protagonist ever dreamed about!!1! Lots of player choices and mechanics there, many ways to harm the baddies, although if you just want to go in guns blazing, there are better systems for that (Shadowrun can be used for very effective lovecraftian bulletstorms settings and GURPS is
alway gud, if you take the time to learn the system).
As for Delta Green in specific, that relies on a weird mix of real life gun-porn and sci-fi. No, you're not supposed to pixel hunt, although you're right in that you're at the mercy of your GM, you always are when playing pnp. Playing with a bad GM is akin to playing with a board games missing key pieces or a very, very buggy cRPG. As I said before, guns are gud against 90%+ of your normal encounters. For very specific monsters, afaik you're supposed to rely on mcguffins that (and this is a very important detail)
mechanically behave like guns in-game. To give you a practical example, a base CoC adventure could end with your chars desperately trying to reach the summit of a mountain to read a tattered scroll and stop the summononing of Ithaqua while deranged cultists rain from all directions, whike in Delta Green you're supposed to arm two players, one with a special "crystalization spray" (flamethower) and another with an high-tech laser rifle (sniper rifle) to freeze a shoggot in place and cut it to pieces while other specialists in your group do "their" things (fire at lesser threats, place explosives to shred the place, crack the computer the Shoggoth is guarding etc).
I'd love to write more but life is a bitch and I have job(s) to do.