Really interesting question. I wonder this a lot on all the 'he doesn't pull his weight and I'm at the end of my tether' threads.
Fundamentally, I don't think we are socialised to find men that we don't respect sexually attractive.
Useless men only tend to come to the fore long after emotional connections are made, and even well after formal commitments are made. In other words ... a couple meet, have a connection, are loved-up, get together, things progress, they get married/move in/have a baby (in whichever order), and it's only then that male uselessness starts to become apparent.
The man is enconsed into the family home and it's then that he starts to opt out of the shitwork. The baby arrives, and it's only then that the women defaults to taking the lion's share.
By which time, the bond between man and woman has been formed and the man's uselessness doesn't have a direct impact on how sexually attractive the woman finds him, because that side of things was already decided back when he was a more appealing figure, back before the shitwork and parenting was in any way a part of their relationship.
And then, as his uselessness starts to become more apparent, starts to impact on daily life, and as the woman starts to feel more like a domestic appliance there to service her man, the sexual attraction starts to erode.
Fundamentally, I don't think we do find people we don't respect/like sexually attractive. But the shift is often so gradual, that you can't pinpoint to any one thing.
How many relationships are there where the sexual side of things in LTRs has dwindled and dwindled to pretty much non-existent? Which partner is it who tends to be the one to opt out of this side if things?
Men don't fancy housework/wifework/shitwork/whatever you want to call it; women don't fancy the men, because after all, what is sexually alluring about someone who sees you as a skivvy? But this takes a long time to evolve.
Not sure if I've made any sense.