SonataDoctrineORMAdminBundle: Simple pager crashes on large datasets

Environment

Sonata packages

show

$ composer show --latest 'sonata-project/*'

sonata-project/admin-bundle              3.99.0 3.99.0 The missing Symfony Admin Generator
sonata-project/block-bundle              4.5.3  4.5.3  Symfony SonataBlockBundle
sonata-project/cache                     2.1.1  2.1.1  Cache library
sonata-project/doctrine-extensions       1.12.0 1.12.0 Doctrine2 behavioral extensions
sonata-project/doctrine-orm-admin-bundle 3.31.0 3.34.0 Integrate Doctrine ORM into the SonataAdminBundle
sonata-project/exporter                  2.6.2  2.6.2  Lightweight Exporter library
sonata-project/form-extensions           1.9.0  1.9.0  Symfony form extensions
sonata-project/twig-extensions           1.5.1  1.5.1  Sonata twig extensions
vagrant@ubuntu2004:~/apps/api-paztir$
sonata-project/block-bundle              4.5.3  4.5.3  Symfony SonataBlockBundle
sonata-project/cache                     2.1.1  2.1.1  Cache library
sonata-project/doctrine-extensions       1.12.0 1.12.0 Doctrine2 behavioral extensions
sonata-project/doctrine-orm-admin-bundle 3.31.0 3.34.0 Integrate Doctrine ORM into the SonataAdminBundle
sonata-project/exporter                  2.6.2  2.6.2  Lightweight Exporter library
sonata-project/form-extensions           1.9.0  1.9.0  Symfony form extensions
sonata-project/twig-extensions           1.5.1  1.5.1  Sonata twig extensions

Symfony packages

show

$ composer show --latest 'symfony/*'
symfony/contracts                v1.1.10 v2.4.0  A set of abstractions extracted out of the Symfony components
symfony/deprecation-contracts    v2.4.0  v2.4.0  A generic function and convention to trigger deprecation notices
symfony/monolog-bundle           v3.7.0  v3.7.0  Symfony MonologBundle
symfony/phpunit-bridge           v5.2.7  v5.2.7  Provides utilities for PHPUnit, especially user deprecation notices management
symfony/polyfill-ctype           v1.22.1 v1.22.1 Symfony polyfill for ctype functions
symfony/polyfill-intl-grapheme   v1.22.1 v1.22.1 Symfony polyfill for intl's grapheme_* functions
symfony/polyfill-intl-icu        v1.22.1 v1.22.1 Symfony polyfill for intl's ICU-related data and classes
symfony/polyfill-intl-idn        v1.22.1 v1.22.1 Symfony polyfill for intl's idn_to_ascii and idn_to_utf8 functions
symfony/polyfill-intl-normalizer v1.22.1 v1.22.1 Symfony polyfill for intl's Normalizer class and related functions
symfony/polyfill-mbstring        v1.22.1 v1.22.1 Symfony polyfill for the Mbstring extension
symfony/polyfill-php72           v1.22.1 v1.22.1 Symfony polyfill backporting some PHP 7.2+ features to lower PHP versions
symfony/polyfill-php73           v1.22.1 v1.22.1 Symfony polyfill backporting some PHP 7.3+ features to lower PHP versions
symfony/polyfill-php80           v1.22.1 v1.22.1 Symfony polyfill backporting some PHP 8.0+ features to lower PHP versions
symfony/psr-http-message-bridge  v2.1.0  v2.1.0  PSR HTTP message bridge
symfony/security-acl             v3.1.2  v3.1.2  Symfony Security Component - ACL (Access Control List)
symfony/string                   v5.2.6  v5.2.6  Provides an object-oriented API to strings and deals with bytes, UTF-8 code points a...
symfony/symfony                  v4.4.22 v5.2.7  The Symfony PHP framework

PHP version

$ php -v
PHP 7.4.18 (cli) (built: May  3 2021 11:27:06) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.18, Copyright (c), by Zend Technologies

Subject

Minimal repository with the bug

3.34

Steps to reproduce

Expected results

should take less than 3 ms

                                                     QUERY PLAN

Limit (cost=0.44…198.90 rows=251 width=1602) -> Group (cost=0.44…13921690.63 rows=17606664 width=1602) Group Key: id -> Index Scan Backward using lock_message_pkey on log c0_ (cost=0.44…13877673.97 rows=17606664 width=1602)

Actual results

takes 100seconds QUERY PLAN

Limit (cost=9115117.69…9115119.57 rows=251 width=16) -> Unique (cost=9115117.69…9247167.67 rows=17606664 width=16) -> Sort (cost=9115117.69…9159134.35 rows=17606664 width=16) Sort Key: (min(dctrn_result.sclr_21)), dctrn_result.id_0 -> GroupAggregate (cost=6511832.11…6819948.73 rows=17606664 width=16) Group Key: dctrn_result.id_0 -> Sort (cost=6511832.11…6555848.77 rows=17606664 width=16) Sort Key: dctrn_result.id_0 -> Subquery Scan on dctrn_result (cost=3732479.90…4216663.16 rows=17606664 width=16) -> WindowAgg (cost=3732479.90…4040596.52 rows=17606664 width=2248) -> Sort (cost=3732479.90…3776496.56 rows=17606664 width=8) Sort Key: c0_.id DESC -> Seq Scan on log c0_ (cost=0.00…1472558.64 rows=17606664 width=8)

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 21 (21 by maintainers)

Most upvoted comments

No custom query. It is standard admin with pager_type: ‘simple’. I tested versions from 3.31 up and this behaviour was introduced in 3.34. I checked and doctrine/orm was fixed at 2.8.4 for all tested SonataDoctrineORMAdminBundle versions.

This is a major issue for us, since all admin list performances are a bit degraded, depending on dataset size, for large datasets they are not usable.

alfabetagama on May 10, 2021