Performance issue with counting in admin order list

[khanh-to-niteco]

Bug report

Describe the bug

Hi Medusa team,

We are doing load tests with around 250K orders in the system, we notice that the order list in admin takes quite some time to respond, Through investigation, it seems that the counting when loading the list takes a lot of time, it seems that the DB has to scan the whole table which slows it down over time. The other queries seem to have run fast and scale well after adding indexes.

This is our test environment, in PROD we can expect to handle more data with more processing power but we may have much more data in PROD accumulated over time pretty quick.

System information

Medusa version (including plugins): 1.17.4 Node.js version: 18 Database: Postgres Operating system: Linux Browser (if relevant):

[adrien2p]

You might need to add specific indexes for you such as what is displayed here https://github.com/medusajs/medusa/issues/6307 with cluster index etc. We can't cover all needs in terms of indexes and db tuning as it can have bad impact for other cases. So it is a case by case needs

[khanh-to-niteco]

Hi @adrien2p ,

Not sure if indexes can help with count. I added clustered index in the test before. There are some articles pointing out issues with count and suggesting alternatives:

https://www.cybertec-postgresql.com/en/postgresql-count-made-fast/ https://www.citusdata.com/blog/2016/10/12/count-performance/

[khanh-to-niteco]

Hi @adrien2p ,

Related to the above issue. It seems that Medusa internally calls listAndCount in the list method for some places. For example:

This could cause performance issues in many places using the list method that don't need the count