All geek questions in one place

Why does one mongoDB join index affect another composite index?

From what I read from mongodb docs, the query uses only one index. However, I find that the presence of some other composite indexes affects the quality of this query. Here is an example:

db.products.ensureIndex ({'b': 1, 'l.d': 1, 'l.i': 1})

db.products.find ({'b': {$ in: b.ct}, 'ld': {$ lt: d}}). limit (24) .sort ({'li': 1}). explain ()

{ "cursor" : "BtreeCursor b_1_l.d_1_l.i_1 multi", "isMultiKey" : true, "n" : 24, "nscannedObjects" : 1079, "nscanned" : 1102, "nscannedObjectsAllPlans" : 1182, "nscannedAllPlans" : 1205, "scanAndOrder" : true, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, ....}

db.products.ensureIndex ({'l.i': 1, 'b': 1, 'l.d': 1})

db.products.find ({'b': {$ in: b.ct}, 'ld': {$ lt: d}}). limit (24) .sort ({'li': 1}). explain ()

 { "cursor" : "BtreeCursor b_1_l.d_1_l.i_1 multi", "isMultiKey" : true, "n" : 24, "nscannedObjects" : 614, "nscanned" : 624, "nscannedObjectsAllPlans" : 1283, "nscannedAllPlans" : 1875, "scanAndOrder" : true, "indexOnly" : false, "nYields" : 1, "nChunkSkips" : 0, ....}

The nscanned value decreases by almost half. Why?

==================================================== ===============

Based on the comments, I updated my sequence of commands to provide more details. Please note that index names are changed because I changed the database. The result is the same. Two indexes are better, but why?

db.products.stats ()

 { "ns" : "mytest.products", "count" : 209607, "size" : 90155636, "avgObjSize" : 430.11748653432375, "storageSize" : 123936768, "numExtents" : 11, "nindexes" : 1, "lastExtentSize" : 37625856, "paddingFactor" : 1, "systemFlags" : 0, "userFlags" : 0, "totalIndexSize" : 5927600, "indexSizes" : { "_id_" : 5927600 }, "ok" : 1 }

b.ct

 [ 2020, 3564969011, 2021, 15762981, 271619011, 2023, 2024, 2027, 3825141, 505092, 2025, 2028, 10825721, 2080, 2026, 2085, 2029, 2030, 2032, 3564970011, 2081, 2082, 2083, 2084, 271621011, 2087

]

d

ISODate ("2012-11-30T00: 00: 00Z")

db.products.ensureIndex ({'b': 1, 'd': 1, 'i': 1})

db.products.stats ()

 { "ns" : "mytest.products", "count" : 209607, "size" : 90155636, "avgObjSize" : 430.11748653432375, "storageSize" : 123936768, "numExtents" : 11, "nindexes" : 2, "lastExtentSize" : 37625856, "paddingFactor" : 1, "systemFlags" : 0, "userFlags" : 0, "totalIndexSize" : 22614816, "indexSizes" : { "_id_" : 5927600, "b_1_d_1_i_1" : 16687216 }, "ok" : 1 }

db.products.find ({'b': {$ in: b.ct}, 'd': {$ lt: d}}). limit (24) .sort ({'i': 1}). explain ()

 { "cursor" : "BtreeCursor b_1_d_1_i_1 multi", "isMultiKey" : true, "n" : 24, "nscannedObjects" : 1294, "nscanned" : 1300, "nscannedObjectsAllPlans" : 1395, "nscannedAllPlans" : 1401, "scanAndOrder" : true, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 12, "indexBounds" : { "b" : [ [ 2020, 2020 ], [ 2021, 2021 ], [ 2023, 2023 ], [ 2024, 2024 ], [ 2025, 2025 ], [ 2026, 2026 ], [ 2027, 2027 ], [ 2028, 2028 ], [ 2029, 2029 ], [ 2030, 2030 ], [ 2032, 2032 ], [ 2080, 2080 ], [ 2081, 2081 ], [ 2082, 2082 ], [ 2083, 2083 ], [ 2084, 2084 ], [ 2085, 2085 ], [ 2087, 2087 ], [ 505092, 505092 ], [ 3825141, 3825141 ], [ 10825721, 10825721 ], [ 15762981, 15762981 ], [ 271619011, 271619011 ], [ 271621011, 271621011 ], [ 3564969011, 3564969011 ], [ 3564970011, 3564970011 ] ], "d" : [ [ true, ISODate("2012-11-30T00:00:00Z") ] ], "i" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] }, "server" : "li91-182:27017" }

db.products.ensureIndex ({'i': 1, 'b': 1, 'd': 1})

db.products.stats ()

 { "ns" : "mytest.products", "count" : 209607, "size" : 90155636, "avgObjSize" : 430.11748653432375, "storageSize" : 123936768, "numExtents" : 11, "nindexes" : 3, "lastExtentSize" : 37625856, "paddingFactor" : 1, "systemFlags" : 0, "userFlags" : 0, "totalIndexSize" : 39302032, "indexSizes" : { "_id_" : 5927600, "b_1_d_1_i_1" : 16687216, "i_1_b_1_d_1" : 16687216 }, "ok" : 1 }

db.products.find ({'b': {$ in: b.ct}, 'd': {$ lt: d}}). limit (24) .sort ({'i': 1}). explain ()

 { "cursor" : "BtreeCursor b_1_d_1_i_1 multi", "isMultiKey" : true, "n" : 24, "nscannedObjects" : 206, "nscanned" : 206, "nscannedObjectsAllPlans" : 445, "nscannedAllPlans" : 619, "scanAndOrder" : true, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 6, "indexBounds" : { "b" : [ [ 2020, 2020 ], [ 2021, 2021 ], [ 2023, 2023 ], [ 2024, 2024 ], [ 2025, 2025 ], [ 2026, 2026 ], [ 2027, 2027 ], [ 2028, 2028 ], [ 2029, 2029 ], [ 2030, 2030 ], [ 2032, 2032 ], [ 2080, 2080 ], [ 2081, 2081 ], [ 2082, 2082 ], [ 2083, 2083 ], [ 2084, 2084 ], [ 2085, 2085 ], [ 2087, 2087 ], [ 505092, 505092 ], [ 3825141, 3825141 ], [ 10825721, 10825721 ], [ 15762981, 15762981 ], [ 271619011, 271619011 ], [ 271621011, 271621011 ], [ 3564969011, 3564969011 ], [ 3564970011, 3564970011 ] ], "d" : [ [ true, ISODate("2012-11-30T00:00:00Z") ] ], "i" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] }, "server" : "li91-182:27017" }

db.products.getIndexes ()

 [ { "v" : 1, "key" : { "_id" : 1 }, "ns" : "mytest.products", "name" : "_id_" }, { "v" : 1, "key" : { "b" : 1, "d" : 1, "i" : 1 }, "ns" : "mytest.products", "name" : "b_1_d_1_i_1" }, { "v" : 1, "key" : { "i" : 1, "b" : 1, "d" : 1 }, "ns" : "mytest.products", "name" : "i_1_b_1_d_1" } ]

db.products.dropIndex ({'i': 1, 'b': 1, 'd': 1}) {"nIndexesWas": 3, "ok": 1}

db.products.getIndexes ()

 [ { "v" : 1, "key" : { "_id" : 1 }, "ns" : "mytest.products", "name" : "_id_" }, { "v" : 1, "key" : { "b" : 1, "d" : 1, "i" : 1 }, "ns" : "mytest.products", "name" : "b_1_d_1_i_1" } ]

db.products.find ({'b': {$ in: b.ct}, 'd': {$ lt: d}}). limit (24) .sort ({'i': 1}). explain ()

 { "cursor" : "BtreeCursor b_1_d_1_i_1 multi", "isMultiKey" : true, "n" : 24, "nscannedObjects" : 1294, "nscanned" : 1300, "nscannedObjectsAllPlans" : 1395, "nscannedAllPlans" : 1401, "scanAndOrder" : true, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 131, "indexBounds" : { "b" : [ [ 2020, 2020 ], [ 2021, 2021 ], [ 2023, 2023 ], [ 2024, 2024 ], [ 2025, 2025 ], [ 2026, 2026 ], [ 2027, 2027 ], [ 2028, 2028 ], [ 2029, 2029 ], [ 2030, 2030 ], [ 2032, 2032 ], [ 2080, 2080 ], [ 2081, 2081 ], [ 2082, 2082 ], [ 2083, 2083 ], [ 2084, 2084 ], [ 2085, 2085 ], [ 2087, 2087 ], [ 505092, 505092 ], [ 3825141, 3825141 ], [ 10825721, 10825721 ], [ 15762981, 15762981 ], [ 271619011, 271619011 ], [ 271621011, 271621011 ], [ 3564969011, 3564969011 ], [ 3564970011, 3564970011 ] ], "d" : [ [ true, ISODate("2012-11-30T00:00:00Z") ] ], "i" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] }, "server" : "li91-182:27017" }
+4
source share
1 answer

According to 10gen, BSON is a binary-coded serialization of JSON-like documents . However, the order of the fields in BSON documents does matter:

 > db.things.insert({b:1,d:1,i:1}); > db.things.insert({i:2,b:2,d:2}); > db.things.insert({d:3,i:3,b:3}); > db.things.find(); { "_id" : ObjectId("50904ee4875db529686c5775"), "b" : 1, "d" : 1, "i" : 1 } { "_id" : ObjectId("50904ef0875db529686c5776"), "i" : 2, "b" : 2, "d" : 2 } { "_id" : ObjectId("50904efc875db529686c5777"), "d" : 3, "i" : 3, "b" : 3 }

So, whenever you create an index with db.products.ensureIndex({'b' : 1, 'ld' : 1, 'li' : 1}) , and then with db.products.ensureIndex({'li' :1, 'b' : 1, 'ld' : 1}) , you get 2 indexes with different field orders. This can be verified as a result of db.products.getIndexes() , which you have kindly provided:

 [{ "v" : 1, "key" : { "b" : 1, "d" : 1, "i" : 1 }, "ns" : "mytest.products", "name" : "b_1_d_1_i_1" }, { "v" : 1, "key" : { "i" : 1, "b" : 1, "d" : 1 }, "ns" : "mytest.products", "name" : "i_1_b_1_d_1" }]

And if the correct order of the fields, obviously, can lead to another nscanned value - the number of objects (including nodes of the index tree) that will be scanned

The number of items (documents or index entries). Elements can be objects or index keys.

+2
source

Source: https://habr.com/ru/post/1439621/

More articles:


All Articles