The RP-series PIOs are little programmable IO controllers. They can do can do their own thing, running independently of the software running on the MCU's CPU core, which allows them to work in tight little loops with very precise and predictable timing.
People use PIOs to do all kinds of things.
For instance, here is a method for using RP2040 PIO to produce VGA signals, using nothing but a Pi Pico, some jumper wires, and a few resistors on a breadboard: https://vanhunteradams.com/Pico/VGA/VGA.html
I myself have used RP2040 PIO to get a consistent PWM output in what was a bit of a boondoggle. I was working in Micropython, and the documentation for that said it supported hardware PWM output on this hardware, but that output was affected by the code in my main loop and was glitchy in ways that I found to be unusable. Rather than investigate the apparent issue with Micropython, I instead put together a thing in RP2040 PIO assembler that produced adjustable PWM with absolutely perfect consistency regardless of whatever I was doing in software.
And at least in my own example: The performance hit of doing this was zero since PIO is a dedicated hardware block that handles jobs like this in any way that I can program up.
Now, sure: I'd rather have used hardware PWM because that's simpler for me. And the ESP32 does have hardware PWM. But that PWM block only does PWM -- it can't be adjusted to do other things, whereas the RP-series PIOs can run arbitrary code to handle IO tasks. (So why didn't I pick an ESP, instead? That's easy enough to explain: I already had a Pi Pico in-hand.)
---
Meanwhile, as a general construct: There is absolutely zero reason to fanboy one MCU platform over another. It is absolutely OK that there are multiple competing inexpensive DIY-friendly-ish things in this space. This isn't Highlander. This isn't the fucking Super Bowl or the World Cup. There can be more than one.