After upgrading to MongoDB v7, we’ve noticed a drastic performance regression with our membership query. The execution time went from milliseconds to over 20 minutes.
Here’s what we found:
- With
collation: { locale: 'en' }
:- Query execution time: 20+ minutes
docsExamined
: 2.47 billion (way more than the 435.6k documents in the target collection)
- Without
collation: { locale: 'en' }
:- Query execution time: ~700ms
docsExamined
: 1270 (acceptable range)
This suggests that applying collation in MongoDB v7 is causing an unexpectedly large scan. Is this a known issue with v7? Could this be related to changes in collation indexing or query planning?
Would appreciate any insights or suggestions on how to mitigate this issue. Thanks!