Database loading Product Collection slow (Minutes)

I’ve got some third party code here that loads a larger product collection. It takes around 9 minutes to load on decent hardware. We have some 150000 SKU in the database.

Collection:

SQL is:

SELECT DISTINCT  `e`.*,
                IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS `status`,
                IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) AS `visibility`,
                `price_index`.`price`,
                `price_index`.`tax_class_id`,
                `price_index`.`final_price`,
                IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`,
                `price_index`.`min_price`,
                `price_index`.`max_price`,
                `price_index`.`tier_price`,
                (SELECT MAX(rating_summary) FROM review_entity_summary AS o
                    WHERE o.entity_pk_value = e.entity_id AND o.store_id = 2) AS `rating_summary`
        FROM `catalog_product_entity` AS `e`
       INNER JOIN `catalog_product_website` AS `product_website` ON product_website.product_id = e.entity_id AND product_website.website_id = 2
       INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '97') AND `at_status_default`.`store_id` = 0
       LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '97') AND (`at_status`.`store_id` = 2)
       INNER JOIN `catalog_product_entity_int` AS `at_visibility_default` ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '99') AND `at_visibility_default`.`store_id` = 0
       LEFT JOIN `catalog_product_entity_int` AS `at_visibility` ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '99') AND (`at_visibility`.`store_id` = 2)
       INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '2'
        WHERE (((IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1')))
          AND (IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) IN(3, 2, 4))

mariadb tells me on an analyze:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
1	PRIMARY	product_website	ref	“PRIMARY,CATALOG_PRODUCT_WEBSITE_WEBSITE_ID”	CATALOG_PRODUCT_WEBSITE_WEBSITE_ID	2	const	82150	50902.00	100	100	Using index; Using temporary
1	PRIMARY	e	eq_ref	PRIMARY	PRIMARY	4	magento2.product_website.product_id	1	1.00	100	100	“”
1	PRIMARY	at_status	eq_ref	“CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE,CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID”	CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID	8	“magento2.product_website.product_id,const,const”	1	0.00	0	100	“”
1	PRIMARY	at_visibility	ref	“CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE,CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID”	CATALOG_PRODUCT_ENTITY_INT_STORE_ID	2	const	1		0.00	100	100
1	PRIMARY	at_status_default	ref	“CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE,CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID”	CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID	4	magento2.product_website.product_id	9	9.11	11.14	8.83	Using where
1	PRIMARY	at_visibility_default	ref	“CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE,CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID”	CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID	4	magento2.product_website.product_id	9	9.24	11.18	10.82	Using where
1	PRIMARY	price_index	ref	“PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE”	CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE	6	“const,const”	91136	1.00	100	100	Using index condition
2	DEPENDENT SUBQUERY	o	ref	“REVIEW_ENTITY_SUMMARY_ENTITY_PK_VALUE_STORE_ID_ENTITY_TYPE,REVIEW_ENTITY_SUMMARY_STORE_ID”	REVIEW_ENTITY_SUMMARY_ENTITY_PK_VALUE_STORE_ID_ENTITY_TYPE	10	“magento2.e.entity_id,const”	1	0.00	100	100	Using index condition