I finally dug up my trusty old Commodore 1084 monitor and tested out the RCA video. Movies look good, but using it for text/GUI will give you a migraine headache as the video is interlaced and flickers like hell.
What I'm looking for is a way to switch the video to progressive. Using a 640x200 resolution, one could display 80 column video in a decent quality, like on the Amiga. I tried creating a modeline in /etc/fb.modes and using fbset, but it seems that all commands to change the video mode are ignored by the RPi hardware.
When issuing tvservice -s, it actually shows progressive on DVI and interlaced on RCA, so I'm guessing it would be simple to add an option to the config.txt to have half the vertical resolution and progressive scan. This would be really awesome especially for running emulators as it would give a video output almost identical to the original.