Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-47223

geoNear/$geoNear does not apply index hints

    XMLWordPrintable

    Details

    • 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

      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}});
      

        Attachments

          Activity

            People

            Assignee:
            mihai.andrei Mihai Andrei
            Reporter:
            alex.bevilacqua Alex Bevilacqua
            Participants:
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: