0

I'm going through the existing indexes on a fairly old MongoDB database which has recently been upgraded from 2.something to 3.4.

The code accessing it is convoluted, automagic, and spaghetti-heavy, plus it allows web-facing frontend code to do arbitrary queries, so it's pretty difficult to get an overview of exactly what queries are made. Doing a bit of profiling, I quickly discovered that a lot of queries were missing indexes, either because logical indexes were missing or misspelled (yeah!), or because someone had assumed that a compound index would support queries on non-prefix parts of them.

Such an example was with a users collection where a compound index, { boxes._id: 1, _deleted: 1 } was missed by a ton of queries filtering by { _deleted: { $exists: false } }.

I wiped it, and created single-field indexes instead, having read up on the index intersection feature which is now part of the version of the MongoDB server backing this app:

db.users.dropIndex({ 'boxes._id': 1, _deleted: 1 });
db.users.ensureIndex({ 'boxes._id': 1 });
db.users.ensureIndex({ '_deleted': 1 });

So far, so good. The dataset here is not very large, so I saw no immediate issue, performance-wise, by dropping the compound index and support index intersection instead. AFAIU from the docs and the interwebs, the performance gains from using compound indexes are mostly felt with really large collections, and for this one, we're looking at about 11k documents.

Simple count queries on either of the two indexed fields yield satisfying responses while hitting the keys:

db.runCommand({ explain: { count: 'users', query: { 'boxes._id': ObjectId('597745846ca2582d8b364c38') }, verbosity: 'executionStats' }})
// "executionTimeMillis" : 8

db.runCommand({ explain: { count: 'users', query: { _deleted: { $exists: false } }, verbosity: 'executionStats' }})
// "executionTimeMillis" : 35

(Note that this is on my old MacBook Air, and compared to query times I'm seeing in the performance log when not using explain, then "normal" response times seem to be less than 25% of those reported with explain in the mongo shell)

However, if I filter on both those fields thus triggering the use of an intersection index, I see a massive performance penalty:

{
  "queryPlanner" : {
    "plannerVersion" : 1,
    "namespace" : "bl.users",
    "indexFilterSet" : false,
    "parsedQuery" : {
      "$and" : [
        {
          "boxes._id" : {
            "$eq" : ObjectId("597745846ca2582d8b364c38")
          }
        },
        {
          "$nor" : [ { "_deleted" : { "$exists" : true } } ]
        }
      ]
    },
    "winningPlan" : {
      "stage" : "FETCH",
      "filter" : {
        "$and" : [
          {
            "$nor" : [ { "_deleted" : { "$exists" : true } } ]
          },
          {
            "boxes._id" : {
              "$eq" : ObjectId("597745846ca2582d8b364c38")
            }
          }
        ]
      },
      "inputStage" : {
        "stage" : "IXSCAN",
        "keyPattern" : { "_deleted" : 1 },
        "indexName" : "_deleted_1",
        "isMultiKey" : false,
        "multiKeyPaths" : { "_deleted" : [ ] },
        "isUnique" : false,
        "isSparse" : false,
        "isPartial" : false,
        "indexVersion" : 2,
        "direction" : "forward",
        "indexBounds" : { "_deleted" : [ "[null, null]" ] }
      }
    },
    "rejectedPlans" : [
      {
        "stage" : "FETCH",
        "filter" : {
          "$nor" : [ { "_deleted" : { "$exists" : true } } ]
        },
        "inputStage" : {
          "stage" : "IXSCAN",
          "keyPattern" : {
            "boxes._id" : 1
          },
          "indexName" : "boxes._id_1",
          "isMultiKey" : true,
          "multiKeyPaths" : {
            "boxes._id" : [
              "boxes"
            ]
          },
          "isUnique" : false,
          "isSparse" : false,
          "isPartial" : false,
          "indexVersion" : 2,
          "direction" : "forward",
          "indexBounds" : {
            "boxes._id" : [
              "[ObjectId('597745846ca2582d8b364c38'), ObjectId('597745846ca2582d8b364c38')]"
            ]
          }
        }
      },
      {
        "stage" : "FETCH",
        "filter" : {
          "$and" : [
            {
              "boxes._id" : {
                "$eq" : ObjectId("597745846ca2582d8b364c38")
              }
            },
            {
              "$nor" : [ { "_deleted" : { "$exists" : true } } ]
            }
          ]
        },
        "inputStage" : {
          "stage" : "AND_SORTED",
          "inputStages" : [
            {
              "stage" : "IXSCAN",
              "keyPattern" : {
                "boxes._id" : 1
              },
              "indexName" : "boxes._id_1",
              "isMultiKey" : true,
              "multiKeyPaths" : {
                "boxes._id" : [
                  "boxes"
                ]
              },
              "isUnique" : false,
              "isSparse" : false,
              "isPartial" : false,
              "indexVersion" : 2,
              "direction" : "forward",
              "indexBounds" : {
                "boxes._id" : [
                  "[ObjectId('597745846ca2582d8b364c38'), ObjectId('597745846ca2582d8b364c38')]"
                ]
              }
            },
            {
              "stage" : "IXSCAN",
              "keyPattern" : { "_deleted" : 1 },
              "indexName" : "_deleted_1",
              "isMultiKey" : false,
              "multiKeyPaths" : { "_deleted" : [ ] },
              "isUnique" : false,
              "isSparse" : false,
              "isPartial" : false,
              "indexVersion" : 2,
              "direction" : "forward",
              "indexBounds" : {
                "_deleted" : [ "[null, null]" ]
              }
            }
          ]
        }
      }
    ]
  },
  "executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 11098,
    "executionTimeMillis" : 731,
    "totalKeysExamined" : 11098,
    "totalDocsExamined" : 11098,
    "executionStages" : {
      "stage" : "FETCH",
      "filter" : {
        "$and" : [
          {
            "$nor" : [ { "_deleted" : { "$exists" : true } } ]
          },
          {
            "boxes._id" : {
              "$eq" : ObjectId("597745846ca2582d8b364c38")
            }
          }
        ]
      },
      "nReturned" : 11098,
      "executionTimeMillisEstimate" : 702,
      "works" : 11099,
      "advanced" : 11098,
      "needTime" : 0,
      "needYield" : 0,
      "saveState" : 127,
      "restoreState" : 127,
      "isEOF" : 1,
      "invalidates" : 0,
      "docsExamined" : 11098,
      "alreadyHasObj" : 0,
      "inputStage" : {
        "stage" : "IXSCAN",
        "nReturned" : 11098,
        "executionTimeMillisEstimate" : 49,
        "works" : 11099,
        "advanced" : 11098,
        "needTime" : 0,
        "needYield" : 0,
        "saveState" : 127,
        "restoreState" : 127,
        "isEOF" : 1,
        "invalidates" : 0,
        "keyPattern" : { "_deleted" : 1 },
        "indexName" : "_deleted_1",
        "isMultiKey" : false,
        "multiKeyPaths" : { "_deleted" : [ ] },
        "isUnique" : false,
        "isSparse" : false,
        "isPartial" : false,
        "indexVersion" : 2,
        "direction" : "forward",
        "indexBounds" : {
          "_deleted" : [ "[null, null]" ]
        },
        "keysExamined" : 11098,
        "seeks" : 1,
        "dupsTested" : 0,
        "dupsDropped" : 0,
        "seenInvalidated" : 0
      }
    },
    "allPlansExecution" : [
      {
        "nReturned" : 101,
        "executionTimeMillisEstimate" : 0,
        "totalKeysExamined" : 101,
        "totalDocsExamined" : 101,
        "executionStages" : {
          "stage" : "FETCH",
          "filter" : {
            "$nor" : [ { "_deleted" : { "$exists" : true } } ]
          },
          "nReturned" : 101,
          "executionTimeMillisEstimate" : 0,
          "works" : 101,
          "advanced" : 101,
          "needTime" : 0,
          "needYield" : 0,
          "saveState" : 3,
          "restoreState" : 3,
          "isEOF" : 0,
          "invalidates" : 0,
          "docsExamined" : 101,
          "alreadyHasObj" : 0,
          "inputStage" : {
            "stage" : "IXSCAN",
            "nReturned" : 101,
            "executionTimeMillisEstimate" : 0,
            "works" : 101,
            "advanced" : 101,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 3,
            "restoreState" : 3,
            "isEOF" : 0,
            "invalidates" : 0,
            "keyPattern" : {
              "boxes._id" : 1
            },
            "indexName" : "boxes._id_1",
            "isMultiKey" : true,
            "multiKeyPaths" : {
              "boxes._id" : [
                "boxes"
              ]
            },
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
              "boxes._id" : [
                "[ObjectId('597745846ca2582d8b364c38'), ObjectId('597745846ca2582d8b364c38')]"
              ]
            },
            "keysExamined" : 101,
            "seeks" : 1,
            "dupsTested" : 101,
            "dupsDropped" : 0,
            "seenInvalidated" : 0
          }
        }
      },
      {
        "nReturned" : 50,
        "executionTimeMillisEstimate" : 12,
        "totalKeysExamined" : 101,
        "totalDocsExamined" : 50,
        "executionStages" : {
          "stage" : "FETCH",
          "filter" : {
            "$and" : [
              {
                "boxes._id" : {
                  "$eq" : ObjectId("597745846ca2582d8b364c38")
                }
              },
              {
                "$nor" : [ { "_deleted" : { "$exists" : true } } ]
              }
            ]
          },
          "nReturned" : 50,
          "executionTimeMillisEstimate" : 12,
          "works" : 101,
          "advanced" : 50,
          "needTime" : 51,
          "needYield" : 0,
          "saveState" : 3,
          "restoreState" : 3,
          "isEOF" : 0,
          "invalidates" : 0,
          "docsExamined" : 50,
          "alreadyHasObj" : 0,
          "inputStage" : {
            "stage" : "AND_SORTED",
            "nReturned" : 50,
            "executionTimeMillisEstimate" : 12,
            "works" : 101,
            "advanced" : 50,
            "needTime" : 51,
            "needYield" : 0,
            "saveState" : 3,
            "restoreState" : 3,
            "isEOF" : 0,
            "invalidates" : 0,
            "flagged" : 0,
            "failedAnd_0" : 0,
            "failedAnd_1" : 0,
            "inputStages" : [
              {
                "stage" : "IXSCAN",
                "nReturned" : 51,
                "executionTimeMillisEstimate" : 0,
                "works" : 51,
                "advanced" : 51,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 3,
                "restoreState" : 3,
                "isEOF" : 0,
                "invalidates" : 0,
                "keyPattern" : {
                  "boxes._id" : 1
                },
                "indexName" : "boxes._id_1",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                  "boxes._id" : [
                    "boxes"
                  ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                  "boxes._id" : [
                    "[ObjectId('597745846ca2582d8b364c38'), ObjectId('597745846ca2582d8b364c38')]"
                  ]
                },
                "keysExamined" : 51,
                "seeks" : 1,
                "dupsTested" : 51,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
              },
              {
                "stage" : "IXSCAN",
                "nReturned" : 50,
                "executionTimeMillisEstimate" : 12,
                "works" : 50,
                "advanced" : 50,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 3,
                "restoreState" : 3,
                "isEOF" : 0,
                "invalidates" : 0,
                "keyPattern" : { "_deleted" : 1 },
                "indexName" : "_deleted_1",
                "isMultiKey" : false,
                "multiKeyPaths" : { "_deleted" : [ ] },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                  "_deleted" : [ "[null, null]" ]
                },
                "keysExamined" : 50,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
              }
            ]
          }
        }
      },
      {
        "nReturned" : 101,
        "executionTimeMillisEstimate" : 11,
        "totalKeysExamined" : 101,
        "totalDocsExamined" : 101,
        "executionStages" : {
          "stage" : "FETCH",
          "filter" : {
            "$and" : [
              {
                "$nor" : [ { "_deleted" : { "$exists" : true } } ]
              },
              {
                "boxes._id" : {
                  "$eq" : ObjectId("597745846ca2582d8b364c38")
                }
              }
            ]
          },
          "nReturned" : 101,
          "executionTimeMillisEstimate" : 11,
          "works" : 101,
          "advanced" : 101,
          "needTime" : 0,
          "needYield" : 0,
          "saveState" : 3,
          "restoreState" : 3,
          "isEOF" : 0,
          "invalidates" : 0,
          "docsExamined" : 101,
          "alreadyHasObj" : 0,
          "inputStage" : {
            "stage" : "IXSCAN",
            "nReturned" : 101,
            "executionTimeMillisEstimate" : 0,
            "works" : 101,
            "advanced" : 101,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 3,
            "restoreState" : 3,
            "isEOF" : 0,
            "invalidates" : 0,
            "keyPattern" : { "_deleted" : 1 },
            "indexName" : "_deleted_1",
            "isMultiKey" : false,
            "multiKeyPaths" : { "_deleted" : [ ] },
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
              "_deleted" : [ "[null, null]" ]
            },
            "keysExamined" : 101,
            "seeks" : 1,
            "dupsTested" : 0,
            "dupsDropped" : 0,
            "seenInvalidated" : 0
          }
        }
      }
    ]
  }
}

The same query hitting the compound index:

{
  "queryPlanner" : {
    "plannerVersion" : 1,
    "namespace" : "bl.users",
    "indexFilterSet" : false,
    "parsedQuery" : {
      "$and" : [
        {
          "boxes._id" : {
            "$eq" : ObjectId("597745846ca2582d8b364c38")
          }
        },
        {
          "$nor" : [ { "_deleted" : { "$exists" : true } } ]
        }
      ]
    },
    "winningPlan" : {
      "stage" : "COUNT",
      "inputStage" : {
        "stage" : "FETCH",
        "filter" : {
          "$nor" : [ { "_deleted" : { "$exists" : true } } ]
        },
        "inputStage" : {
          "stage" : "IXSCAN",
          "keyPattern" : {
            "boxes._id" : 1,
            "_deleted" : 1
          },
          "indexName" : "boxes._id_1__deleted_1",
          "isMultiKey" : true,
          "multiKeyPaths" : {
            "boxes._id" : [
              "boxes"
            ],
            "_deleted" : [ ]
          },
          "isUnique" : false,
          "isSparse" : false,
          "isPartial" : false,
          "indexVersion" : 2,
          "direction" : "forward",
          "indexBounds" : {
            "boxes._id" : [
              "[ObjectId('597745846ca2582d8b364c38'), ObjectId('597745846ca2582d8b364c38')]"
            ],
            "_deleted" : [ "[null, null]" ]
          }
        }
      }
    },
    "rejectedPlans" : [ ]
  },
  "executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 0,
    "executionTimeMillis" : 37,
    "totalKeysExamined" : 11098,
    "totalDocsExamined" : 11098,
    "executionStages" : {
      "stage" : "COUNT",
      "nReturned" : 0,
      "executionTimeMillisEstimate" : 35,
      "works" : 11099,
      "advanced" : 0,
      "needTime" : 11098,
      "needYield" : 0,
      "saveState" : 87,
      "restoreState" : 87,
      "isEOF" : 1,
      "invalidates" : 0,
      "nCounted" : 11098,
      "nSkipped" : 0,
      "inputStage" : {
        "stage" : "FETCH",
        "filter" : {
          "$nor" : [ { "_deleted" : { "$exists" : true } } ]
        },
        "nReturned" : 11098,
        "executionTimeMillisEstimate" : 35,
        "works" : 11099,
        "advanced" : 11098,
        "needTime" : 0,
        "needYield" : 0,
        "saveState" : 87,
        "restoreState" : 87,
        "isEOF" : 1,
        "invalidates" : 0,
        "docsExamined" : 11098,
        "alreadyHasObj" : 0,
        "inputStage" : {
          "stage" : "IXSCAN",
          "nReturned" : 11098,
          "executionTimeMillisEstimate" : 23,
          "works" : 11099,
          "advanced" : 11098,
          "needTime" : 0,
          "needYield" : 0,
          "saveState" : 87,
          "restoreState" : 87,
          "isEOF" : 1,
          "invalidates" : 0,
          "keyPattern" : {
            "boxes._id" : 1,
            "_deleted" : 1
          },
          "indexName" : "boxes._id_1__deleted_1",
          "isMultiKey" : true,
          "multiKeyPaths" : {
            "boxes._id" : [
              "boxes"
            ],
            "_deleted" : [ ]
          },
          "isUnique" : false,
          "isSparse" : false,
          "isPartial" : false,
          "indexVersion" : 2,
          "direction" : "forward",
          "indexBounds" : {
            "boxes._id" : [
              "[ObjectId('597745846ca2582d8b364c38'), ObjectId('597745846ca2582d8b364c38')]"
            ],
            "_deleted" : [ "[null, null]" ]
          },
          "keysExamined" : 11098,
          "seeks" : 1,
          "dupsTested" : 11098,
          "dupsDropped" : 0,
          "seenInvalidated" : 0
        }
      }
    },
    "allPlansExecution" : [ ]
  }
}

Replacing the compound index with just a single index on boxes._id yields pretty much identical performance to having the compound key on both filtered fields:

{
  "queryPlanner" : {
    "plannerVersion" : 1,
    "namespace" : "bl.users",
    "indexFilterSet" : false,
    "parsedQuery" : {
      "$and" : [
        {
          "boxes._id" : {
            "$eq" : ObjectId("597745846ca2582d8b364c38")
          }
        },
        {
          "$nor" : [ { "_deleted" : { "$exists" : true } } ]
        }
      ]
    },
    "winningPlan" : {
      "stage" : "COUNT",
      "inputStage" : {
        "stage" : "FETCH",
        "filter" : {
          "$nor" : [ { "_deleted" : { "$exists" : true } } ]
        },
        "inputStage" : {
          "stage" : "IXSCAN",
          "keyPattern" : {
            "boxes._id" : 1
          },
          "indexName" : "boxes._id_1",
          "isMultiKey" : true,
          "multiKeyPaths" : {
            "boxes._id" : [
              "boxes"
            ]
          },
          "isUnique" : false,
          "isSparse" : false,
          "isPartial" : false,
          "indexVersion" : 2,
          "direction" : "forward",
          "indexBounds" : {
            "boxes._id" : [
              "[ObjectId('597745846ca2582d8b364c38'), ObjectId('597745846ca2582d8b364c38')]"
            ]
          }
        }
      }
    },
    "rejectedPlans" : [ ]
  },
  "executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 0,
    "executionTimeMillis" : 31,
    "totalKeysExamined" : 11098,
    "totalDocsExamined" : 11098,
    "executionStages" : {
      "stage" : "COUNT",
      "nReturned" : 0,
      "executionTimeMillisEstimate" : 34,
      "works" : 11099,
      "advanced" : 0,
      "needTime" : 11098,
      "needYield" : 0,
      "saveState" : 88,
      "restoreState" : 88,
      "isEOF" : 1,
      "invalidates" : 0,
      "nCounted" : 11098,
      "nSkipped" : 0,
      "inputStage" : {
        "stage" : "FETCH",
        "filter" : {
          "$nor" : [ { "_deleted" : { "$exists" : true } } ]
        },
        "nReturned" : 11098,
        "executionTimeMillisEstimate" : 34,
        "works" : 11099,
        "advanced" : 11098,
        "needTime" : 0,
        "needYield" : 0,
        "saveState" : 88,
        "restoreState" : 88,
        "isEOF" : 1,
        "invalidates" : 0,
        "docsExamined" : 11098,
        "alreadyHasObj" : 0,
        "inputStage" : {
          "stage" : "IXSCAN",
          "nReturned" : 11098,
          "executionTimeMillisEstimate" : 11,
          "works" : 11099,
          "advanced" : 11098,
          "needTime" : 0,
          "needYield" : 0,
          "saveState" : 88,
          "restoreState" : 88,
          "isEOF" : 1,
          "invalidates" : 0,
          "keyPattern" : {
            "boxes._id" : 1
          },
          "indexName" : "boxes._id_1",
          "isMultiKey" : true,
          "multiKeyPaths" : {
            "boxes._id" : [
              "boxes"
            ]
          },
          "isUnique" : false,
          "isSparse" : false,
          "isPartial" : false,
          "indexVersion" : 2,
          "direction" : "forward",
          "indexBounds" : {
            "boxes._id" : [
              "[ObjectId('597745846ca2582d8b364c38'), ObjectId('597745846ca2582d8b364c38')]"
            ]
          },
          "keysExamined" : 11098,
          "seeks" : 1,
          "dupsTested" : 11098,
          "dupsDropped" : 0,
          "seenInvalidated" : 0
        }
      }
    },
    "allPlansExecution" : [ ]
  }
}

Finally, dropping all indexes on these fields, triggering a COLLSCAN yields performance pretty much identical to the index intersection one:

{
  "queryPlanner" : {
    "plannerVersion" : 1,
    "namespace" : "bl.users",
    "indexFilterSet" : false,
    "parsedQuery" : {
      "$and" : [
        {
          "ideaboxes._id" : {
            "$eq" : ObjectId("597745846ca2582d8b364c38")
          }
        },
        {
          "$nor" : [ { "_deleted" : { "$exists" : true } } ]
        }
      ]
    },
    "winningPlan" : {
      "stage" : "COUNT",
      "inputStage" : {
        "stage" : "COLLSCAN",
        "filter" : {
          "$and" : [
            {
              "ideaboxes._id" : {
                "$eq" : ObjectId("597745846ca2582d8b364c38")
              }
            },
            {
              "$nor" : [ { "_deleted" : { "$exists" : true } } ]
            }
          ]
        },
        "direction" : "forward"
      }
    },
    "rejectedPlans" : [ ]
  },
  "executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 0,
    "executionTimeMillis" : 848,
    "totalKeysExamined" : 0,
    "totalDocsExamined" : 11109,
    "executionStages" : {
      "stage" : "COUNT",
      "nReturned" : 0,
      "executionTimeMillisEstimate" : 847,
      "works" : 11111,
      "advanced" : 0,
      "needTime" : 11110,
      "needYield" : 0,
      "saveState" : 123,
      "restoreState" : 123,
      "isEOF" : 1,
      "invalidates" : 0,
      "nCounted" : 11098,
      "nSkipped" : 0,
      "inputStage" : {
        "stage" : "COLLSCAN",
        "filter" : {
          "$and" : [
            {
              "ideaboxes._id" : {
                "$eq" : ObjectId("597745846ca2582d8b364c38")
              }
            },
            {
              "$nor" : [ { "_deleted" : { "$exists" : true } } ]
            }
          ]
        },
        "nReturned" : 11098,
        "executionTimeMillisEstimate" : 847,
        "works" : 11111,
        "advanced" : 11098,
        "needTime" : 12,
        "needYield" : 0,
        "saveState" : 123,
        "restoreState" : 123,
        "isEOF" : 1,
        "invalidates" : 0,
        "direction" : "forward",
        "docsExamined" : 11109
      }
    },
    "allPlansExecution" : [ ]
  }
}

Surely getting COLLSCAN performance with index intersection queries can't be right? I admit I'm very new with Mongo, so I'm also quite ignorant when trying to decipher this report — the answer may be staring me in the face.

I've tested the same queries on Mongo 3.2 on an ObjectRocket instance, and while their servers are much faster than my ageing laptop, they exhibit the same numbers relatively speaking.

Any ideas why this is happening?

Thank you :)

DanielSmedegaardBuus
  • 977
  • 1
  • 10
  • 18
  • I don't see any query that uses intersection. There is only 1 with rejected `AND_SORTED` stage. – Alex Blex Sep 26 '17 at 09:37
  • Queries like `{ _deleted: { $exists: false } }` will work way better with sparse indexes. – Alex Blex Sep 26 '17 at 09:39
  • You still can use 2 indexes: `{ 'boxes._id': 1, _deleted: 1 }` and `{ _deleted: 1 }` so you don't need to bother with intersection. The planer uses intersection when both indexes are highly selective. – Alex Blex Sep 26 '17 at 09:42
  • Thanks for the comments, @AlexBlex :) It seems I'm even dumber at reading the reports than I thought — your pointer helped me read it better, and I see what you're saying. I also tried making the `_deleted` index sparse as you suggested, and *blammo* — instantly brought the performance on par with the compound index'. I'm gonna read up on sparse indexes. If you want the credit, you can add an answer and I'll mark it as the correct one, otherwise I'll do it myself later :) Thanks man! – DanielSmedegaardBuus Sep 26 '17 at 10:00
  • I was about to ask for you to perhaps provide an explanation why making the index sparse made it perform better with this query, as I couldn't see from the mongo docs why this was the case. I then discovered that the reason the query was faster is because MongoDB ignores the sparse index altogether, and the most significant performance gain comes from the index on `boxes._id`. This still leaves me wondering though, why and what exactly mongo chooses to do in the case of the two "intersectable" indexes, and why when rejecting those two plans, it doesn't fall back on using the `boxes._id` index? – DanielSmedegaardBuus Sep 26 '17 at 10:12
  • Have a look at https://stackoverflow.com/a/46327839/1110423 regarding sparse indexes, and https://jira.mongodb.org/browse/SERVER-3071?focusedCommentId=508454&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-508454 regarding index intersection. As [one said](https://stackoverflow.com/questions/30626726/why-mongodb-doesnt-use-index-intersection#comment69804538_30628389) "index intersection seems to never happens in real life queries". Not in 11k docs collection anyway. – Alex Blex Sep 26 '17 at 10:26
  • Thanks for those links :) I'm getting a bit depressed here, though, as it slowly becomes apparent that I can't do what I was hoping for after discovering this "index intersection" support when taking all those caveats into account. This app sooo should have been backed by a relative DBMS in the first place — its data model and data set size just screams "Imma need relationalz!" Well, thank you for all your input, Alex. Much appreciated :) [ EDIT: Still can't figure out, though, why the planner likes to fall back on the worst possible strategy :'( ] – DanielSmedegaardBuus Sep 26 '17 at 11:11

0 Answers0