I haven’t yet had the chance to test HPOS’s query efficiency. I suppose the biggest potential issue is in the sync operation that extracts information from the postmeta table and inserts it in the new COT / HPOS tables.
The benchmarks provided by the WooCommerce team look promising, and the indexes they’ve chosen look appropriate. (One exception: their Data Definition Language doesn’t specify prefix indexes for VARCHAR(255) columns. That will fail on legacy, Antelope versions of MySQL / MariaDB (5.6-).
Maybe they are going to deprecate those old versions. That would be great except for the fact that GoDaddy and other low-end hosting providers still operate those legacy servers. (I myself happen to support a Woo store that runs on GoDaddy against my advice.)
I think we’re good for now on COT / HPOS indexing. But I will absolutely keep an eye on it. Thanks for the heads-up and the question.