Compared to most other mammals, and especially to dogs, humans have a really bad sense of smell. Since it's such a crude and undiscerning tool, it focuses on the broadest strokes; in particular, virtually everything we can smell is a clue about whether something is good or bad. "Good" refers to only a few things: "I can eat this", or "I can reproduce with him/her", or "This is safe". But there's a lot more bad things in the world, ranging from food you can't eat, to places and situations that can spread disease or illness, to predators and places they're more likely to find you, to natural dangers like fire or inclement weather. So it's no surprise that a lot more smells are bad ones than good ones.
Since our sense of smell tells us "this is good" or "this is bad" in such a simplistic, crude way, we tend to immediately associate the smell with a desire to avoid or embrace something. Thus, our reaction to unpleasant smells like sewage is to try to get to where we're not smelling it anymore, period.
Dogs have a sense of smell that's a thousand times more powerful than ours, both in discernment and distance. It's hard to overestimate how much better their sense of smell is than ours; the challenge is to imagine what it could be like to smell like they do since their facilities are not just like ours but better, but several orders of magnitude better, enough that it's a difference of kind, not just of quantity. And yet dogs don't recoil from bad smells. If you imagine how you react to the smell of feces and multiply it by a thousand, you'd wonder why dogs don't flee all the time, but they embrace it. In fact, dogs often follow up awful smells by tasting, which grosses people out.
Does this make sense? It turns out it does. A rough analogy can be made if you consider our sense of sight, which is far better than a dog's sense of sight (not a thousand times better, but still better). Sight is so refined for us that very few sights are immediately telling us nothing more than "this is good" or "this is bad". Maybe a completely dark area could be seen as triggering "this is bad" the same way sewage smells do, but generally, we don't react to sights the way we do to smells. Even the sight of something that suggests danger is something we take in, in all its details, and parse into its component parts, so we can understand what's going on. Sight is refined enough that we can expect it to tell us far more than "this is bad: avoid it", it also tells us what exactly is going on, so we can figure out how best to deal with it. Even the most horrifying sights are as likely to compel us to look as to look away or close our eyes. And very few sights are so terrible that they are directly terrible the way a smell is; they're only terrible when you come to understand what you're seeing and then react to the implications, whereas the smell of sewage is immediately awful long before your mind has worked out that it's sewage (or even if it never does figure that out).
So when a dog smells poop, he's getting a thousand times more information than our simple "poop is yucky" reaction. Poop tells him who pooped, and what their health state was, and where they might have gone, and whether they might be a danger or a food source or a potential mate, and a lot more we can't even imagine. The dog's reaction is not to recoil from its ickiness but to investigate farther. If it's a bad thing, the dog needs to understand it, and smell (and taste) is the best way to do it. And if it's an opportunity, the same thing is true.