It's only ever enforced against a handful of the biggest players, driving traffic to smaller sites. Those smaller sites might also not even be "porn", but include things like Tumblr and Reddit.
A lot of those smaller sites do not enforce standards as well as they need to. This is particularly true regarding consent, extortion, prostitution, etc. They also suggest it applies to CSAM. So instead of getting some (relatively) vanilla porn, they are exposed to all sorts of horrors.

Other people have already mentioned the obvious privacy concerns, and how it could be used for blackmail, trust in 3rd parties, etc. But this covered why it fails even at its core objective.