Why doesn't MongoDB use cross-index?

Question

I am trying to reproduce the first example of an index crossing instruction ( http://docs.mongodb.org/manual/core/index-intersection/ ), but before the problem: mongo does not use both indexes

My steps:

• Download mongo (3.0.3) and install it
• Run mongod: mongod.exe --dbpath d: \ data (folder is empty)
• Run mongo: mongo.exe

• Add Indexes:

  db.orders.ensureIndex({ qty: 1 }) db.orders.ensureIndex({ item: 1 }) db.orders.getIndexes() [{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "test.orders" }, { "v" : 1, "key" : { "qty" : 1 }, "name" : "qty_1", "ns" : "test.orders" }, { "v" : 1, "key" : { "item" : 1 }, "name" : "item_1", "ns" : "test.orders" }] 

• Check request:

   db.orders.find( { item: "abc123", qty: { $gt: 15 } } ).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.orders", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "item" : { "$eq" : "abc123" } }, { "qty" : { "$gt" : 15 } } ] }, "winningPlan" : { "stage" : "KEEP_MUTATIONS", "inputStage" : { "stage" : "FETCH", "filter" : { "qty" : { "$gt" : 15 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "item" : 1 }, "indexName" : "item_1", "isMultiKey" : false, "direction" : "forward", "indexBounds" : { "item" : [ "[\"abc123\", \"abc123\"]" ] } } } }, "rejectedPlans" : [ { "stage" : "KEEP_MUTATIONS", "inputStage" : { "stage" : "FETCH", "filter" : { "item" : { "$eq" : "abc123" } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "qty" : 1 }, "indexName" : "qty_1", "isMultiKey" : false, "direction" : "forward", "indexBounds" : { "qty" : [ "(15.0, 1.#INF]" ] } } } } ] }, "serverInfo" : { "host" : "localhost", "port" : 27017, "version" : "3.0.3", "gitVersion" : "b40106b36eecd1b4407eb1ad1af6bc60593c6105" }, "ok" : 1 } 

As you can see, winPlan contains only the index item_1. There is rejectPlans that contains a qty_1 index. But there are no plans that contain an index intersection. I know that there are many conditions for choosing a specific index. But in my case, the mango doesn't even plan on this!

Can someone help me?

Answer 1

The SERVER-3071 JIRA section has some information on defining the index, but I cannot say if they are all relevant for 3.0. Anyway:

MongoDB 3.0.2 does not seem to consider index interactions for querying a range. But this will be for point intervals:

 > db.orders.find( { item: {$eq : "abc123"}, qty: { $eq: 15 } } ).explain() ... { "stage" : "FETCH", "inputStage" : { "stage" : "KEEP_MUTATIONS", "inputStage" : { "stage" : "AND_SORTED", "inputStages" : [ { "stage" : "IXSCAN", "keyPattern" : { "qty" : 1 }, "indexName" : "qty_1", "isMultiKey" : false, "direction" : "forward", "indexBounds" : { "qty" : [ "[15.0, 15.0]" ] } }, { "stage" : "IXSCAN", "keyPattern" : { "item" : 1 }, "indexName" : "item_1", "isMultiKey" : false, "direction" : "forward", "indexBounds" : { "item" : [ "[\"abc123\", \"abc123\"]" ] } } ] } 

Answer 2

We host a site with several tenants. So, there is a mongo collection containing information about all customers, for example,

 {customerId: 22, category: "category", region: "region", balance: 23434... } 

After we learned about the Intersection index, we tried to create several indexes of the same field to support queries with different query criteria with variable fields, such as

 { customerId: 22, category: "1" } { customerId: 22, region: "somewhere" } { customerId: 22, balance: {$gt:0} } { customerId: 22, region: {$in: ["R1", "R2"]},balance: {$gt:0} } .... 

But it just turned out that the intersection did not happen, studying "db.collection.explain". Since the fields are limited, we finally made a decision to build a composite index that includes all fields.

 {customerId: 1, category: 1, region: 1, balance:1...} 

And then it turned out that under all the queries, a "large" composite index is used, since "queryId" is in the query.

 { customerId: 22, category: "1" } { customerId: 22, region: "somewhere" } { customerId: 22, balance: {$gt:0} } { customerId: 22, region: {$in: ["R1", "R2"]},balance: {$gt:0} } ....