Slow data fetch in Admin UI caused by SELECT DISTINCT in PostgreSQL

Questions and Answers Database-SQL
1

Hi, all,

I got a problem with slow data fetch in admin UI for collections, having ~1M items.
Strapi v3.5.1, PostgreSQL.

After analyzing the query produced by Strapi I found that it queries ‘select distinct’ for all columns.
EXAMPLE: select distinct “apps”.*, “apps”.“id” as “_strapi_tmp_apps_id” from “apps” order by “_strapi_tmp_apps_id” desc limit 10
Even if I create index on id field, ‘select distinct’ causes sequential scan of the table, making this index useless.

I wonder if there is an option to opt-out of ‘distinct’ and create pure select.
There should be some reason why Strapi developers put ‘distinct’ on such queries, but having unique IDs I don’t see scenario, where ‘distinct’ is needed.

Happy to hear any suggestion on optimizing this.
Thanks!

2

This problem should be mainly solved in release 3.6.2, as you can see in this issue: Remove some of the unnecessary distinct by jonmol · Pull Request #9538 · strapi/strapi · GitHub

I had the same problem, and updating the strapi version did the trick!

1 Like