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.