I would like to extend your taxonomy before I answer ...
Firstly, you have the super-optimised. A narrow focus, like the grog with +20 attack and no social skills, survival skills, surveillance skill or anything else which could be useful. Or the magus with a lab total of 120 for longevity potions, and no other interests whatsoever. We all know these characters from our juvenile games I think, but they tend to get boring and most of us grow out of them.
Then you have the complete optimised characters with a broad enough skill set to fill a role. the shield grog who also has the awareness to serve as a guard, or the social skills to book a room at the inn for the magus. These characters are optimised for a role, which is sometimes complex, rather than for a single task. IMO these characters also tend to be boring, especially as hot-played grogs.
What you describe as counteroptimised is at least two different things. You have the antioptimised, like your Inspector Gadget, or maybe Ford Fairlane, possibly with a delusion flaw to make him pursue a role at which s/he is bad. These characters are designed for failure, and I do not think they are ever going to be fun unless the SG designs stories accordingly, at least not beyond the first complete failure.
The other variant you seem to suggest is optimised with a twitch. Your Holy character with weakness, may well be optimised, or even super-optimised. The weakness is something to roleplay for greater fun, but it is unlikely to affect the character at a moment of critical skill tests. The soft-hearted shield grog that I made for Fight and Flight never was very popular I think ... Furthermore, characters may be optimised with a bonus, say the tough guy who excells at storytelling as a side skill. Something which only rarely has a mechanical impact, but which adds flavour. Whether there is a negative twitch or a positive bonus, these characters allow more axes of roleplay, and are therefore more fun.
Your unoptimised character in its extreme means that the character is not designed for its role in the story. It would break all good principles of storytelling. I would prefer to call that randomised, but there is also suboptimised, a character tailored to its role, but deliberately not to perfection. Some traits may well be randomised, as long as enough are selected for its role.
Of course we know the super-optimised characters, but I don't see them much in Ars Magica, probably because players either do not have enough experience to fully exploit such a complex system, or they have enough experience to be bored of them. More complete, optimised characters are more common, but I think mainly for template grogs which are best forgotten after the game.
No doubt it is the many variations over optimised characters with extra flavour that we remember, and obviously all sound storytelling principles tell us why. (1) The character has to fill its role to move the story forward. (2) It has to have enpough flavour to add some surprise elements. (3) It has to avoid more traits and quirks than can be remembered by the player and displayed in the story, so that the behaviour be recognisable.
It is of course possible to make a character super-optimised with a quirk, essentially deciding to play the character actively towards multiple tasks or roles, where the character is super-optimised for some and anti-optimised for others. This, is not uncommon. Some players are really good at bringing such characters alive.
Even anti-optimised characters should have a quirk to be remembered by. Maybe a side-skill with which s/he excels. No, I have not seen such a character played, certainly not in Ars Magica.
NPCs should normally be suboptimised, especially antagonists. I'd rather make them older than the PCs and less optimised, so that they appear as a threat but still can be overcome. This is logical in that the PCs should be the exceptional characters in the world, those who become heroes.
In my experience, I'd say that PCs, designed to be actually played actively in stories, tend to split 50-50 between variations optimised with twists and straight-forward optimised, usually complete. But there is no doubt which work best in the story.