My understanding is it's not just the resolution, but CRT vs LCD. N64 looks best on a a CRT.

The N64 is also mainly hampered by it's texture memory. You only have 4KB total to play with for a texture (it's manually managed memory, not a cache) and with mipmaping, that's effectively 2KB. That's one 32x32 picture at 16bit color.

Additionally, developers didn't have a great handle on how the more modern memory hierarchy (read high DRAM latency) affected things yet so a lot of techniques they used sort of fought against the architecture and led to low resolutions, and they used heavy blurring and antialiasing to compensate.

To add to this, this is why GoldenEye N64 has so many grayscale textures... they use less memory.

They took this to the next level with bad fur day. By combining simple textures with plain color filters they could create complex visuals without using more memory. Eg: All the liquids (there are a lot!) look and flow the same, but have different color filters applied to differentiate them. They managed to squeeze in a full voice track too!

Many artistic techniques in pre-LCD-era games are designed for the physical effects of composite cables and CRT screens. A simple example is transparency via dithering, where intentional color bleed produces the effect in the display equipment rather than the console: https://i.imgur.com/F6aW3dG.png

Perhaps, but a huge part of this is the shoddy decoding circuitry that most LCD TVs have. You can do a lot better. For example, you could buy a Framemeister, or you could retrofit the N64 with HDMI output.

The N64 the console with the worse video output and it poor texture quality doesn't help. The only thing that really improves it is an expensive internal HDMI mod.