I'd have to say that the right trap in the right place has proven a more effective PC retirer than anything, in my experience. I've played since the early '80s, and nothing scared us more than an upper level Gygax-written module. Though I just play 3.5 now, I still read through that old stuff for ideas. The man was truly devious.
Of course, part of the key was in making the trap both seductive and destructive. The traps in Tomb Of Horrors, for example, were largely designed to kill munchkins. Other modules had traps designed to be over-thought, where escape was ridiculously simple in reality, but the skittish PCs were likely to believe it just COULDN'T be that easy.
So in summation, right trap, right place, right time.
- Rob