I found the acl_get_list query to take ~7 seconds on a beefy AWS RDS Aurora box.
This was being called often as we use stop forum spam. A github issue showed some others had similar issues: https://github.com/rmcgirr83/stopforumspam/issues/19

acl_get_list:

https://area51.phpbb.com/docs/code/3.1.x/phpbb/auth/auth.html#method_acl_get_list

Tried about 20 index variations, but found that mysql only liked putting an index on all 4 columns in the user_group table. I am aware this is odd, but after not getting meaningful performance improvements without it, this is my recommendation for an enhancement to the DDL in phpbb.

ALTER TABLE {{phpbb_user_group}}
{{ ADD INDEX {{ acl_get_list_tuning}} (group_id ASC, user_pending ASC, user_id ASC, group_leader ASC);}}
{{ ;}}