[SERVER-31620] Compound index with datetime and geospatial fields doesn't work Created: 18/Oct/17  Updated: 27/Oct/23  Resolved: 19/Oct/17

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 3.4.9
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Michael Felix Dias Assignee: Mark Agarunov
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-9257 2dsphere compound index cannot be use... Closed
Operating System: ALL
Participants:

 Description   

I've created a compound index:

db.lightningStrikes.createIndex({ datetime: -1, location: "2dsphere" })

But when I run the query below the MongoDB doesn't consider the index, making a COLLSCAN.

db.lightningStrikes.find({ datetime: { $gte: new Date('2017-10-15T00:00:00Z') } }).explain(true).executionStats

Ps. When I create a compound index by datetime and another type of field it works (searching by index), as below:

db.lightningStrikes.createIndex({ datetime: -1, source: 1 })

I've seen in the documentation and I haven't found any reason for it.
https://docs.mongodb.com/manual/core/2dsphere/#create-a-compound-index-with-2dsphere-index-key



 Comments   
Comment by Michael Felix Dias [ 20/Oct/17 ]

So if I create a compound index with `sparse = false` it should works!

Anyway I had already solved it as the same way that your recommendation. It makes me sure that I chose the best solution. Thanks!

Comment by Mark Agarunov [ 19/Oct/17 ]

Hello michaelfdias,

Thank you for the report. Looking over your examples, I believe this may be due to 2dsphere indexes being sparse. This behavior seems similar to the behavior described in SERVER-13830:

A Version 2 2dsphere index is always sparse. This means that documents which do not have the 2dsphere field, [...] will not be inserted into the index. 2dsphere indices in previous versions would simply reject such documents, which is why the sparseness property is desirable. A consequence of the sparseness property is that a Version 2 2dsphere index cannot be used to answer queries which are missing a geo predicate over the 2dsphere field.:

My recommendation would be to create another index for datetime which would be used for queries lacking a location field.

Thanks,
Mark

Generated at Thu Feb 08 04:27:39 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.