Yeah, composite video really sucks. It has a habit of creating a blurred, washed out mess. Colours are also misinterpreted as you pointed out. Compare the red vertical stripes in the shop window in the top/left of the image too. In RGB each line is clearly defined, in composite they all blur together.
This is what happens when you cram a video signal into a single cable. Why do you think all arcade machines, even the earliest ones, use an RGB video signal. Composite is only a minor step up from RF. Aside from the convenience of not having to tune in a channel and stereo sound, it (composite) has little benefit. Naturally, RF sucks ass as the video signal is crammed into an even worse cable which is prone to interference, you need to tune it in and to make matters worse, the same cable shares a (crappy mono) audio signal too.
To be fair though, some consoles output composite better than others. For example, the Super Nintendo actually has quite decent composite output whereas the Mega Drive has possibly the worst composite output. Interestingly, the white Japanese PC Engine, which only has RF out, still manages to output a really clean video signal. It's the only console that I know of that has great RF out.
I wouldn't recommend retro gaming on anything less than an S-Video cable. Component or RGB are the way to go though, where possible. You'd be surprised at how many retro consoles natively output an RGB signal. All you need is the right cable and a TV with an RGB scart socket. Off the top of my head, some consoles that output an RGB signal without modification are: Sega Master System, Sega Mega Drive, Sega Saturn, Sega Dreamcast, Super Nintendo, Neo Geo AES, Sony Playstation etc. For those that don't, you can either mod them to do so or in some cases an S-Video cable will exist, such is the case with some N64's.
Another option is video encoders but that's another lengthy discussion and I've probably already bored everyone here with this post.
