Performance of moderation control state filter is slow

I'll get the ball rolling on this issue once again.

@smustgrave, just like you pointed out, it was a pretty miserable experience trying to go from 9.2.x to 10.1.x. So, I've started on a new (feature) branch with 10.1.x as base. I'll make an attempt to fix things based on larowlan's feedback.

amateescu → credited mstrelan → .

Just closed 🐛 Slow query joining on content_moderation_state_field_revision Active as a duplicate.

acbramley → changed the visibility of the branch 10.1.x to hidden.

acbramley → changed the visibility of the branch 11.x to hidden.

I've opened a new MR against 11.x with the implementation from https://git.drupalcode.org/project/drupal/-/merge_requests/9108 and then test coverage from https://git.drupalcode.org/project/drupal/-/merge_requests/1529

IMO the implementation is a bit clearer with the indexes added directly in getEntitySchema