In contrast to the problem you've stated, if I were to exclusively use my Google account to log into websites, it becomes a single point of failure if the service was down, and if it were to be compromised.
Very true. Sadly there's no real right or wrong answer here; a single point of failure but a better secured portal, or a decentralised network with arguably less secured portals.
Personally I try to use a balance of both: Twitter passports for sites I don't trust and passwords for sites I do trust. But that's just my personal preference.
This is exactly right. And, as you mentioned above, there are more kinds of people out there than are present in this thread.
I have a password manager and generate a new random password per site, so I don't have any desire to use a single log-in for almost all sites. However, many (most?) people reuse a single password (or a handful of them), and until that changes, they're likely much better protected by having a single well-protected authentication point.