Optimization of pulling "latest record earlier than X" in a large (500 million record) collection using index

Hello Fabien,

The time taken is because the index on the data.summary.elecScore is not used in the first $match stage. As the plan shows only the sort stage used the index defined on the updated_at field. The query had to scan all documents to filter the 75k documents.

To get an idea about how to use indexes with filter and sort stages see: Use Indexes to Sort Query Results. The sub-topic Sort and Non-prefix Subset of an Index is related to this aggregation query.

P.S. You may want to post a sample document from your collection.