It's easy to look back with hindsight and look at these platforms as failures, or obvious, stupid, mistakes; but the reality was that technology was exploding in the early 90s and the CD/FMV/3D arms race was in full swing. SEGA desperately didn't want to be left behind and even Nintendo themselves tried (and failed) to get in on the action.
At the time, it was the obvious next step for platform holders to take. Jumping straight to (very expensive) 3D hardware wasn't the obvious decision; lest we forget that both the PS1 and the Saturn were significantly more expensive than both the SNES and the Mega Drive!
SEGA CD was a huge boon for SEGA fans at the time, even if the library didn't end up living up to its potential. Its still one of the most successful addons of all time (which is very impressive, especially considering its hefty price tag!). It's very much to the 16 bit era, what PSVR is right now (even the sales numbers are similar).
Yeah, in terms of adoption, the CD-i is in the upper tier of addons that aren't called the Xbox 360 Kinect -- 8-something percent if I remember right. PS4 PSVR adoption was recently compared to Sega CD purchase rates, yes, as you say. Sega may have considered the SCD to be something of a failure, but that'd only be because they apparently had much too high expectations for sales of a quite expensive addon. It did okay, and has a pretty good and interesting game library.
Makes sense that CDi would excel at video decoding, given that that was more or less its hardware's entire focus. It couldn't even do basic smooth scrolling!
Hey, the 32X and 3DO rarely manage 60fps in side-scrolling 2d games either, 3DO platformers usually are 30fps at best. Some systems were just not designed for platformers... and the CD-i was designed for photo CDs, informative interactive demo stations, video playback, and oh yeah a few games like Battleship and Connect Four. About the best scrolling I've seen so far in games that don't use the Digital Video Cartridge are... well, probably Link: The Faces of Evil, which does scroll not too badly, though it is slow. And in the racing genre, while Micro Machines and Virtual Speedway both are horribly slow and choppy, Accelerator has solid, smooth scrolling, and it doesn't use the DVC. Pretty nice. (I own Accelerator, but did not get CD-i Micro Machines, not yet at least. Accelerator is fun, in that Amiga-ish kind of way...)
With the additional hardware in the Digital Video Cartridge, however, games can do better scrolling. Few did, but look up Lucky Luke for CD-i -- it uses the DVC for some pretty nice graphics, reasonable scaling, and good animation. Unfortunately the sprites are inordinately large and the game is frustrating as a result, but it looks good...
But anyway, yeah, while the CD-i may be better at things like National Parks Tour -- the thing sure can display great-quality photographs for the early '90s -- or, with the DVC, Video CD movies than games, in terms of color and photo or video quality the CD-i is really quite good. It's far and away better than its contemporaries, the Sega CD and Turbo CD, in that respect. It has big downsides too of course, but it IS good at something.
(Interesting CD-i fact: to the system, all CD-i controllers are actually a mouse. So all CD-i gamepads have a speed switch on them somewhere, to adjust cursor speed, and the sticks on its remotes are analog.)
Pretty sure that Sipheed just uses pre-rendered sprites. There isn't any actual real-time polygon rendering happening.
This is a somewhat controversial point, but there is reason to believe that they are actual polygon models, and not just sprites. That is, the in-game objects, enemies, your ship, and such, are polygons. The model viewer is one thing, but ingame they look like polygons as well. The background is of course FMV.
Pretty much since I got the system, I've thought that SoulStar and Silpheed are the two games with the best visuals on the Sega CD. They're both quite impressive, SoulStar for being the best use of the Sega CD's scaling and rotation chips, and Silpheed for being the best use of FMV and polygons. (It was mostly Western developers who made games that used hardware scaling and rotation on the SCD, Japanese examples are few... there are the Sonic CD bonus stages, Formula One World Championship, Night Striker... not a lot else. Starblade is interesting too though, yes. I like the SCD version.). For FMV, as I said... maybe Wirehead? Or maybe it's just how crazy-silly that game is... it's grainy of course, but good looking for SCD FMV:
There was supposed to be a 32X CD version of Wirehead, but unfortunately it was cancelled. That's too bad, it'd have been cool to see.