Тестовая БД

@Serializable
data class Population(
    @SerialName("Country Code") val code: String,
    @SerialName("Country Name") val name: String,
    @SerialName("Value") val value: Float,
    @SerialName("Year") val year: Int
)
val population = 
  mongoDatabase
    .getCollection<Population>().apply { drop() }

Тестовые данные

// got from https://datahub.io/core/population
val populationJson = Population::class.java
  .getResource("population_json.json")
  .readText()
val populationCol = Json.decodeFromString(
  ListSerializer(Population.serializer()),
  populationJson)
println(populationCol.size)
population.insertMany(populationCol)

15409

Запрос. План

prettyPrintExplain(population.find(Population::year eq 2000))

explainVersion": "1",
"queryPlanner": {
  ...
  "winningPlan": {
  "filter": {"Year": {"$eq": 2000}},
    "stage": "COLLSCAN",
    "direction": "forward"
  },
  "namespace": "test.population",
  "maxIndexedOrSolutionsReached": false,
  "parsedQuery": {"Year": {"$eq": 2000}},
  "maxIndexedAndSolutionsReached": false
},

Запрос. Статистика

prettyPrintExplain(population.find(Population::year eq 2000))

"executionStats": {
  "executionStages": {
    ...
  },
  "totalDocsExamined": 15409,
  "executionSuccess": true,
  "nReturned": 263,
  "totalKeysExamined": 0,
  "executionTimeMillis": 6
},

Запрос с индексом. План.

population.createIndex(Document.parse("{'Year' : 1}"))
prettyPrintExplain(population.find(Population::year eq 2000))

"queryPlanner": {
  ...
  "winningPlan": {
    "stage": "FETCH",
    "inputStage": {
      "keyPattern": {"Year": 1},
      "multiKeyPaths": {"Year": []},
      "stage": "IXSCAN",
      "indexName": "Year_1",
      ...
      "indexBounds": {"Year": ["[2000, 2000]"]},
      "isMultiKey": false,
      "direction": "forward"
    }}, ... }

Запрос с индексом. Статистика.

population.createIndex(Document.parse("{'Year' : 1}"))
prettyPrintExplain(population.find(Population::year eq 2000))

"executionStats": {
  "executionStages": {
    ...
    "inputStage": {
      ...
      "stage": "IXSCAN",
      "keysExamined": 263,
      "executionTimeMillisEstimate": 0, }, },
  "totalDocsExamined": 263,
  "executionSuccess": true,
  "nReturned": 263,
  "totalKeysExamined": 263,
  "executionTimeMillis": 1 },

Сложный запрос с индексом. План.

val bR = and(Population::code eq "RUS", Population::year gt 2000)
prettyPrintExplain(population.find(bR))

"queryPlanner": { 
  ...
  "winningPlan": {
    "filter": {"Country Code": {"$eq": "RUS"}},
    "stage": "FETCH",
    "inputStage": {
      "keyPattern": {"Year": 1},
      "multiKeyPaths": {"Year": []},
      "stage": "IXSCAN",
        ...
    }
  },
  ... 
},

Сложный запрос с индексом. Статистика.

val bR = and(Population::code eq "RUS", Population::year gt 2000)
prettyPrintExplain(population.find(bR))

"executionStats": {
  "executionStages": {    ...
    "inputStage": {
      "keyPattern": {"Year": 1},
      ...
      "keysExamined": 4727,
      "executionTimeMillisEstimate": 0,
    },
    ... },
  "totalDocsExamined": 4727,
  "executionSuccess": true,
  "nReturned": 18,
  "totalKeysExamined": 4727,
  "executionTimeMillis": 8 },

Сложный запрос без индекса. План.

population.dropIndex(yearIndex)
prettyPrintExplain(population.find(bsonRequest))

"queryPlanner": {
  ...
  "winningPlan": {
    "filter": {"$and": [
      {"Country Code": {"$eq": "RUS"}},
      {"Year": {"$gt": 2000}}
    ]},
    "stage": "COLLSCAN",
    "direction": "forward"
  },
  ...
},

Сложный запрос без индекса. Статистика.

population.dropIndex(yearIndex)
prettyPrintExplain(population.find(bsonRequest))

""executionStats": {
  "executionStages": {
    ...
    "stage": "COLLSCAN",
    "docsExamined": 15409,
    ...
  },
  "totalDocsExamined": 15409,
  "executionSuccess": true,
  "nReturned": 18,
  "totalKeysExamined": 0,
  "executionTimeMillis": 11
},

Сложный запрос со сложным индексом. План.

population.createIndex(
  Document.parse("{'Country Code' : 1, 'Year' : 1}"))
prettyPrintExplain(population.find(bsonRequest))

"queryPlanner": { ...
  "winningPlan": {
    "stage": "FETCH",
    "inputStage": {
      "keyPattern": {
        "Year": 1,
        "Country Code": 1
      },
      "multiKeyPaths": {
        "Year": [],
        "Country Code": []
      },
      "stage": "IXSCAN",
      }, ... }, 

Сложный запрос со сложным индексом. Статистика.

population.createIndex(
  Document.parse("{'Country Code' : 1, 'Year' : 1}"))
prettyPrintExplain(population.find(bsonRequest))

"executionStats": {
  "executionStages": {
    ...
    "stage": "IXSCAN",
    "keysExamined": 18,
    ...
  },
  "totalDocsExamined": 18,
  "executionSuccess": true,
  "nReturned": 18,
  "totalKeysExamined": 18,
  "executionTimeMillis": 0
},