Pagination limits and count query behavior in Doctrine's Paginator

Behavior of Doctrine Paginator Count Queries

Yes, the Doctrine\ORM\Tools\Pagination\Paginator will always trigger a count query when you call count() or iterate over the result set, regardless of whether you already know the total count. When a fetch join is applied to a collection association, the Paginator automatically implements a DISTINCT identifier strategy to ensure that the root entity count remains accurate despite the Cartesian product created by the join.

Likely Explanation of Performance Scaling

The performance degradation observed on datasets with millions of rows is typically not caused by the data retrieval query, but by the count query. Because the Paginator must ensure distinct root entities, it often generates a SELECT COUNT(DISTINCT id) or a wrapped subquery. On large datasets, this forces the database to perform a full index scan or create a temporary table to resolve the distinct identifiers, which scales poorly as the result set grows.

Strategies to Mitigate Count Overhead

Since the count query cannot be disabled via a configuration flag within the Paginator class, you must move the count logic outside the Paginator's automated flow:

• Manual Count Caching: Execute a separate, optimized count query and cache the result. Pass the Paginator only the setFirstResult() and setMaxResults() parameters, and avoid calling count($paginator).
• Approximate Counting: For extremely large datasets, replace the exact count with an estimate from database metadata (e.g., EXPLAIN plans in PostgreSQL or MySQL) if a precise page count is not business-critical.
• Keyset Pagination: Abandon the Paginator class in favor of "seek-method" pagination. By filtering on the last seen ID (WHERE id > :last_id LIMIT 20), you eliminate the need for a total count and OFFSET, which significantly improves performance on million-row tables.

Verification Steps

To confirm the impact of the count query in your specific environment, use the following approach:

1. Enable the Doctrine SQL logger or use the Symfony Profiler to capture the exact SQL generated.
2. Isolate the SELECT COUNT(...) query and run it directly in your database console with EXPLAIN ANALYZE.
3. Compare the execution time of the count query against the data retrieval query to verify that the count is indeed the bottleneck.

Diagnostic Detail Needed: Are you using a specific database dialect (e.g., PostgreSQL vs MySQL) and which version of Doctrine ORM are you running? Certain versions have different optimizations for how they wrap the count subquery.