It does have a great impact.
Nowadays TTL are pretty short because infra is very dynamic, you don't necessarily hit the cache that often.
And a random webpage will pull assests from all over the place, with many different domains/subdomains, all requiring DNS resolution.
In the end, even if it's hidden you can definitely feel it in the "snapiness" of loading webpages. Of course when you only load/stream content it has no impact, so it is really dependent on your usage patterns.
Running your own private DNS resolver is also much faster, even with a medium org, you can definitely feel it.
That was initially what I thought as well. Until I tried out Google DNS, and then Cloudflare 1.1.1.1 before settling on NextDNS.