I don't think it's about the special effects. It's more about the audience.
I think there are number of factors in play, that I could probably write about for several pages, but I think what it comes down to is that it's much harder to write an SF RPG that is obviously exciting, and sustains excitement, especially it's not Science Fantasy, and somewhat harder to run such RPGs, than it is fantasy RPGs. Rules-light ones are particularly hard to do well, and in a way that actually feels like SF, but equally going crunch-heavy can make them extremely tiresome for the DM. I think you can use fiction-lead stuff like PtbA-type systems to bridge the gap, but it's an issue.
So I think is about the audience to some degree - but not in that they don't want to run or play SF RPGs, but rather that it's often quite hard to find an SF RPG that actually seems fun for a given group, and isn't a crushing burden on the DM.
I mean, speaking personally, I'd much rather run an SF RPG than a fantasy one. I naturally think of SF scenarios and events more easily than fantasy ones, but finding an SF RPG that actually does what I want, is about what I want, and so on? Oooof. I haven't found it yet. I had some hope when one of the Dungeon World guys (I think) started working on a conversion of Dungeon World to Mass Effect, but he never released it (or even a prototype, AFAIK, though he said he got pretty far), and other attempts have just not "got it".
Of course another issue one can run into with SF is that a lot of DMs aren't actually that hot on science, and thus the less fantasy and more science-oriented an RPG is, setting-wise, the more likely you run into an issue where the DM screws up some physical law that the players are aware of, and it's problematic just DM-fiat physical laws in a way that it's not in a wild and wooly fantasy world.
On top of all this, there's the issue that, even if you manage to make a good SF RPG that manages to balance a lot of this (I would argue CP2020 did a good job), the fact is, time will pass, and suddenly your futuristic setting will be looking a lot less futuristic, and your totally zeitgeist-y art-style, societal concepts, and so on may seem really old-fashioned. It's not just cyberpunk where this has happened. Traveller felt like a blast from the past in like, 1990, let alone 2020, in terms of the space-future it envisioned (space aristocrats and so on). Plus you're kind of competing against a lot of strong brands from film/TV, especially in space-SF. You essentially have to convince people they should play your setting instead of Star Wars, Star Trek, Firefly, and so on. That's kind of tough. With fantasy there's much less of this - particularly because few TV/movie/book settings actually seem that appealing to play in.
I'm definitely on board with the decaying society or frontier approach here. Both have some nice built in management tools.
These are practical solutions, but they narrow the scope of an RPG, and they often push it outside of what players and DMs are actually interested in. There are countless "decaying society"-type SF RPGs, even Traveller kind of is one (and arguably Star Wars), and Fading Suns is the clearest example, but they tend to come with a lot of baggage (often fantasy elements) and don't really seem like SF a lot of the time, because they tend to focus on different ideas to most SF, and not be optimistic.
Frontier SF is strangely somewhat rarely touched in RPGs. I mean, obviously it could be problematic, because there's the temptation to go for ghastly "cowboys and indians" scenarios (however well-meaning and noble-savage-involving), but it does seem like you could do more with that at least. The only obvious example I can think of, though, is Heavy Gear, but that is focused on very different elements (and indeed the frontier scenario is more about setting up a confrontation with the colonial power than anything else). I do feel like we could use some more frontier/exploration-type SF that doesn't try and do huge twists and actually crafts an interesting world (and like, is not any more science-fantasy than, say, Mass Effect, which is quite a lot of leeway but still routinely exceeded).
I do think depressing-as-hell setting are a bit of a problem with SF, too - you want hopeful, or least where positive things could happen, but without necessarily going into Star Trek Federation territory, and without glossing over the problems inherent to certain approaches.