First, allow me to patronise you with this lecture. I was asking myself a similar question recently and came up with this answer to myself. Your answer is In the last paragraph.
If you want to write a compelling story do not have your main character make mistakes without good reason. Just because 'it's realistic' is not a good reason. Arbitrary events are worthless in story currency. What's the moral of such a tale? 'Don't make mistakes'? That's a little unhelpful!
Most of the characters can be as dumb or random as you like, but the bond between your MC and your reader is very strong, almost psychic. In a way, your MC is your reader. If you have told your reader there is danger ahead, your MC's instinct should tell him the same thing. If it doesn't, your reader may get confused, or frustrated, or simply scornful of the strange or dumb ways your MC is behaving, and they may just take it out on your writing. You as the writer are held responsible for everything in the story. You won't be around to explain that random slip-ups are 'realistic'.
If you want your MC to make mistakes he should do it because he is visibly at fault. He's ignoring instinct, advice or clues because he's struggling with character flaws, e.g. pride, stubbornness, prejudices. His refusal or inability to do the right thing is the heart of your story.
You should look at the mistakes you want him to make, and find his reasons for doing those things. They should define his personal journey, whether it ultimately ends in success or failure.
Lecture over. You knew all of that. What you're looking for is a way around it or a way to subvert it. Try this...
Surprise your readers with a twist: reveal that detective is not the main character of the story. Somebody else is. Somebody else, whose personal story was actually deeper and more compelling (maybe his partner, maybe the narrator?) steps into the limelight to finish off the overall story, and his/her own personal story. Perhaps in the way Dr. Watson would have to step in to save an incompetent Sherlock Holmes. This gives you a framework to hang up the detective's story and poke holes in his judgement or lament his terrible bad luck, without the reader feeling they had been robbed of a satisfying and logical conclusion.