[SERVER-10801] Allow compound geo indexes to be able to provide sort Created: 17/Sep/13  Updated: 28/Dec/23

Status: Backlog
Project: Core Server
Component/s: Geo, Querying
Affects Version/s: 2.4.6
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Jacob Ribnik Assignee: Backlog - Query Integration
Resolution: Unresolved Votes: 15
Labels: qi-geo
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-10971 Mongodb compound 2dsphere index dosen... Closed
is duplicated by SERVER-11897 Mongodb compound 2dsphere index dosen... Closed
Related
is related to SERVER-13899 "Whole index scan" query solutions ca... Closed
is related to SERVER-13908 Whole index scan on sparse index shou... Backlog
Assigned Teams:
Query Integration
Participants:
Case:

 Description   

I have a user with a collection of documents with points and timestamps. He performs a geo query on the points and a sort on the timestamps.

I thought it would be useful to build a compound index with timestamp first and position second (timestamp_-1_position_2dsphere) and use that in the sort. However, the explain shows that the sort does not recognize that the timestamp is first in the index and instead goes straight to an S2Cursor.

I recreated the user environment with the following javascript:

for(var i = 0; i < 100000; i++){
    xrand = Math.random()*179+1;
    yrand = Math.random()*89+1;
    x = xrand.toFixed(2);
    y = yrand.toFixed(2);
    t = new Date();
    doc = {
        position: {
            type: "Point",
            coordinates: [parseFloat(x), parseFloat(y)]
        },
        timestamp: t
    };
 
    db.randomcoordinates.insert(doc);
}
 
db.randomcoordinates.ensureIndex({timestamp:-1,position:'2dsphere'})

and executed the following query and explain:

localhost(mongod-2.4.6) test> db.randomcoordinates.find({position: {$geoWithin: {$geometry: {type: "Polygon", coordinates: [[[1, 1], [1, 90], [180, 90], [180, 1], [1, 1]]]}}}}).sort({timestamp: -1}).limit(5).hint("timestamp_-1_position_2dsphere").explain()
{
  "cursor": "S2Cursor",
  "isMultiKey": true,
  "n": 5,
  "nscannedObjects": 47285,
  "nscanned": 114006,
  "nscannedObjectsAllPlans": 47285,
  "nscannedAllPlans": 114006,
  "scanAndOrder": true,
  "indexOnly": false,
  "nYields": 2,
  "nChunkSkips": 0,
  "millis": 1272,
  "indexBounds": {
 
  },
  "nscanned": 114006,
  "matchTested": NumberLong("66721"),
  "geoTested": NumberLong("66721"),
  "cellsInCover": NumberLong("14"),
  "server": "localhost:27017"
}

As you can see an S2Cursor was used, scanAndOrder is true, and it does not appear to use the timestamp index. It is perfectly happy however to use timestamp as a single index.



 Comments   
Comment by Donald Venable [ 30/Aug/17 ]

Is this still an issue? This is a use-case we're interested in. $nearSphere returns quick, but incorrect results (only uses sort), $geoWithin is very slow.

Comment by J Rassi [ 13/May/14 ]

SERVER-13899 (intentionally) breaks this; re-opening. Leaving assigned to hari.khalsa@10gen.com for triage.

Comment by Daniel Pasette (Inactive) [ 03/Oct/13 ]

This is a limitation of the current query system. You cannot currently use the non-geo portion of a compound 2dsphere index to perform a sort. It will scan all geo matches and do an in-memory sort of the results.

Comment by Marco Biagi [ 03/Oct/13 ]

I agree that it was duplicate with my issue. But please note that in my case was an integer/float, not a timestamp. Keep it in mind when solving this issue.
Kind regards

Generated at Thu Feb 08 03:24:06 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.