Poor performance of joins only on revision_id

Problem/Motivation

It appears that at least one views plugin is using an inefficient joins.

Steps to reproduce

Create a view, add "Hierarchy depth" and set it to 0. You'll get a query like this:

SELECT node_field_data.title AS node_field_data_title, node_field_data.nid AS nid, users_field_data_node_field_data.uid AS users_field_data_node_field_data_uid FROM
node_field_data node_field_data
INNER JOIN users_field_data users_field_data_node_field_data ON node_field_data.uid = users_field_data_node_field_data.uid
LEFT JOIN nested_set_field_parent_node nested_set_field_parent_node ON node_field_data.vid = nested_set_field_parent_node.revision_id
WHERE (nested_set_field_parent_node.depth = '0')
ORDER BY node_field_data_title ASC
LIMIT 35 OFFSET 25

I believe this nested_set_field_parent_node.revision_id suffers from a lack of dedicated index. I'm finding it hard to replicate our performance issues outside of production so just creating the placeholder to get it started. Experimenting with an index.

Before

After index

Proposed resolution

Remaining tasks

User interface changes

API changes

Data model changes