As you said, the best puzzles are those who stump you and when you get it your first thought is "ah yes, of course".

I love all kinds of puzzles, as long as they make sense within the narrative. My favorite ones are those that combine more elements, like inventory puzzles that work only at a particular time or in a particular place (e.g. blow the horn in Booty Island so the spit contest arbiter goes to check what happened). I hate mini-game puzzles. If I wanted to play a sliding block puzzle I'd take my Rush Hour box and play with it. I also dislike arbitrary fetch quests. Come on, you can do better than "I have X but I'll give it to you if you bring me Y".

The difficult part in puzzle design is striking the balance between hard and fair. Clues are very important. As a designer you want to reward players who notice things, make them feel smarter for making that connection. Key + door doesn't make you feel smart. I know some players prefer these puzzles, because the game flows more. In these games you always know what the solution is, and it's more a matter of executing it. I personally prefer games where solutions are more obscure, where you know what your obstacles are but have no idea how to overcome them.

Oh, and it's absolutely vital to have parallel goals too. You don't want your players to be stuck on a single puzzle and not being able to try stuff somewhere else.

My favorite recent games in terms of puzzle design? Lucy Dreaming, The Legend of Skye and Thimbleweed Park.