Query complains about missing 2dsphere-index, but it's there

Question

When I execute the following code (a larger example, boiled down to the essentials)

var mongoose = require("mongoose");

var LocationSchema = new mongoose.Schema({
  userName: String,
  loc: {
    'type': { type: String, enum: "Point", default: "Point" },
    coordinates: { type: [Number] }
  }
})
LocationSchema.index({ category: 1, loc: "2dsphere" });
var Location = mongoose.model("location", LocationSchema);
var mongoDB = 'mongodb://user1:test@ds042417.mlab.com:42417/locationdemo';
mongoose.Promise = global.Promise;
mongoose.connect(mongoDB, { useMongoClient: true });

var testUser = Location({
  userName: "Tester",
  loc: { coordinates: [12.44, 55.69] }
});
testUser.save(function (err) {
  if (err) {
    return console.log("UPPPPs: " + err);
  }
  console.log("User Saved, Try to find him:");

  let query = Location.find({
    loc:
    {
      $near:
      {
        $geometry:
        {
          type: "Point",
          coordinates: [12.50, 55.71]
        },
        $maxDistance: 600000
      }
    }
  })
  query.exec(function (err, docs) {
    if (err) {
      return console.log("Err: " + err);
    }
    console.log("Found: " + JSON.stringify(docs))
  })
});

I get this error:

Err: MongoError: error processing query: ns=locationdemo.locationsTree: GEONEAR field=loc maxdist=600000 isNearSphere=0 Sort: {} Proj: {} planner returned error: unable to find index for $geoNear query

But the index is there (see line 10) and the screenshot from mlab below. What am I doing wrong?:

Answer 1

My first solution to this problem was to create the index via the mLab web-interface which worked like a charm.

I have tried the solution suggested by Neil, but that still fails. The detailed instructions related to indexes, given by Neil however, did point me toward the solution to the problem. It was a timing problem (which you not always see if you run the database locally) related to that my test code did the following: Created the index, created a Location document (which first time will also create the collection), and then in the callback provided by save, I tried to find the user. It seems that the index was not yet created here, which is what gave the error.

If I delay the find method a second, using setTimeout it works fine.

But still, thanks to Neil for valuable information about the right way of using indexes (background) :-)

Answer 2

You are breaking a rule of how you can use a an index in general. Whilst it is true that there is no restriction that a "2dsphere" index be the "first" property in a compound index, it is however very important that your "queries" actually address the first property in order for the index to be selected.

This is covered in Prefixes from the manual on compound indexes. In excerpt:

{ "item": 1, "location": 1, "stock": 1 }

The index has the following index prefixes:

• { item: 1 }
• { item: 1, location: 1 }

For a compound index, MongoDB can use the index to support queries on the index prefixes. As such, MongoDB can use the index for queries on the following fields:

• the item field,
• the item field and the location field,
• the item field and the location field and the stock field.

However, MongoDB cannot use the index to support queries that include the following fields since without the item field, none of the listed fields correspond to a prefix index:

• the location field,
• the stock field, or
• the location and stock fields.

Because your query references "loc" first and does not include "category", the index does not get selected and MongoDB returns the error.

So in order to use the index you have defined, you need to actually query "category" as well. Amending your listing:

var mongoose = require("mongoose");

mongoose.set('debug',true);

var LocationSchema = new mongoose.Schema({
  userName: String,
  category: Number,
  loc: {
    'type': { type: String, enum: "Point", default: "Point" },
    coordinates: { type: [Number] }
  }
})
//LocationSchema.index({ loc: "2dsphere", category: 1 },{ "background": false });
LocationSchema.index({ category: 1, loc: "2dsphere" });

var Location = mongoose.model("location", LocationSchema);
var mongoDB = 'mongodb://localhost/test';
mongoose.Promise = global.Promise;
mongoose.connect(mongoDB, { useMongoClient: true });


var testUser = Location({
  userName: "Tester",
  category: 1,
  loc: { coordinates: [12.44, 55.69] }
});

testUser.save(function (err) {
  if (err) {
    return console.log("UPPPPs: " + err);
  }
  console.log("User Saved, Try to find him:");

  let query = Location.find({
    category: 1,
    loc:
    {
      $near:
      {
        $geometry:
        {
          type: "Point",
          coordinates: [12.50, 55.71]
        },
        $maxDistance: 600000
      }
    }
  })
  query.exec(function (err, docs) {
    if (err) {
      return console.log("Err: " + err);
    }
    console.log("Found: " + JSON.stringify(docs))
  })
});

As long as we include "category" everything is fine:

User Saved, Try to find him:
Mongoose: locations.find({ loc: { '$near': { '$geometry': { type: 'Point', coordinates: [ 12.5, 55.71 ] }, '$maxDistance': 600000 } }, category: 1 }, { fields: {} })
Found: [{"_id":"59f8f87554900a4e555d4e22","userName":"Tester","category":1,"__v":0,"loc":{"coordinates":[12.44,55.69],"type":"Point"}},{"_id":"59f8fabf50fcf54fc3dd01f6","userName":"Tester","category":1,"__v":0,"loc":{"coordinates":[12.44,55.69],"type":"Point"}}]

The alternate case is to simply "prefix" the index with the location. Making sure to drop previous indexes or the collection first:

LocationSchema.index({ loc: "2dsphere", category: 1 },{ "background": false });

As well as you probably should be in the habit of setting "background": true, else you start running into race conditions on unit tests where the index has not finished being created before unit test code attempts to use it.