A game that comes to mind would be The Last Express. It’s not so much a detective-y game and more… Well, more of some kind of device to torture yourself. Absolutely not my cup of tea, but PushingUpRoses did quite a good retrospective review on it - back when she still did those. The developers poured lots of time and energy into details that might have seemed really important to them when they were sitting together - but which did have very little importance or impact on the player’s experience.
I might come up with a better negative example if I had all the time in the world… But right now, that game seems like a very good pick for your specific request. :) (You don’t have to take just my word for it: It even made it into a certain list.)
The Last Express is available on GOG and went on sale rather regularly back when I still used GOG - just in case you want a closer look. The game itself, the manual and their making of video might be interesting, but truth be told: You will most likely get the most important parts just by watching PushingUpRoses’ YouTube video.