Is this any different from when Picard waited several seconds in "First Contact" to give the order to fire on the Borg cube, while quite a number of Starfleet ships were obliterated while standing by? Yet people still adore Jean-luc Picard as a captain and as a human being.
Picard and Pike are both my favorite captains, mostly because in sharp contrast to most of the other captains who shoot first and ask questions later, they take time to think. Unfortunately the side effect is they can overthink things sometimes, which can and sometimes does cost lives. It is an obvious failing, but it is a human response, and one that is a realistic example of the thinking man as a commanding officer.
My point is, both the action men (Kirk, Sisko) and the thinking men (Pike, Picard) have their strengths and weaknesses. Because they are all human, and they all have potential for greatness even in the face of the mistakes they make.