Index intersection queries performing the same as COLLSCAN

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 :)

链接地址: http://www.djcxy.com/p/57920.html

上一篇: MongoDb复合索引:基于键的顺序的性能

下一篇: 索引相交查询与COLLSCAN执行相同