0

I created a sparse index in my MongoDB Atlas like so:

db.detection_events.createIndex({"emp_id": 1}, {sparse: true})

When I performed this operation:

db.detection_events.count({"emp_id": {$exists: true}})

I got the result extremely quickly. Upon readon the docs for sparse indeces, MongoDB recommends to use partial indeces instead. I created a partial index like so:

db.detection_events.createIndex({"emp_id": 1}, {partialFilterExpression: {emp_id: true}})

And the same query takes a long time to execute. I got the partial index code from here.

Why does the partial index have a much higher query time than the sparse one? Is it because I created the index incorrectly?

2
  • What is the MongoDB version(x,y,z)? May 13, 2019 at 7:52
  • @MdHaidarAliKhan The server version is 4.0.9
    – A_toaster
    May 14, 2019 at 3:11

1 Answer 1

1

Turns out I created the partial index incorrectly.

If you want to speed up an $exists query, you need to add the partialFilterExpression option like so:

db.detection_events.createIndex({"thingToQueryOn": 1}, {partialFilterExpression: {thingToQueryOn: {$exists: true}}})

Note the lack of quotation marks in the second document

3
  • It still seems slower than the sparse index version, but oh well
    – A_toaster
    May 13, 2019 at 6:26
  • 2
    These should actually be equivalent. I suggest comparing the results of explain('executionStats') for a partial vs sparse index -- one possible difference could be whether index/data has already been loaded into RAM. If there is a difference in query plans, you could post both for comparison as a new question. The specific version of MongoDB server would also be relevant (but should be included in the query explain output).
    – Stennie
    May 14, 2019 at 1:34
  • @Stennie I believe you're right. I ran the test again today and am not noticing any difference in speed anymore. Maybe I ran it while it was still building the index or something?
    – A_toaster
    May 14, 2019 at 3:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.