Use simple query mode for entity queries with limit 1 and no offset

Problem/Motivation

Entity queries have a "simple mode" which does not group by entity revision ID and entity ID and in some cases can be significantly more performant. It is not generally applicable because without the explicit grouping the same entity (revision) rows will appear multiple times in the case of multi-value fields and this messes with various expectations.

However, if we know the query is limited to a single result and there is no offset, this does not matter so we can still enable simple mode to avoid the performance penalty.

Steps to reproduce

Run

(string) \Drupal::entityTypeManager()->getStorage('node')->getQuery()
  ->accessCheck(FALSE)
  ->sort('changed', 'DESC')
  ->range(0, 1);

The result is:

SELECT "base_table"."vid" AS "vid", "base_table"."nid" AS "nid", max(node_field_data.changed) AS "expression"
FROM
"node" "base_table"
LEFT JOIN "node_field_data" "node_field_data" ON "node_field_data"."nid" = "base_table"."nid"
GROUP BY "base_table"."vid", "base_table"."nid"
ORDER BY "expression" DESC
LIMIT 1 OFFSET 0

Proposed resolution

Enable simple query mode for limit 1 queries with no offset.

The changes the resulting query to be the following:

SELECT "base_table"."vid" AS "vid", "base_table"."nid" AS "nid", "node_field_data"."changed" AS "changed"
FROM
"node" "base_table"
LEFT JOIN "node_field_data" "node_field_data" ON "node_field_data"."nid" = "base_table"."nid"
ORDER BY "node_field_data"."changed" DESC
LIMIT 1 OFFSET 0

Remaining tasks

User interface changes

-

Introduced terminology

-

API changes

-

Data model changes

-

Release notes snippet