I heard that The Last Of Us on PS3 really pushed the hardware to its limits, and seeing that game's visuals, I believe it. I think Naughty Dog put an enormous amount of time and effort into building and optimizing a game engine that could make thorough use of PS3's cell architecture, being very smart about the timing and order of various tasks to ensure maximum parallel processing during a generation where everything else was still running on one or two cores.

Additionally, Naughty Dog famously been pushing/punishing gaming systems for a long time, even games like Crash Bandicoot (1996 - PlayStation 1) and Jak and Dexter (2001 - PlayStation 2) really went up against the limitations and lots of hacks were incorporated to get the games to run well.

Uncharted and The Last of Us is just continue what is tradition at this point :)

There's an interesting documentary on the development of Crash Bandicoot on YouTube, https://www.youtube.com/watch?v=izxXGuVL21o

It would have been impressive in 2005, but by 2013-2014 TLOU wasn't that special any more. Average computers were much stronger than consoles by then.

Sure, but we're talking about impressive technical achievements on dated hardware, which is a different conversation than whether it bested modern PC hardware on the year of release.

I do think there are a few things that make the N64 kinda "special" for going back and squeezing more performance out of it. The 3D hardware is remarkably modern in some ways, but there are a couple very thorny resource limitations that you have to work within, and the system complexity is higher than you would probably like.

The main resource limitations you deal with are the main RAM banwidth and the TMEM size. The complexity comes from the division of work between the CPU and a coprocessor called the RSP, which is basically a stripped-down MIPS CPU core that has a SIMD unit and some scratch RAM. You can come up with cool ways to use the RSP, but if you eat up more of the main RAM bandwidth, you'll hurt performance.

The demo here is focused on working around the TMEM size limitation, but it looks like it also reduces the RAM bandwidth use by drawing in Z order rather than using the Z buffer... which is an overall solid approach for improving graphics performance on the N64.

The PS3 has some striking similarities to the N64, in that both consoles have coprocessors with SIMD units that operate on scratch RAM. Both consoles have a reputation for being difficult to program for. The PS3 takes things a bit farther in that the Cell SPEs can only access main memory through DMA. I can only imagine how hard it was to effectively use the SPEs.

The framebuffer tricks with the N64 and PS2 gave the developers (and later the emulator creators) lots of headaches.