3

I have a Mongo 4.2.0 instance here on my development environment with a simple collection of only 300 entries.

I've build some basic queue handling juggling with some date fields.

To get an document that should be updated I have the following $expr-query, which runs very slow imho. 

db.collection("myupdates").findOneAndUpdate({
    $expr: {
      $and: [
        { $gt: ["$shouldUpdate", "$updatedAt"] },
        { $gt: ["$shouldUpdate", "$isUpdatingAt"] },
        { $gt: ["$shouldUpdate", "$updateErroredAt"] },
      ]
    },
  }, {
    $set: {
      isUpdatingAt: new Date(),
    },
  });

This query takes around ~120ms after warmup on my standard year 2019 laptop. Where my other simple queries only take ~3ms.

Although it doesn't really matter to set indexes with 300 documents, I've tried of course to set them all. Single to compound indexes. This does not do the trick.

It's also not the findOneAndUpdate, with countDocuments I achieve the same slow speed.

Is this the normal speed of an $expr or aggregation syntax? What did I wrong? Is there a better way to achieve this? Do I have to use Redis for this use case?

Possible solution

As @Neil Lunn pointed out in the answers, calculated conditions do not utilize an index and should be the last resort.

So I just got rid of the calculated condition by splitting the query into 2 queries. The first query is getting an actual value I can match with. These 2 queries boil down to ~10ms total, which is much better then 120ms.

  const shouldUpdateDateResult = await mongo.db.collection("myupdates").findOne({
    shouldUpdate: { $exists: true }
  }, {
    shouldUpdate: 1,
  });

  const shouldUpdateDate = shouldUpdateDateResult && shouldUpdateDateResult.shouldUpdate;

  const result = await mongo.db.collection("myupdates").findOneAndUpdate({
    $and: [
      { shouldUpdate: shouldUpdateDate },
      { $or: [
        { updatedAt: { $eq: null } },
        { updatedAt: { $exists: false } },
        { updatedAt: { $lte: shouldUpdateDate } }
      ] },
      { $or: [
        { isUpdatingAt: { $eq: null } },
        { isUpdatingAt: { $exists: false } },
        { isUpdatingAt: { $lte: shouldUpdateDate } }
      ] },
      { $or: [
        { updateErroredAt: { $eq: null } },
        { updateErroredAt: { $exists: false } },
        { updateErroredAt: { $lte: shouldUpdateDate } }
      ] },
    ],
  }, {
    $set: {
      isUpdatingAt: new Date(),
    },
  });

The whole idea behind this is a processing queue usable by multiple workers.

Felix Gertz
  • 175
  • 2
  • 8
  • 4
    Of course it's slow. Calculated conditions ***cannot utilize an index*** and therefore really should be a last resort and not something you want to depend on in a production implementation at any scale. That's not just a MongoDB thing BTW. Are you doing it wrong? Yes. What should you do? We don't really know without a lot better explanation of why you think you want to do this sort of update and what the overall purpose is. Only when you explain that can you get meaningful feedback on a better design. – Neil Lunn Oct 17 '19 at 10:34
  • 3
    Slight aside. Not sure if you chose the tag or if it was "auto suggested" ( as the question editor tends to do ) but the `expr` tag originally applied to the question, is actually for something completely different. More on topic, important things like **full collection scan** and **does not use an index** are indeed notably absent from the `$expr` documentation. There is *some* type of warning on the very similar `$where` clause, but even this seems *toned down* from what it once was. – Neil Lunn Oct 17 '19 at 10:43
  • 1
    Thanx a lot Neil. This was very helpful. I wasn't aware that the calculated conditions do not use an index, but if I think about it, it totally makes sense. This should be noted in red big letters at the top of the Mongo documentation. I've edited my question and updated it with a possible solution, although there is some room for optimization I guess. The whole thing should be a processing queue usable by multiple workers. – Felix Gertz Oct 17 '19 at 15:27

0 Answers0