MongoDB uses only one index on simple query with sorting

Question

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.

Answer 1

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:

• Index Intersection and Sort

• Use Indexes to Sort Query Results

• Optimizing MongoDB Compound Indexes

Answer 2

You can add the "price":1 at the end of your compound index (drop the other index) and mongod will use it for both find and sort phase,

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

The reason that optimizer favors {price:1} vs the compound index is the number of results that the query returns.

The new index will do the job but don't expect to see performance gain if the number of results are close to full collection size.

Its also interesting to test index intersection but I guess due to the number of results returned it will be slow.