It's hard to say but I think it would have been more successful. It's cool to think about all the different "what if..." scenarios. If Sony & Nintendo had gone ahead and released this and it ended up being a flop or not as successful as the Sega CD would Sony had even pursued doing their own console later on? Maybe they do, maybe they don't....but it didn't happen and things worked out better for them in the end....and Nintendo.
It's hard to say and of course this is all hypothetical. But If the SNES launched in 1991 and then they launched a SNES CD circa 92 or 93 as planned it would have been hard to support a console and an add on and have the add on be successful.
For one if it was a CD add on to the SNES then you would limit your sales to only people who have a SNES which in North America was struggling against the Genesis which had close to 60% market share up until late 1994. So right there you have a limited install base to basically being Japan and a smaller segment of the North American market.
Also you have the problem of the SNES CD competing against the Genesis. The Sega CD ran into a wall because why on Earth would you spend 200$ on a Genesis add on when you could spend less than that on an SNES which had way better games? So it's unlikely that a lot of consumers who had the cash to spend on an add on wouldn't have chosen the Genesis over a SNES CD.
Unless Nintendo and Sony would have been able to develop a series of killer apps that made the add on a must have (Games that were so good and so unlike anything available on the SNES/Genesis/SegaCD) then I can't see how successful an add on would be even with Nintendo's talent and both Nintendo and Sony's resources behind it.
Nintendo would also still have to support the SNES which would have taken up development resources and made their output on an add on more limited. If given good support it would have been a nice side venture and a good experiment into CD's for Nintendo and Sony, but considering that the Sega CD sold about 2+ million units I think a Nintendo Playstation would have topped out somewhere between 3-6 million depending on how much support it saw.
If the Playstation failed or was only a moderate venture (Sega never viewed the Sega CD as a failure internally, they thought it was an OK risk to take and they didn't lose money on it according to the console wars book) I am not sure what Sony's next move would have been. Would they have gone it alone or would Nintendo have gone with CD's for the N64 after having been more familiar with the tech and having Sony's talent chime in on their joint venture? Who knows but it for sure changed the course of video game history.