SonataDoctrineORMAdminBundle: List view for big tables very slow due to DISTINCT query

There was an issue where this was mentioned but at the end not adressed: #297 We’re using the SimplePager which indeed speeds things up as it does not do a count(…) query.

To visualize the difference, first one with DISTINCT, second one without:

drivenow_production=# explain analyze SELECT 
  DISTINCT e0_.id AS id_0, 
  e0_.timestamp AS timestamp_1 
FROM 
  EmittedEventRecord e0_ 
ORDER BY 
  e0_.timestamp DESC 
LIMIT 
  33 OFFSET 0;
                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=795326.66..795326.91 rows=33 width=45) (actual time=64059.389..64059.467 rows=33 loops=1)
   ->  Unique  (cost=795326.66..814667.44 rows=2578770 width=45) (actual time=64059.386..64059.432 rows=33 loops=1)
         ->  Sort  (cost=795326.66..801773.59 rows=2578770 width=45) (actual time=64059.384..64059.401 rows=33 loops=1)
               Sort Key: "timestamp", id
               Sort Method: external merge  Disk: 147920kB
               ->  Seq Scan on emittedeventrecord e0_  (cost=0.00..282727.70 rows=2578770 width=45) (actual time=0.040..48492.275 rows=2608727 loops=1)
 Total runtime: 64102.981 ms

drivenow_production=# explain analyze SELECT 
  e0_.id AS id_0, 
  e0_.timestamp AS timestamp_1 
FROM 
  EmittedEventRecord e0_ 
ORDER BY 
  e0_.timestamp DESC 
LIMIT 
  33 OFFSET 0;
                                                                                   QUERY PLAN                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..4.77 rows=33 width=45) (actual time=0.037..0.174 rows=33 loops=1)
   ->  Index Scan Backward using emittedeventrecordtimestamp on emittedeventrecord e0_  (cost=0.43..339144.15 rows=2578780 width=45) (actual time=0.034..0.115 rows=33 loops=1)
 Total runtime: 0.232 ms

Probably we can improve a bit by tuning Postgres, but generally I totally don’t understand why doing DISTINCT on the primary key(s) field(s) is required?

It seems there is no sustainable way to change this behaviour as we’d have to overwrite a couple of services, so we’d have to change if the bundles are updated.

I guess a config option to disable the DISTINCT behaviour would do the job. This was suggested in the closed issue reference above but never done.

Best regards, Anton