[SERVER-11897] Mongodb compound 2dsphere index dosen't work as expected Created: 28/Nov/13  Updated: 10/Dec/14  Resolved: 01/May/14

Status: Closed
Project: Core Server
Component/s: Geo, Index Maintenance
Affects Version/s: 2.4.6
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Marco Biagi Assignee: Thomas Rueckstiess
Resolution: Duplicate Votes: 0
Labels: 2dsphere, geo, geoIntersects, index
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-10801 Allow compound geo indexes to be able... Backlog
Participants:

 Description   

I have a collection called search2 with about 20000 documents like this:

    {
        "loc": {
        "type": "Polygon",
        "coordinates": [
            [
            [
                43.78526674007639,
                11.14739998758569
            ],
            [
                43.78526674007639,
                11.183372851822439
            ],
            [
                43.79443488391605,
                11.183372851822439
            ],
            [
                43.79443488391605,
                11.264311796355125
            ],
            [
                43.812771171595415,
                11.264311796355125
            ],
            [
                43.83110745927479,
                11.264311796355125
            ],
            [
                43.83110745927479,
                11.273305012414314
            ],
            [
                43.849443746954144,
                11.273305012414314
            ],
            [
                43.858611890793824,
                11.273305012414314
            ],
            [
                43.858611890793824,
                11.264311796355125
            ],
            [
                43.8769481784732,
                11.264311796355125
            ],
            [
                43.8769481784732,
                11.246325364236752
            ],
            [
                43.88611632231286,
                11.246325364236752
            ],
            [
                43.88611632231286,
                11.237332148177565
            ],
            [
                43.895284466152546,
                11.237332148177565
            ],
            [
                43.895284466152546,
                11.228338932118376
            ],
            [
                43.904452609992234,
                11.228338932118376
            ],
            [
                43.904452609992234,
                11.165386419704065
            ],
            [
                43.895284466152546,
                11.165386419704065
            ],
            [
                43.895284466152546,
                11.156393203644878
            ],
            [
                43.88611632231286,
                11.156393203644878
            ],
            [
                43.8769481784732,
                11.156393203644878
            ],
            [
                43.858611890793824,
                11.156393203644878
            ],
            [
                43.849443746954144,
                11.156393203644878
            ],
            [
                43.849443746954144,
                11.165386419704065
            ],
            [
                43.83110745927479,
                11.165386419704065
            ],
            [
                43.83110745927479,
                11.156393203644878
            ],
            [
                43.812771171595415,
                11.156393203644878
            ],
            [
                43.812771171595415,
                11.14739998758569
            ],
            [
                43.79443488391605,
                11.14739998758569
            ],
            [
                43.78526674007639,
                11.14739998758569
            ]
            ]
        ]
        },
        "docId": 1,
        "docVote": 0,
        "title": "title-1",
        "_id": {
        "$oid": "5248725d2dd5622510000001"
        }
    }

I define an index with this command:

    db.search2.ensureIndex({"docVote": 1,"loc":"2dsphere"});

On the collection there are only this index and the default index on "_id" field.

When i execute the following query i expect "nscannedObjects" to be = 10 :

    db.search2.find({
        loc: {
        $geoIntersects: {
            $geometry: {
            type: "Polygon",
            coordinates: [
                [
                    [43.7269795, 11.1540365],
                    [43.8329368, 11.1540365],
                    [43.8329368, 11.3310908],
                    [43.7269795, 11.3310908],
                    [43.7269795, 11.1540365]
                ]
            ]
            }
        }
        }
    }, {
        "docVote": 1,
        _id: 0
    }).sort({
        "docVote": 1
    }).limit(10).hint({
        "docVote": 1,
        "loc": "2dsphere"
    }).explain()

But this is the result:

    {
    "cursor" : "S2Cursor",
    "isMultiKey" : true,
    "n" : 10,
    "nscannedObjects" : 44283,
    "nscanned" : 648117,
    "nscannedObjectsAllPlans" : 44283,
    "nscannedAllPlans" : 648117,
    "scanAndOrder" : true,
    "indexOnly" : false,
    "nYields" : 13,
    "nChunkSkips" : 0,
    "millis" : 12632,
    "indexBounds" : {
 
    },
    "nscanned" : 648117,
    "matchTested" : NumberLong(46642),
    "geoTested" : NumberLong(46642),
    "cellsInCover" : NumberLong(8),
    "server" : "*********"
    }

If i remove sort from the query i obtain this:

    {
        "cursor" : "S2Cursor",
        "isMultiKey" : true,
        "n" : 10,
        "nscannedObjects" : 10,
        "nscanned" : 25,
        "nscannedObjectsAllPlans" : 10,
        "nscannedAllPlans" : 25,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 3,
        "indexBounds" : {
 
        },
        "nscanned" : 25,
        "matchTested" : NumberLong(10),
        "geoTested" : NumberLong(10),
        "cellsInCover" : NumberLong(8),
        "server" : "******"
    }

So, why index is not used to sort results? From this documentation: http://docs.mongodb.org/manual/applications/geospatial-indexes/ http://docs.mongodb.org/manual/tutorial/sort-results-with-indexes/ I understand that MongoDb support "A compound index with scalar index fields (i.e. ascending or descending) as a prefix or suffix of the 2dsphere index field" and that "If the sort document is a subset of a compound index and starts from the beginning of the index, MongoDB can use the index to both retrieve and sort the query results."

What am I missing?

Thanks in advance



 Comments   
Comment by Thomas Rueckstiess [ 01/May/14 ]

Hi Marco,

I noticed this ticket is still marked unresolved, apologies for that. As discussed in SERVER-10801, this was a limitation in the old query framework. I just wanted to let you know that I tested this issue with 2.6.0 and your particular use case seems to work now (see below). Therefore I'll mark this ticket as fixed.

Regards,
Thomas

db.search2.find({
    "loc" : {
        "$geoIntersects" : {
            "$geometry" : {
                "type" : "Polygon",
                "coordinates" : [
                    [
                        [ 43.7269795, 11.1540365 ],
                        [ 43.8329368, 11.1540365 ],
                        [ 43.8329368, 11.3310908 ],
                        [ 43.7269795, 11.3310908 ],
                        [ 43.7269795, 11.1540365 ]
                    ]
                ]
            }
        }
    }
}).sort({docVote:1}).limit(10).explain()
{
	"cursor" : "BtreeCursor docVote_1_loc_2dsphere",
	"isMultiKey" : true,
	"n" : 10,
	"nscannedObjects" : 10,
	"nscanned" : 11,
	"nscannedObjectsAllPlans" : 10,
	"nscannedAllPlans" : 11,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 2,
	"indexBounds" : {
		"docVote" : [
			[
				{
					"$minElement" : 1
				},
				{
					"$maxElement" : 1
				}
			]
		],
		"loc" : [
			[
				{
					"$minElement" : 1
				},
				{
					"$maxElement" : 1
				}
			]
		]
	},
	"server" : "enter.local:27017",
	"filterSet" : false
}
>

Comment by Marco Biagi [ 29/Nov/13 ]

Sorry, please change the version to 2.5.4.

Comment by Marco Biagi [ 28/Nov/13 ]

Note: in my collection i have 20010 object.

Comment by Marco Biagi [ 28/Nov/13 ]

I have made the same test with mongodb 2.5.4 and it dosen't seems to work as I Expect:
index is used, but this is the result:
"cursor" : "BtreeCursor vote_-1_bounding_polygon_2dsphere_tags_1 reverse",
"isMultiKey" : true,
"n" : 1,
"nscannedObjects" : 20010,
"nscanned" : 678839,
"nscannedObjectsAllPlans" : 20109,
"nscannedAllPlans" : 678938,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 5303,
"nChunkSkips" : 0,
"millis" : 1962,

The limit was 20.

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