It was a fairly unique architecture that had some pros and cons. In reality what made it a winner was the steep subsidy by Sony (units were sold at a loss, making the assumption you would buy a few games) and the availability (later removed) of an official Linux distribution.
It was basically an all-purpose vector computing monster focused on SIMD. You could use it for physics simulations, animations, tesselation, etc. Basically everything you'd use a compute shader for nowadays.
That's why emulators need AVX512 support to match the PS3. It was incredibly powerful.
Obviously, in that era's single-threaded world no engine could make use of that functionality and few knew how to program for it. It was ahead of its time, by quite a while.
Remember around that time when the industry said that OpenCL would allow write-once run-everywhere compute code for a booming industry of diverse and competitive compute devices? I fell for that scam for a couple of years, before very fortunately getting a different job for long enough to watch the collapse from a safe distance.
Was the cell processor in the PS3 really that efficient for this purpose?