So I renamed my profile and started clean.
Try in actual standalone installation too just for good measure. And, for your regular Opera install, uninstall it (no need to delete your profile when uninstalling since that doesn't make a difference), delete the install folder if it remains, and reinstall. That's just to make sure everything is good with Opera's program files.
You can also try clearing the "C:\windows\prefetch" folder so that Windows rebuilds the cache for Opera.
And, also, try something non-related like going to
opera://settings/systemand disabling hardware acceleration just to see if Opera on your system is taking a while to do GPU initialization stuff for some reason.
Well, it gets more interesting. Before trying clean, I disabled hardware and things changed. Now, most links open the new tab within 2 seconds. Some links/sites take a bit longer so it's not consistent anymore, seems more aligned with page complexity.
Now when I reenable hardware processing, it is slightly slower but inconsistent. Before it was consistently 7 secs or more. Now it is highly variable. Very weird. I wonder if it was somehow connected to older video drivers/API before?
For now I will keep hardware accelerate disabled.