In a film like The Dark Knight, a completely psychopathic villain with seemingly no human or redeeming characteristics whatsoever works extremely well.
My humble take on all three of the Nolan Batman films is that they are an extended debate on the nature of human beings - with the citizens of Gotham acting as the constant proxy for "humanity" - and with Batman & other characters trying to make the case for the fact that at least
some people in Gotham are good and worth saving, and with all the villains making the case that all of Gotham is corruptible and irredeemably corrupted, and thus the whole city is not worth saving.
In essence, this drives the narrative of Ras al Ghul, The Joker, and Bane in the third film.
The (Heath Ledger) Joker is not
completely psychopathic - in fact, he makes a good point to Batman at various points. Psychopathic insanity "is a bit like gravity" - "all you need is a little shove". Take away the things that matter most to people, and you can fundamentally transform them. Even Gotham's greatest hero - which Batman acknowledges is greater than him and more needed by the people - Harvey Dent, the crusader against crime, can be "brought low" and turned into Two-Face given the proper "shove" -- not just acid burning half his face, BUT the death of Rachel.
Everything he does in this film is a riff on this theme, especially the situation with the two boats and the explosives. What's interesting, of course, is it's not just Batman who refutes Joker's belief that everyone is corruptible and irredeemable; it is a convict on one of the boats who tosses away the detonator, showing that even "hard core" criminals can be redeemed.
What I like about all the Nolan Batman films is not just the villains but that some of the heroes (Commisioner Gordon, "pre" Harvey Dent, Rachel, and "Robin" in film 3) are
not wearing capes, body armor, and masks.
I guess I would say what makes Joker a good villain is he represents an argument about human nature and its corruptibility that all three films try and refute. With difficulty, with the final case of course only finally being made in the third film.