Does adding field to sparse index making it compound negate its performance?

25 Views Asked by At

This question was hinted at Here: https://jira.mongodb.org/browse/DOCS-14734

My nuance is as follows. Refer:

db.contacts.createIndex(
   { name: 1 },
   { sparse: true }
)

So in a collection with a million documents, only a small portion of them have name populated, hence the sparse index.

Now I want to leverage this index to filter to only those that have name populated and equal to some value plus match in some date range. So I want to modify this index as follows:

db.contacts.createIndex(
   { name: 1, postDate: 1 },
   { sparse: true }
)

Thing is, "postDate" is actually not sparsely populated, i.e., it's set in every document.

Per the documentation a compound sparse index:

Only indexes documents that contain a value for at least one of the keys.

Taken literally, this means that every document is indexed since the second field in the index is populated in every document.

This doesn't seem to square with notion that queries on compound indexes depend on the prefix.

I.e. a query like:

db.mycoll.find( { name: "abc", "postDate": { $gt : ISODate("2018-03-20T00:00:00.000+0000") } } )

would be inefficient since the sparseness of the name field is(?) ignored, unless I misunderstand. What I had assumed is that the resulting index would be small since only queries that have the name field will use this index because it's the prefix of the compound index. Actually I was hoping the index would stay sparse so that existing queries like

db.mycoll.find( { name: "abc" } )

would use the compound index and still be as performant, but losing the sparseness here by adding the second field to the index would seem to negate the performance of such a query since all documents in the db would fall in the index with name set to null, is this correct?

0

There are 0 best solutions below