My 2 cents, although I think I'm mostly on the same wavelength as 14v6. I'm basing this on my personal experience after owning a 2G Ridgeline for nearly 6 years and having an extensive 2 week 'test drive' of a 9 speed Pilot.
I'll preface this by saying that if you do a lot of towing or actual off-roading the 9 speed is probably a better transmission - the wider gear ratio spread, the lack of overheating (at least I haven't seen any complaints yet) and the ability to hold a gear are definite benefits (although I would also argue there are better choices than the RL for those tasks in the first place). Also, the 9-speed is probably better if you live on a flat area with high enough speed limits where the transmission won't hunt as much and you can actually use 9th gear.
That said, the wife and I have been looking for a 3-row SUV and even though I was looking to break away from Honda, we feel that the Pilot is probably the best choice for us. Unfortunately we are limited to a pre-2021 EX-L trim or lower because neither of us like the 9-speed.
We live in a rural hilly area and the 9-speed just
never stops looking for a better gear. And at the speeds around here we are constantly in that 3-4-5-6 zone where the shifts mostly feel normal except for that one disconcerting one that lingers or 'stumbles' a bit. My wife, who normally doesn't really care too much about that sort of thing, picked up on it right away, It is the same on our hilly highways (South PA/Northern MD I-83 for instance) except it is gears 7,8, and 9th. Mark describes it pretty well at the 12:05 mark
On the other hand, the 6-speed in the RL is normally in the right gear, or at least it isn't constantly looking for a better one. And when it does shift, the shifts all feel the same and there is no weirdness going on at any speed (other than VCM but that isn't the transmissions fault). If it has to downshift for a hill on the highway it drops one gear and stays there.
Even knowing the issues that others have reported about the 6-speed, and the ones that I had with mine ( TSB and flush/fill at 7k miles, another flush/fill at 40-ishK ) I'd rather go with the 6-speed. I'd rather know when something feels amiss (and likely taken care of with a cheap fluid change or a pressure switch) than constantly be wondering if that weird hiccup was something wrong or just the transmission doing what it does.