Extremely slow query on 'catalog_product_relation' (index)

[tommartensa20f]

During an index process, this kind of query seem to hold up everything. MySQL is using around 1.5 hours to complete it and causes deadlocks or long waiting times for other indexers:

SELECT `e`.`entity_id` FROM `catalog_product_relation` AS `relation`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = relation.parent_id WHERE 
 (relation.child_id IN('522593',
  '522594', '522595', '522596', '443072', '443074', '522597', '522598', '522599'
  , '522600', '522601', 138, 151, 152, 153, 154, 155, 156, 157, 158, 172, 186, 214, 242, 256, 290,  ..... 

The WHERE clause uses around 395,000 product ids which most-likely skips the index and uses a table scan and slows down.

We wonder why where all these productIds come from. Can this be a third-party extension causing this tremendous slow query?

Version: 2.4.5-p1

397,237 products ( 377,038 simples ) ( 19,942 configurables )

Server Specs:

960GB SSD

36-core CPU

131GB RAM

[tommartensa20f]

Think we've resolved this issue. Somehow a handful of products contained, like, the entire product catalog als a child, which caused this ridiculously high number.