Changing a date in the query by 1ms increases examined docs by a factor of 300!

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 :pray:

Ok, apparently the indexes that we had were not selective enough for recent data.
The solution was to create a new index validFrom_-1_price_decimal_-1
and force it with .hint({validFrom: -1, price_decimal: -1}) when querying recent data.

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.