OK, given that it became clear that I'm alone experiencing this issue, it means that there's something very particular to my phone or VPN or network that is triggering it.
Then it came to me: in my home network, DNS via UDP is hijacked, DoT is blocked and well-known DoH servers too. Only my own DNS server (that implements all 3 protocols) can be used. This is so nothing in my network can bypass my DNS server and any domain rules I might have.
I then played with the "Private DNS" option in GOS and behold: once I changed it from "Automatic" to "Off", the other profiles immediately started to work. And what's more strange, if I change it back to "Automatic", it continues to work too. So mere changing the setting is enough to "fix" the issue.
I just found this out so I need to more experimentation and do some packet capturing as well, but what I suspect that is happening is the following:
When "Automatic" is chosen, the OS auto-detects my own DNS server when in my local network (I tested this before and I know it works: the label changes to "On" and I can see the queries arriving via TLS/HTTPS and not UDP 53). Then I am away from my network, "Automatic" ends up not using any DoT/DoH server, but then I connect to the VPN, and Android "Private DNS" algorithm detects my server again and configures the system to use it.
However, this will only work via the VPN. Other profiles are not in the VPN, but they attempt to use the private DNS anyway as this is a global setting. It has a private IP address, and therefore in the secondary profile it goes to the actual network and it is routed to nowhere and queries are never answered. Eventually they time out. I guess that it might even be the captive portal detection in the secondary profile that is failing and "holding" all other connections from ever completing.
Given that I just found this out and I'm reporting it here, a few details about the above may be slightly off but I think the explanation is consistent. With this new knowledge I'm going to spend more time in the upcoming days to do more testing and gather more evidence.
But it looks to me, even though everything is behaving as it should in a way, the "Private DNS: Automatic" setting in my specific setup ends up being a way in which the VPN in the owner profile ends up affecting a global state at the device level and then "poisoning" the other profiles.