Hi @steevej, I managed to reduce the number of joins and updated the $match conditions so the id index can be used where I’m matching by multiple fields:
{ $eq: ['$_id.field1', '$$field1Id'] },
{ $eq: ['$_id.field2', '$$field2Id'] },
The performance improvement was 10 minutes, from ~30 mins to ~20 mins. However this is still a lot, I moved the same data to PostgreSQL, and without any major optimizations, I managed to run the same query with the same data in under 9 seconds.
Initially, this query was split into multiple queries, where we aggregated the secondary collections as you suggested. I did not implement that part, but I recall the execution time was around 2-3 hours.