Performance degradation with the new Paginator

[ossinkine]

Environment

Sonata packages

show

$ composer show --latest 'sonata-project/*'
sonata-project/admin-bundle              4.2.2  4.2.2  The missing Symfony Admin Generator
sonata-project/block-bundle              4.8.0  4.8.0  Symfony SonataBlockBundle
sonata-project/cache                     2.2.0  2.2.0  Cache library
sonata-project/doctrine-extensions       1.15.0 1.15.0 Doctrine2 behavioral extensions
sonata-project/doctrine-orm-admin-bundle 4.2.0  4.2.0  Integrate Doctrine ORM into the SonataAdminBundle
sonata-project/exporter                  2.9.1  2.9.1  Lightweight Exporter library
sonata-project/form-extensions           1.12.1 1.12.1 Symfony form extensions
sonata-project/twig-extensions           1.9.0  1.9.0  Sonata twig extensions

Symfony packages

show

$ composer show --latest 'symfony/*'
symfony/asset                      v4.4.27 v5.3.4  Manages URL generation and versioning of web assets such as CSS stylesheets, JavaScript files and image files
symfony/browser-kit                v4.4.27 v5.3.4  Simulates the behavior of a web browser, allowing you to make requests, click on links and submit forms prog...
symfony/cache                      v4.4.33 v5.3.10 Provides an extended PSR-6, PSR-16 (and tags) implementation
symfony/cache-contracts            v2.4.0  v2.4.0  Generic abstractions related to caching
symfony/config                     v4.4.33 v5.3.10 Helps you find, load, combine, autofill and validate configuration values of any kind
symfony/console                    v4.4.33 v5.3.10 Eases the creation of beautiful and testable command line interfaces
symfony/css-selector               v4.4.27 v5.3.4  Converts CSS selectors to XPath expressions
symfony/debug                      v4.4.31 v4.4.31 Provides tools to ease debugging PHP code
symfony/dependency-injection       v4.4.33 v5.3.10 Allows you to standardize and centralize the way objects are constructed in your application
symfony/deprecation-contracts      v2.4.0  v2.4.0  A generic function and convention to trigger deprecation notices
symfony/doctrine-bridge            v4.4.31 v5.3.8  Provides integration for Doctrine with various Symfony components
symfony/dom-crawler                v4.4.30 v5.3.7  Eases DOM navigation for HTML and XML documents
symfony/dotenv                     v4.4.33 v5.3.10 Registers environment variables from a .env file
symfony/error-handler              v4.4.30 v5.3.7  Provides tools to manage errors and ease debugging PHP code
symfony/event-dispatcher           v4.4.30 v5.3.7  Provides tools that allow your application components to communicate with each other by dispatching events a...
symfony/event-dispatcher-contracts v1.1.9  v2.4.0  Generic abstractions related to dispatching event
symfony/expression-language        v4.4.30 v5.3.7  Provides an engine that can compile and evaluate expressions
symfony/filesystem                 v4.4.27 v5.3.4  Provides basic utilities for the filesystem
symfony/finder                     v4.4.30 v5.3.7  Finds files and directories via an intuitive fluent interface
symfony/flex                       v1.17.2 v1.17.2 Composer plugin for Symfony
symfony/form                       v4.4.33 v5.3.10 Allows to easily create, process and reuse HTML forms
symfony/framework-bundle           v4.4.31 v5.3.10 Provides a tight integration between Symfony components and the Symfony full-stack framework
symfony/http-client-contracts      v2.4.0  v2.4.0  Generic abstractions related to HTTP clients
symfony/http-foundation            v4.4.33 v5.3.10 Defines an object-oriented layer for the HTTP specification
symfony/http-kernel                v4.4.33 v5.3.10 Provides a structured process for converting a Request into a Response
symfony/inflector                  v4.4.27 v5.3.4  Converts words between their singular and plural forms (English only)
Package symfony/inflector is abandoned, you should avoid using it. Use use `EnglishInflector` from the String component instead instead.
symfony/intl                       v4.4.31 v5.3.8  Provides a PHP replacement layer for the C intl extension that includes additional data from the ICU library
symfony/maker-bundle               v1.34.1 v1.36.1 Symfony Maker helps you create empty commands, controllers, form classes, tests and more so you can forget a...
symfony/mime                       v4.4.31 v5.3.8  Allows manipulating MIME messages
symfony/monolog-bridge             v4.4.27 v5.3.7  Provides integration for Monolog with various Symfony components
symfony/monolog-bundle             v3.7.1  v3.7.1  Symfony MonologBundle
symfony/options-resolver           v4.4.30 v5.3.7  Provides an improved replacement for the array_replace PHP function
symfony/phpunit-bridge             v4.4.33 v5.3.10 Provides utilities for PHPUnit, especially user deprecation notices management
symfony/polyfill-php80             v1.23.1 v1.23.1 Symfony polyfill backporting some PHP 8.0+ features to lower PHP versions
symfony/polyfill-php81             v1.23.0 v1.23.0 Symfony polyfill backporting some PHP 8.1+ features to lower PHP versions
symfony/process                    v4.4.30 v5.3.7  Executes commands in sub-processes
symfony/property-access            v4.4.30 v5.3.8  Provides functions to read and write from/to an object or array using a simple string notation
symfony/property-info              v4.4.31 v5.3.8  Extracts information about PHP class' properties using metadata of popular sources
symfony/proxy-manager-bridge       v4.4.27 v5.3.4  Provides integration for ProxyManager with various Symfony components
symfony/psr-http-message-bridge    v2.1.2  v2.1.2  PSR HTTP message bridge
symfony/routing                    v4.4.30 v5.3.7  Maps an HTTP request to a set of configuration variables
symfony/security-acl               v3.2.0  v3.2.0  Symfony Security Component - ACL (Access Control List)
symfony/security-bundle            v4.4.27 v5.3.8  Provides a tight integration of the Security component into the Symfony full-stack framework
symfony/security-core              v4.4.33 v5.3.10 Symfony Security Component - Core Library
symfony/security-csrf              v4.4.27 v5.3.4  Symfony Security Component - CSRF Library
symfony/security-guard             v4.4.27 v5.3.7  Symfony Security Component - Guard
symfony/security-http              v4.4.30 v5.3.10 Symfony Security Component - HTTP Integration
symfony/serializer                 v4.4.33 v5.3.10 Handles serializing and deserializing data structures, including object graphs, into array structures or oth...
symfony/service-contracts          v2.4.0  v2.4.0  Generic abstractions related to writing services
symfony/stopwatch                  v4.4.27 v5.3.4  Provides a way to profile code
symfony/string                     v5.3.10 v5.3.10 Provides an object-oriented API to strings and deals with bytes, UTF-8 code points and grapheme clusters in ...
symfony/swiftmailer-bundle         v3.5.2  v3.5.2  Symfony SwiftmailerBundle
symfony/translation                v4.4.32 v5.3.10 Provides tools to internationalize your application
symfony/translation-contracts      v2.4.0  v2.4.0  Generic abstractions related to translation
symfony/twig-bridge                v4.4.27 v5.3.7  Provides integration for Twig with various Symfony components
symfony/twig-bundle                v4.4.30 v5.3.10 Provides a tight integration of Twig into the Symfony full-stack framework
symfony/validator                  v4.4.33 v5.3.10 Provides tools to validate values
symfony/var-dumper                 v4.4.33 v5.3.10 Provides mechanisms for walking through any arbitrary PHP variable
symfony/var-exporter               v4.4.31 v5.3.8  Allows exporting any serializable PHP data structure to plain PHP code
symfony/web-profiler-bundle        v4.4.31 v5.3.8  Provides a development tool that gives detailed information about the execution of any request
symfony/workflow                   v4.4.27 v5.3.4  Provides tools for managing a workflow or finite state machine
symfony/yaml                       v4.4.29 v5.3.6  Loads and dumps YAML files

Doctrine packages

show

$ composer show --latest 'doctrine/*'
doctrine/annotations                1.13.2 1.13.2 Docblock Annotations Parser
doctrine/cache                      1.12.1 2.1.1  PHP Doctrine Cache library is a popular cache implementation that supports many different drivers such as red...
doctrine/collections                1.6.8  1.6.8  PHP Doctrine Collections library that adds additional functionality on top of PHP arrays.
doctrine/common                     3.2.0  3.2.0  PHP Doctrine Common project is a library that provides additional functionality that other Doctrine projects ...
doctrine/data-fixtures              1.5.1  1.5.1  Data Fixtures for all Doctrine Object Managers
doctrine/dbal                       2.13.4 3.1.4  Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and manag...
doctrine/deprecations               v0.5.3 v0.5.3 A small layer on top of trigger_error(E_USER_DEPRECATED) or PSR-3 logging with options to disable all depreca...
doctrine/doctrine-bundle            2.3.2  2.4.3  Symfony DoctrineBundle
doctrine/doctrine-fixtures-bundle   3.4.1  3.4.1  Symfony DoctrineFixturesBundle
doctrine/doctrine-migrations-bundle 3.0.3  3.2.1  Symfony DoctrineMigrationsBundle
doctrine/event-manager              1.1.1  1.1.1  The Doctrine Event Manager is a simple PHP event system that was built to be used with the various Doctrine p...
doctrine/inflector                  2.0.4  2.0.4  PHP Doctrine Inflector is a small library that can perform string manipulations with regard to upper/lowercas...
doctrine/instantiator               1.4.0  1.4.0  A small, lightweight utility to instantiate objects in PHP without invoking their constructors
doctrine/lexer                      1.2.1  1.2.1  PHP Doctrine Lexer parser library that can be used in Top-Down, Recursive Descent Parsers.
doctrine/migrations                 3.3.0  3.3.2  PHP Doctrine Migrations project offer additional functionality on top of the database abstraction layer (DBAL...
doctrine/orm                        2.8.5  2.10.2 Object-Relational-Mapper for PHP
doctrine/persistence                2.2.3  2.2.3  The Doctrine Persistence project is a set of shared interfaces and functionality that the different Doctrine ...
doctrine/sql-formatter              1.1.2  1.1.2  a PHP SQL highlighting library

PHP version

$ php -v
PHP 7.4.16 (cli) (built: Apr 15 2021 00:56:09) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.16, Copyright (c), by Zend Technologies
    with Xdebug v3.0.4, Copyright (c) 2002-2021, by Derick Rethans

Subject

After updating this package from 3.30.0 to the latest 3.* and later to 4.2.0 we've faced with a huge performance degradation. Looks like this relates to Paginator update #1444

Seems to be the problem with this code

SonataDoctrineORMAdminBundle/src/Util/SmartPaginatorFactory.php

Lines 65 to 68 in f0ca9d9

	// it is only safe to disable output walkers for really simple queries
	if (!$hasHavingPart && !$hasJoins && $hasSingleIdentifierName) {
	$paginator->setUseOutputWalkers(false);
	}

Admin class generates the following query (simplified):

SELECT f0_.id AS id_0, f0_.foo AS foo_1 FROM foo f0_ LEFT JOIN bar b1_ ON f0_.id = b1_.foo_id ORDER BY id_0 DESC LIMIT 257

Since it has a join $paginator->setUseOutputWalkers(false); wasn't call and Doctrine Paginator uses LimitSubqueryOutputWalker and converts the query to

SELECT DISTINCT id_0, MIN(sclr_2) AS dctrn_minrownum FROM (
    SELECT f0_.id AS id_0, f0_.foo AS foo_1, ROW_NUMBER() OVER (ORDER BY t0_.id DESC) as sclr_2 FROM foo f0_ LEFT JOIN bar b1_ ON f0_.id = b1_.foo_id                                       
) GROUP BY id_0 ORDER BY dctrn_minrownum ASC LIMIT 257

Actually this means full scan foo table which contained over billion records.

Query plan looks like

 Limit  (cost=10130996966.59..10130996966.60 rows=1 width=16)
   ->  Unique  (cost=10130996966.59..10282834876.19 rows=20245054613 width=16)
         ->  Sort  (cost=10130996966.59..10181609603.12 rows=20245054613 width=16)
               Sort Key: (min(dctrn_result.sclr_120)), dctrn_result.id_0
               ->  GroupAggregate  (cost=5500480962.56..5854769418.29 rows=20245054613 width=16)
                     Group Key: dctrn_result.id_0
                     ->  Sort  (cost=5500480962.56..5551093599.10 rows=20245054613 width=16)
                           Sort Key: dctrn_result.id_0
                           ->  Subquery Scan on dctrn_result  (cost=0.71..1224253414.26 rows=20245054613 width=16)
                                 ->  WindowAgg  (cost=0.71..1021802868.13 rows=20245054613 width=24)
                                       ->  Index Only Scan Backward using foo_id_idx on foo f0_  (cost=0.71..667514412.41 rows=20245054613 width=16)

$paginator->setUseOutputWalkers(false); call reverts behavior and fixes the issue.

I suggest to fix this behavior or add an option to switch old Paginator behavior.

[ossinkine]

@alfabetagama could you please explain what exactly you did fix? Because the old behavior is pretty quickly and I have no idea why the current behavior should be faster.

[ossinkine]

I dug deeper and realized that @alfabetagama was solving the exact same problem but apparently with a different request.
Creating the SmartPaginatorFactory was a good solution to me but I don't understand why such conditions are used for setUseOutputWalkers(false);. According to doctrine/orm#8278 (comment) it allows to have joins so the following code might be a fix

// it is only safe to disable output walkers for really simple queries
- if (!$hasHavingPart && !$hasJoins && $hasSingleIdentifierName) {
+ if (!$hasHavingPart && $hasSingleIdentifierName) {
    $paginator->setUseOutputWalkers(false);
}

If you approve this I'll create a PR

[VincentLanglet]

Indeed, seems like they don't check for join in EasyAdmin either https://github.com/EasyCorp/EasyAdminBundle/blob/master/src/Orm/EntityPaginator.php#L55-L63