0

I am trying to update round 255k documents in a proximately 200GB mongo collection. If I run explain plan on the query the winning plan hits the right index. But when I run node js script that executes the same query the index is missed. I found out the only way that index is always used is if I add a hint.

So, If I add a hint update of 255k records is made in seconds else in hours/days... :)

I would really like to understand why mongo misses the index and explain plain scores.

And a few more thing:

  • Does the order of indexes matter. What are some good practices
  • Do fields in the filter need to be I in the same order as in the index
  • How does index behave when using neq (not equal) filter
  • What does mongo do when it found more than one performance equal response. Does it execute limit(1)
  • How can he miss index even though the appropriate index is extremely faster

So down below are data mode, existing indexes, and part of the node js script.

 //Existing indexes 
  schema.index({ executed: -1, account: 1, status: 1, type: 1, retry: 1, _id: 1 });
  schema.index({ id: 1 });
  schema.index({ status: 1, deleted: 1, account: 1, updatedAt: -1 });
  schema.index({ account: 1, deleted: 1, updatedAt: -1 });
  schema.index({ type: 1, deleted: 1, account: 1 });
  schema.index({ deleted: 1, account: 1, status: 1 });
  schema.index({ willRun: 1, type: 1, deleted: 1 });
  schema.index({ cases: 1, deleted: 1 });
  schema.index({ procedure: 1, deleted: 1, type: 1, scheduled: -1, cases: 1 });
  schema.index({ account: 1, status: 1, executed: 1, type: 1, retry: 1, _id: 1 });

//part of script
        let tasksList = await tasks
            .find(
                {
                    account: mongoose.Types.ObjectId(accountId),
                    status: 0,
                    executed: {
                        $lte: '2020-10-16T06:01:17.171Z' // new Date(deleteDate)
                    },
                    type: {
                        $ne: 'taskForDelete'
                    },
                    retry: {
                        $ne: 5
                    }
                }, { _id: 1 }).hint({ account: 1, status: 1, executed: 1, type: 1, retry: 1, _id: 1 });
tadejcek
  • 49
  • 1
  • 5
  • Try running explain with the 'allPlansExecution' option to see how each index perfomed when planning. – Joe May 03 '21 at 07:48
  • The funny thing is that explain plan shows that the right index is always hit, but not when run just find – tadejcek May 04 '21 at 06:23
  • When it takes a long time there should be an entry in the log. What index does that log line show was used? – Joe May 04 '21 at 06:26

0 Answers0