[SERVER-47223] geoNear/$geoNear does not apply index hints Created: 01/Apr/20  Updated: 29/Oct/23  Resolved: 22/Jun/20

Status: Closed
Project: Core Server
Component/s: Aggregation Framework, Querying
Affects Version/s: 4.2.3, 4.0.17
Fix Version/s: 4.0.20, 4.4.0-rc11, 4.2.9, 4.7.0

Type: Bug Priority: Major - P3
Reporter: Alex Bevilacqua Assignee: Mihai Andrei
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Related
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v4.4, v4.2, v4.0
Sprint: Query 2020-04-20, Query 2020-05-04, Query 2020-05-18, Query 2020-06-01, Query 2020-06-15, Query 2020-06-29
Participants:

 Description   

Prior to MongoDB 4.2 (4.1.1 specifically) the $geoNear aggregation stage was internally issuing a geoNear command through the
DBDirect client. With SERVER-35043 the geoNear command was removed and the necessary logic merged into the aggregation stage.

MongoDB 4.0.17

2020-03-31T07:44:58.950-0400 I COMMAND [conn7] command admin.foo appName: "MongoDB Shell" command: geoNear { geoNear: "foo", near: { coordinates: [ 106.65589, 10.787627 ], type: "Point" }, num: 100, query: { _id: { $in: [ "1", "2", "3" ] }, state: "ACTIVE" }, collation: { locale: "simple" }, spherical: true, distanceMultiplier: 1.0, key: "location", $db: "admin" } planSummary: GEO_NEAR_2DSPHERE { _id: 1, location: "2dsphere", state: 1 } keysExamined:0 docsExamined:0 numYields:0 reslen:128 locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 2 } }, Collection: { acquireCount: { r: 2 } } } protocol:op_msg 0ms

2020-03-31T07:44:58.951-0400 I COMMAND [conn7] command admin.foo appName: "MongoDB Shell" command: aggregate { aggregate: "foo", pipeline: [ { $geoNear: { query: { _id: { $in: [ "1", "2", "3" ] }, state: "ACTIVE" }, spherical: true, near: { coordinates: [ 106.65589, 10.787627 ], type: "Point" }, distanceField: "distance", key: "location" } }, { $project: { id: 1.0, name: 1.0 } } ], hint: { _id: 1.0, location: "2dsphere", state: 1.0 }, cursor: {}, lsid: { id: UUID("46e819d1-9d58-43ed-aee0-97d37cd0ba09") }, $db: "admin" } keysExamined:0 docsExamined:0 cursorExhausted:1 numYields:0 nreturned:0 reslen:98 locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 2 } }, Collection: { acquireCount: { r: 2 } } } protocol:op_msg 2ms

Given the two log lines shared you can see that the initial geoNear command has chosen a different index than what was hinted to the pipeline. This appears to be due to how the call from DocumentSourceGeoNear::runCommand() is building the command (see DocumentSourceGeoNear::buildGeoNearCmd()). The underlying geoNear command does not appear to take the hint from the pipeline when it is constructed so the query planner has to decide which index to use.

This bug affects the geoNear command as well when it is invoked directly.

MongoDB 4.2.5

2020-04-01T09:53:34.479-0400 I COMMAND [conn3] command admin.foo command: aggregate { aggregate: "foo", pipeline: [ { $geoNear: { query: { _id: { $in: [ "1", "2", "3" ] }, state: "ACTIVE" }, spherical: true, near: { coordinates: [ 106.65589, 10.787627 ], type: "Point" }, distanceField: "distance", key: "location" } }, { $project: { id: 1.0, name: 1.0, distance: 1.0 } } ], cursor: {}, $db: "admin", $readPreference: { mode: "primaryPreferred" } } planSummary: IXSCAN { location: "2dsphere" }, IXSCAN { location: "2dsphere" }, IXSCAN { location: "2dsphere" }, IXSCAN { location: "2dsphere" }, IXSCAN { location: "2dsphere" }, IXSCAN { location: "2dsphere" }, IXSCAN { location: "2dsphere" } keysExamined:7 docsExamined:2 fromMultiPlanner:1 cursorExhausted:1 numYields:0 nreturned:1 queryHash:54B91EED planCacheKey:B1991E01 reslen:150 locks:{ ReplicationStateTransition: { acquireCount: { w: 2 } }, Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 2 } }, Collection: { acquireCount: { r: 2 } }, Mutex: { acquireCount: { r: 2 } } } storage:{ data: { bytesRead: 223 } } protocol:op_msg 3ms

Even once the geoNear command is no longer in use (MongoDB 4.2) the pipeline stage still discards the hint (it is not logged either).

Note that this behavior can be reproduced as follows:

db.setProfilingLevel(0, { slowms: 0 })
db.foo.drop();
db.foo.insert({ _id: "1", state: "ACTIVE", location: [106, 10], name: "TEST" })
db.foo.createIndex({ location: "2dsphere" })
db.foo.createIndex({ _id: 1, location: "2dsphere", state: 1 });
db.foo.aggregate([
{ $geoNear: {
  query: { 
    _id: { $in: ["1", "2", "3"] },
    state: "ACTIVE"
  },
  spherical: true,
  near: { coordinates: [106.65589, 10.787627], type: "Point" },
  distanceField: "distance",
  key: "location"
}}, 
{ $project: {
  id: 1, name: 1, distance: 1
}}], { hint: { _id: 1, location: "2dsphere", state: 1}});



 Comments   
Comment by Githook User [ 25/Jun/20 ]

Author:

{'name': 'Mihai Andrei', 'email': 'mihai.andrei@10gen.com', 'username': 'mtandrei'}

Message: SERVER-47223 geoNear/$geoNear does not apply index hints

(cherry picked from commit e1d82874e90ed0cb7fd6898edc54d47c1a42b97c)
(cherry picked from commit bcb0cf270fdbcb6d1cbf9998e29afbfe4cbeebde)
(cherry picked from commit ae22f844e2e142f6f973edd210dbc511a9dc1ef0)
Branch: v4.0
https://github.com/mongodb/mongo/commit/2d6458d8a1a8de3f58d2f8c6747b60971ff9800f

Comment by Githook User [ 22/Jun/20 ]

Author:

{'name': 'Mihai Andrei', 'email': 'mihai.andrei@10gen.com', 'username': 'mtandrei'}

Message: SERVER-47223 Verify that $geoNear applies index hint correctly

(cherry picked from commit e1d82874e90ed0cb7fd6898edc54d47c1a42b97c)
(cherry picked from commit bcb0cf270fdbcb6d1cbf9998e29afbfe4cbeebde)
Branch: v4.2
https://github.com/mongodb/mongo/commit/ae22f844e2e142f6f973edd210dbc511a9dc1ef0

Comment by Githook User [ 22/Jun/20 ]

Author:

{'name': 'Mihai Andrei', 'email': 'mihai.andrei@10gen.com', 'username': 'mtandrei'}

Message: SERVER-47223 Verify that $geoNear applies index hint correctly

(cherry picked from commit e1d82874e90ed0cb7fd6898edc54d47c1a42b97c)
Branch: v4.4
https://github.com/mongodb/mongo/commit/bcb0cf270fdbcb6d1cbf9998e29afbfe4cbeebde

Comment by Githook User [ 22/Jun/20 ]

Author:

{'name': 'Mihai Andrei', 'email': 'mihai.andrei@10gen.com', 'username': 'mtandrei'}

Message: SERVER-47223 Verify that $geoNear applies index hint correctly
Branch: master
https://github.com/mongodb/mongo/commit/e1d82874e90ed0cb7fd6898edc54d47c1a42b97c

Comment by Justin Seyster [ 01/Apr/20 ]

Yes, definitely a bug, and one that probably affects master.

Actually, it looks like it's three distinct bugs. In 4.0, a $geoNear DocumentSource operates by dispatching a geoNear command, but it neglects to forward its hint. Furthermore, the geoNear command-when it does have a hint-fails to forward its hint to the query planner. Both of those code paths were removed in 4.2, though, and replaced by a new $geoNear DocumentSource.

Unfortunately, that new code path also seems to have an error (that we have not yet diagnosed) that prevents it from using its hint, meaning that this bug also affects 4.2 and probably 4.4 and master as well.

We will probably want to schedule this for 4.5 and backport to 4.4 and 4.2. If we wanted to correct this behavior on 4.0, we would need to write a new fix.

Comment by Asya Kamsky [ 01/Apr/20 ]

Isn’t it a bug that we discard incompatible hint? Generally we either use the index hinted or error.

Generated at Thu Feb 08 05:13:37 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.