Add foreign key indices for *massive* performance wins!

[skid]

Is your feature request related to a problem? Please describe.

Once you get to thousands of orders/products/customers, queries start to become slow. For exmple, we have around 60,000 imported orders and the admin order listing screen is quite slow. A simple explain analyze revealed the problem:

At least on PostgreSQL, indices are not automatically added to foreign key relations. So, for example, translation tables do not have indices on their baseId columns. This causes left joins on translation tables to perform sequential scans, and Vendure has quite a lot of queries like this one: SELECT * FROM product p LEFT JOIN product_translation t ON p.id = t.baseId.

It's not only on translation tables - these sequential scans happen everywhere. Here's an example visualization:

Here the `product_variant_price` does a seq scan each time it's joined to the `product_variant` table, which is basically every time you load a list of products with prices, which is ... 90% of the queries.

Describe the solution you'd like

I assume that ecommerce applications are read heavy. Many people browse all the time, and some of them add items to cart. It makese sense to add indices to ALL foreign keys.

Alternatively, it would also be useful for a developer using Vendure to be able to decorate Vendure entities with indices, or maybe add indices through configuration. There are also cases where non-foreign key indices are needed - e.g. we are searching the facet_value_translation tables by name.

[michaelbromley]

Note: it seems that MySQL automatically adds an index to all FK constraints: https://stackoverflow.com/a/304339/772859

Also confirmed by looking at the schema definition for OrderLine: