We have a collection of around 60 million docs.
When the date in the following query increases and reaches a certain timestamp, the query takes much longer (> 2min compared to <1s).
const results = await Offer.find({
validFrom: { $gte: new Date(1675635799000) },
}).sort({ price_decimal: "desc" }).limit(4);
It’s not clear to me what is going on internally.
Even if some malformed docs were causing this, it doesn’t make sense that the execution takes longer when less docs need to be returned (when the $gte timestamp is increased).
Note that if we remove sorting from the query there is no issue and it takes < 1s.
The collection has 13 indexes and the one used for this query is price_decimal_-1_validFrom_-1
Here is a diff after running .explain("allPlansExecution")
for both queries:
JSONCompare - The Advanced JSON Linting & Comparison Tool
Any help would be appreciated