Тестовая БД
@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
},