How does it work when executing the $regex query in Mongodb?

Question

I have a question about Mongodb Index.

As far as I know, when the query is executed, it is found in the data sorted by the selected Index

db.HASH_TAG.find({"hashtag" : { $regex : "^Test"}}).sort({htseq:1}).hint("hashtag_1_htseq_1").explain("executionStats");

========================= explain===============================

executionStages  :{
  "stage" : "SORT", 
  "inputStage" : {
     "stage" : "SORT_KEY_GENERATOR",
     "inputStage" : {
             "stage" : "FETCH",
              "inputStage" : {
                        "stage" : "IXSCAN",
                        "indexName" : "hashtag_1_htseq_1", 
                         "keyPattern" : {
                            "hashtag" : 1.0, 
                            "htseq" : 1.0
                        }, 
                  }
          }
     }
}

Please explain why SORT_KEY_GENERATOR occurs in the input stage.

score 1 · Accepted Answer · answered Feb 11 '22 at 06:43

Your regular expression is not an equality match, therefore the query planner cannot guarantee ahead of time that the index will return the documents in sorted order.

The values in the index will be sorted by hashtag and then by htseq, the regex would match hashtags "Test", "Tester", "Tested", "Testing", etc., so the matched documents will still need to be sorted to be returned in order of htseq values.

You might be able to get index-supported sorting by anchoring both end of the regex like { $regex : "^Test$"}, but at that point you may as well just use equality match.

How does it work when executing the $regex query in Mongodb?

1 Answers1