Performance issues with a lot of orders and customers

[khanh-to-niteco]

Bug report

Describe the bug

We were doing load tests with about 120K orders and 100K customers. Then we tried to load orders in admin UI with pagination, the system were not responding. After checking the Postgres database, the CPU usage was almost 100%.

After some investigation, it seemed that when loading orders, we need to join with customer table (with 100K rows)

The customer table has only 1 index, but it is not a clustered index on the primary key

After trying to add a clustered index on the customer.id column, we observed huge performance improvements, the paged orders now takes only about 2s to load.

System information

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

[olivermrbl]

@adrien2p, will you be able to have a look at this?

[adrien2p]

I have started a work on that domain sometimes ago here which leads to massive improvements as well. could you look at those indexes and see if that works for you as well?

cc @olivermrbl