Major performance hit for aggregation with lookup from 5.0.13 to 5.0.18

Working with Data
,
1

Hi, I’m trying to find why one of my aggregation is choking the server on 5.0.18 but is working fine on 5.0.13 (the issue was discovered after yesterday’s upgrade and fixed today by downgrading).

I’m using Java (reactive) driver, latest version. No changes were applied to indexes.

From my initial match, I’m doing 2 lookups using a $match and $eq with a variable. One of the lookup has a sub-lookup using the basic form (local/foreign field).

i.e. Collection A → Collection B and Collection C matching indexed fields of B and C from a variable of A. Then Collection C → D using the simple form on a indexed field of D.

It jumps from a sub-sec. query to choking one CPU for minutes.

My current guess is that the 2 $match using a variable from A are not using the indexes on B/C anymore…

3

Hi @Jean-Francois_Lebeau,

I assume nothing changed (same query, same server) except the MongoDB version. Could you provide the following information if possible (possibly from a v5.0.18 test server for 2.):

  1. The output of db.collection.explain("executionStats").aggregate(...) for the version 5.0.13 environment
  2. The output of db.collection.explain("executionStats").aggregate(...) for the version 5.0.18 environment

Otherwise if a 5.0.18 test environment is not available for 2. above then please provide:

  • Some sample documents from each collection
  • The aggregation pipeline being executed
  • The amount of documents in each collection

Regards,
Jason

1 Like