Issue #2950869 by Fabianx, amateescu, alexpott, abhishek-anand, adam.weingarten, Timmy_Cos, bryanmanalo, corneboele, matsbla, Wim Leers, mheip, effulgentsia, mariancalinro, Krzysztof Domański: Entity queries querying the latest revision very slow with lot

Issue #2950869 by Fabianx, amateescu, alexpott, abhishek-anand, adam.weingarten, Timmy_Cos, bryanmanalo, corneboele, matsbla, Wim Leers, mheip, effulgentsia, mariancalinro, Krzysztof Domański: Entity queries querying the latest revision very slow with lots of revisions

Closes #2950869

core/lib/Drupal/Core/Entity/Query/Sql/Query.php

@@ -132,8 +132,13 @@ protected function prepare() {
     // Add a self-join to the base revision table if we're querying only the
     // latest revisions.
     if ($this->latestRevision && $revision_field) {
-      $this->sqlQuery->leftJoin($base_table, 'base_table_2', "[base_table].[$id_field] = [base_table_2].[$id_field] AND [base_table].[$revision_field] < [base_table_2].[$revision_field]");
-      $this->sqlQuery->isNull("base_table_2.$id_field");
+      // Fetch all latest revision ids in a sub-query
+      $revision_subquery = $this->connection->select($base_table, 'base_table');
+      $revision_subquery->addExpression("MAX(base_table.$revision_field)");
+      $revision_subquery->groupBy("base_table.$id_field");
+
+      // Restrict results only to latest ids
+      $this->sqlQuery->condition("base_table.$revision_field", $revision_subquery, 'IN');
     }
 
     if (is_null($this->accessCheck)) {

[daffie]

This comment needs an update as it no longer reflects what we are doing.