why does everyone think Moore's Law will just stop having any effect, and the PS3 will still be competitive in 5 years?
I'm sorry to disappoint you all, but Moore's law has held for 30 years (and you can even extrapolate it backwards to apply to computers without transistors) and there's no reason why it will suddenly stop being valid. This means that there will be 4x as many transistors on chips in four years time. more transistors means a higher performance per clock cycle.
The Cell is an interesting idea, and an excellent special purpose computer, but it is very specialised for tasks that are "embarrassingly parallel", and also heavy in mathematical calculations.
Interestingly enough, graphics processing is the single most parallelisable task that we know of, but that is already being taken care of by a graphics card (it's little wonder that Sony originally tried to have the Cell handle graphics, it would be well suited. However, specialised graphics hardware will always be better). Unfortunately, many other areas of games (such as AI) are not, and completely unsuited to the Cell. As a general purpose computer, the Core 2 Duo blows the Cell out of the water, and C2D is older than the PS3.
I can envision (in terms of hardware, five years from now) a powerful general purpose computer connected with multiple special purpose processors (one for graphics, one for physics, one for AI, etc), which would make the PS3 look like the PS2 does now. Of course, games would cost $100 million to make and the company that made this machine would never succeed. But don't think the Cell is some amazingly incredible piece of kit that won't be obsolete in 5 years, bcause it will be.
In 5 years Nintendo could easily release something more powerful than Cell, at the same price point as the Wii and with the same form factor, if they so desired.