Performance degradation on catalog page

[IvanPletnyov]

Preconditions

1. Magento with MSI
2. Load performance fixtures
   bin/magento setup:perf:generate-fixtures setup/performance-toolkit/profiles/ce/small.xml
3. Enable SQL profiler

Steps to reproduce

1. Open category page (example http://localhost/category-4.html)

Magento with MSI profiler results

SQL query example:

Magento without MSI profiler results

SQL query example:

Query with explain

With MSI:

1. With conditions
   
2. Without conditions
   

Without MSI:

Additional info

1. Build on bamboo
2. Paying attention, if we transfer all products from default stock to custom stock, page load speed approximately equal to magento without MSI.
   SQL load speed on custom stock:
   

[maghamed]

hey, @vickdoan is there any update on this issue or maybe some assistance needed from our side?

[Stepa4man]

@maghamed, I did some investigation for category page performance.
I found 15 queries that have a very low performance. I have checked it
And I have several conclusions:

• All slow queries contain join to inventory_stock_1 sql view and it can't be indexed because it's just view (query without this sql view works like 10 times faster)
• Some of the queries have unnecessary joins to catalog_product_enity table just for the purpose to provide sku for join inventory_stock_1. (We don't need it, once we have product_id field in inventory_stock_1. We have to get rid of this join in spite of this will not solve any performance issue)
• Join by sku field between inventory_stock_1 view and another table (join by varchar field is anyway slower then join by int, so using product_id from the previous paragraph will solve this issue)

I suppose that changing all joins inventory_stock_1 view from sku to product_id will decrease at least 50% of all degradation.

Here is a db logging file with this 15 queries and stack trace.
(It was logged using medium profile)
msi-performance.log

P.S. I have already fixed with this pulll request for category page