Pagination limits and count query behavior in Doctrine's Paginator

Goal and Context

When paginating query results that involve joins, Doctrine’s Paginator class performs a count query followed by a data retrieval query. This two‑step process ensures accurate total counts, but it can introduce significant overhead on large datasets or heavily filtered queries.

Constraints and Uncertainty

In scenarios where a fetch join is applied to a collection association, the Paginator uses a distinct identifier strategy to avoid duplicate root entities. However, the necessity and behavior of the count query in such cases are not fully documented. It is unclear whether the count query is always executed, whether it can be bypassed, and how its performance scales with dataset size.

Unresolved Questions

• Will the Paginator always trigger a distinct count query when a fetch join is used, even if the total count is known beforehand?
• Can the count query be disabled or replaced with a more efficient strategy for large, complex joins?
• What are the measurable performance implications of the count query on datasets exceeding several million rows, and are there best‑practice mitigations?