While Max Payne was cutting-edge, a lot of what made the visuals appear impressive was due to hand-tweaking by a team of highly skilled artists and designers, who were probably using ridiculously primitive tooling. Pretty much every realistic 3D game of this era had to make do with low-res diffuse textures, prebaked lighting, mostly fixed-function rendering, pre-scripted interactions, and particle dynamics that were basically just a few lines of C++. Other early-2000's games like Serious Sam, Halo, and Metroid Prime also managed to create immersive visuals with very limited tech, using the same techniques as Max Payne.
Also interesting how his life changed after the game, he went a totally different route and left programming for good.
P.S. I still see polygonal instead of truly round barrels in modern games, when will we finally have quadratic surfaces or some other solution for that?
A remake/remaster of Max Payne 1&2 is in the works by the original developer (Remedy). Stated to be financed by Rockstar (who own the IP) to normal Rockstar AAA levels.