MongoDB uses only one index on simple query with sorting

indexindex-tuningmongodbmongodb-3.0

So the problem is that I'm having a huge collection and I'm trying to run a query that filters and sorts on only fields that have indexes on them.

I have these two indexes:

db.getCollection('product').createIndex({"_type": 1, "_collectivity": 1, "own_risk": 1});
db.getCollection('product').createIndex({"price": 1});

And that's the simple query I am running:

db.getCollection('product').find({
    "_type": "healthcare2",
    "_collectivity": null,
    "own_risk": 375
}).sort({price: 1}).explain()

And here's the winningPlan for this query:

{
  "winningPlan": {
    "stage": "FETCH",
    "filter": {
      "$and": [
        {
          "_collectivity": {
            "$eq": null
          }
        },
        {
          "_type": {
            "$eq": "healthcare2"
          }
        },
        {
          "own_risk": {
            "$eq": 375.0000000000000000
          }
        }
      ]
    },
    "inputStage": {
      "stage": "IXSCAN",
      "keyPattern": {
        "price": 1
      },
      "indexName": "price_1",
      "isMultiKey": false,
      "direction": "forward",
      "indexBounds": {
        "price": [
          "[MinKey, MaxKey]"
        ]
      }
    }
  }
}

So it scans through all the collection ignoring all indexes. But if I try to hint:

db.getCollection('product').find({
    "_type": "healthcare2",
    "_collectivity": null,
    "own_risk": 375
}).sort({price: 1}).hint({"_type": 1, "_collectivity": 1, "own_risk": 1})

Mongo will return me an error…

error: {
    "$err" : "Executor error: Overflow sort stage buffered data usage of 33686190 bytes exceeds internal limit of 33554432 bytes",
    "code" : 17144
}

Mongo will not try to use the price index after it sorts out all documents with the first index.

So… What is my problem? Or Mongo's? How do I make MongoDB use more than one index in one query?

My MongoDB is 3.0.6 and running in Ubuntu within Vagrant.

Best Answer

MongoDB 2.6+ will consider index intersection in cases where multiple indexes might improve the query performance, however for sorting the index has to have keys in common with the query predicate. In your scenario, this means an index on {price:1} cannot be used for index intersection unless price is included in the query.

So it scans through all the collection ignoring all indexes.

What's actually happening according to the query explain() is that the price:1 index is being used to return the results in sorted order via an index scan (IXSCAN). Your compound index would require an in-memory sort of the result set, which may be larger than the size of the in-memory sort buffer (32MB) as you discovered by trying to hint that index. If you can add a limit to your query so the results are smaller than 32MB, the compound index may be usable but not ideal.

For your query, the ideal index to support the desired sort order (and an unlimited set of results) is a compound index including the price:

db.product.createIndex({"_type": 1, "_collectivity": 1, "own_risk": 1, "price": 1});

For more information, see:

Related Question