-
Type:
Bug
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
Labels:None
-
ALL
Would like to report an incident which happened on MongoDB 4.4.8 running on Atlas.
We have a large table "reservations" and we did an $in query on the "customer_ids" field for roughly 240 values.
The table has the following indexes available:
- customer_ids: 1, shop_id: 1, deleted_at: 1
- customer_ids: 1
Looking at the log below, for some reason, the QueryPlanner selected an index which did not include customer_ids at all (deleted_at: 1, shop_id: 1, st: 1). This caused a full table scan and apparently locked our database from getting replication updates.
To resolve the issue on our end, I will add an index for (customer_ids: 1, deleted_at: 1). However, given this situation (an "any" with 240 ID keys) I still feel the QueryPlanner should have given preference to the customer_ids field. Please let me know your thoughts.
Full query is below:
{
"type": "command",
"ns": "XXX_production.reservations",
"appName": "XXX-worker-5-ap-northeast-1-production-6454d4c9b7-24x62",
"command": {
"getMore": {
"$numberLong": "8607675802982378262"
},
"collection": "reservations",
"$db": "XXX_production",
"$clusterTime": {
"clusterTime": {
"$timestamp": {
"t": 1628771231,
"i": 37
}
},
"signature": {
"hash": {
"$binary": "cV4XqTMxUfDx6OhwuVf2ombd7VM=",
"$type": "00"
},
"keyId": {
"$numberLong": "6952549997699137540"
}
}
},
"lsid": {
"id": {
"$uuid": "e5a5aabb-3b9e-4e74-b627-5f0545c5bc8c"
}
}
},
"originatingCommand": {
"find": "reservations",
"filter": {
"deleted_at": null,
"customer_ids": {
"$in": [
{
"$oid": "5577e7c9342fc4dacf00049a"
},
{
"$oid": "5465aa078a9c91d514000010"
},
{
"$oid": "54cb29669ab0bb2a660009b4"
},
{
"$oid": "518404e870f78ebe160009c0"
},
{
"$oid": "518404e670f78ebe16000949"
},
{
"$oid": "518404e570f78ebe160008d7"
},
{
"$oid": "53c48fc19ab0bb8b1d000197"
},
{
"$oid": "518404e570f78ebe160008fb"
},
{
"$oid": "518404ea70f78ebe16000a46"
},
{
"$oid": "546d6a3e9ab0bb9b6e000fae"
},
{
"$oid": "546aa7cc342fc4b70800021c"
},
{
"$oid": "518404e670f78ebe16000917"
},
{
"$oid": "53e9896eb81b12ec84000085"
},
{
"$oid": "518404e570f78ebe160008e8"
},
{
"$oid": "551b94c9342fc400770004c5"
},
{
"$oid": "53ee0b0fa0749e863f0000f2"
},
{
"$oid": "548564fa342fc42db3000945"
},
{
"$oid": "518404e470f78ebe160008b1"
},
{
"$oid": "55b8e382a0749ef52b0012b6"
},
{
"$oid": "55cd58a76c0b62afcb0002f2"
},
{
"$oid": "55c43d6d342fc4bf6b00086a"
},
{
"$oid": "55a4d5468a9c9136770000cd"
},
{
"$oid": "55cb25d66c0b62fd82001091"
},
{
"$oid": "561152a9a0749eebe300078f"
},
{
"$oid": "561139158a9c91cd72001384"
},
{
"$oid": "56c6a8846c0b628978000842"
},
{
"$oid": "564ebe7433aed5936e003362"
},
{
"$oid": "555bd7f1a0749ebdb1000065"
},
{
"$oid": "56647e806c0b62d50700102b"
},
{
"$oid": "566a889b342fc4884d002a17"
},
{
"$oid": "5539a6309ab0bb54e3000182"
},
{
"$oid": "56949fd733aed5a958000962"
},
{
"$oid": "555440d98a9c91d9bf0003f6"
},
{
"$oid": "56a87d819ab0bbe38c00061c"
},
{
"$oid": "56c6bd3a6c0b6269700007b0"
},
{
"$oid": "56c2afd5a932367402000827"
},
{
"$oid": "56f20708b81b1274a6000ef1"
},
{
"$oid": "56fcdcc5342fc4fd300040ed"
},
{
"$oid": "5704788a8a9c91f8c60011fb"
},
{
"$oid": "5705f6e8342fc414f5000550"
},
{
"$oid": "570604d3342fc45f150008d8"
},
{
"$oid": "5719b2ed30a9d7dc15000538"
},
{
"$oid": "5732c2d06c0b62fecd001fd1"
},
{
"$oid": "5747be78207a5899ca0024f5"
},
{
"$oid": "5749a5046c0b621ee60063bf"
},
{
"$oid": "5758ca86342fc49ce400047b"
},
{
"$oid": "5768f9c29ab0bb9145000721"
},
{
"$oid": "57a906b3c6c69b3ece000289"
},
{
"$oid": "57a9b72c9ab0bbaca300286a"
},
{
"$oid": "57c7e1c3a93236d2d502bb0a"
},
{
"$oid": "57ce57a0342fc4dd0d00172d"
},
{
"$oid": "57ea05e1b81b124ac70053e5"
},
{
"$oid": "57f5b590207a58fa140005ef"
},
{
"$oid": "5826f4d4b81b12fa1400a0b5"
},
{
"$oid": "5827e2d830a9d749f7009e2a"
},
{
"$oid": "5829b504b81b129352002f47"
},
{
"$oid": "582a8fa4fa23b74bf5004d3e"
},
{
"$oid": "584ebd0cb81b125f96027aeb"
},
{
"$oid": "58819431664bf283bf00cd2b"
},
{
"$oid": "588ee008207a58c458005625"
},
{
"$oid": "58930fe5f619afebf60002a6"
},
{
"$oid": "58bd20e2f619afed09001a95"
},
{
"$oid": "58bfa836342fc437f2000700"
},
{
"$oid": "58c175be664bf20753003d12"
},
{
"$oid": "58c772c433aed5be92000e62"
},
{
"$oid": "58ca37d2207a58fc8b002fcb"
},
{
"$oid": "58e1d999a9323631f9005fbd"
},
{
"$oid": "58e61bdf33aed54cda000c7f"
},
{
"$oid": "58eb270833aed50dfb00027f"
},
{
"$oid": "58ecbb8f9ab0bbb12100083a"
},
{
"$oid": "58eef9e733aed5cc420003cb"
},
{
"$oid": "58f71604016974e3c20002ed"
},
{
"$oid": "58feac6a207a586ee4001a67"
},
{
"$oid": "5900080530a9d7a90f00324e"
},
{
"$oid": "590143b2207a5861d3001098"
},
{
"$oid": "590c491ffa23b7b0f9010a43"
},
{
"$oid": "59102c7db81b12fb4400104d"
},
{
"$oid": "5915072733aed5e2ca000f89"
},
{
"$oid": "59156ee99ab0bb1eee00005d"
},
{
"$oid": "591e4eecc6c69b880401228a"
},
{
"$oid": "5926c806207a58f4f90006e2"
},
{
"$oid": "59282d7f664bf2592b007c49"
},
{
"$oid": "592e6d8b342fc46117000a62"
},
{
"$oid": "5938dd74664bf27e6f04484f"
},
{
"$oid": "593dff86fa23b7718806a672"
},
{
"$oid": "593e4359b81b127ced06d27d"
},
{
"$oid": "594132fd9ab0bbb80f000446"
},
{
"$oid": "594131e7b1b2a8fae2003dc9"
},
{
"$oid": "5942238530a9d7d8fa00267e"
},
{
"$oid": "595601c6342fc4fd9b000699"
},
{
"$oid": "597e7c51342fc4cf5700037a"
},
{
"$oid": "5987c347a0749e172e00054d"
},
{
"$oid": "5989643a6c0b62e71f0003d2"
},
{
"$oid": "598971c0664bf2450c022e17"
},
{
"$oid": "5994e433a0749e6f670000d8"
},
{
"$oid": "599d62cd3a4fafd84e0034af"
},
{
"$oid": "59a680b2207a588e950007b5"
},
{
"$oid": "59ad148933aed5f2e90005cf"
},
{
"$oid": "59adfada01697406c7000664"
},
{
"$oid": "59b9e1d36c0b625289000369"
},
{
"$oid": "59d499c6b81b12198f017339"
},
{
"$oid": "59d5cbd8f619afcd6100143e"
},
{
"$oid": "59e81f7dc02c6cccdb002326"
},
{
"$oid": "59f34e32af2dfff71200015b"
},
{
"$oid": "59f9260543984267ca000a2e"
},
{
"$oid": "5a1bf77b3c496e8b3b0000be"
},
{
"$oid": "5a1e53f0af2dffcd1900006e"
},
{
"$oid": "5a2916c9f94ffd779d02c53f"
},
{
"$oid": "5a291578fc1229ea2602766a"
},
{
"$oid": "5a2926a48e6bd526cc000640"
},
{
"$oid": "5a3b829c705aac56db095be1"
},
{
"$oid": "5a3b854fdf967501d908acf1"
},
{
"$oid": "5a3f20ca8e6bd51454009bf7"
},
{
"$oid": "5a40b5cdfce08787d8003717"
},
{
"$oid": "5a56fffbfce087dad5002733"
},
{
"$oid": "5a619945f94ffde8b9013baf"
},
{
"$oid": "5a65794598a804b5a6000f18"
},
{
"$oid": "5a6a73d28479d27313003ced"
},
{
"$oid": "5a6fcd26cd869d307f003ee5"
},
{
"$oid": "5a73f3c440cab511e8001940"
},
{
"$oid": "5a7a984e8479d2e1e3001bed"
},
{
"$oid": "5a7a9e62d784c0ff3d002220"
},
{
"$oid": "5a7fce5ffce087a79d00959e"
},
{
"$oid": "5a840af2fce087d0d2011715"
},
{
"$oid": "5a854ce2c4088d28ab00078c"
},
{
"$oid": "5a864760c4088d1329000baa"
},
{
"$oid": "5a8a5351fce08754b8006141"
},
{
"$oid": "5a8a5719fce087f315006f9d"
},
{
"$oid": "5a8d6e1298a804f22c003670"
},
{
"$oid": "5a9547b2c4088d3180001890"
},
{
"$oid": "5a9ca1f9be1af73717037eb0"
},
{
"$oid": "5a9e2488be1af7db04006912"
},
{
"$oid": "5aa883b2cd869d4881004dcd"
},
{
"$oid": "5ab098f58e6bd5b37600143e"
},
{
"$oid": "5ab09f258e6bd5d81a007930"
},
{
"$oid": "5ab9bf77c2cf3fa3b9002f11"
},
{
"$oid": "5abc6fdcd5d72c3e1a0005dd"
},
{
"$oid": "5abc906797acdab1e8001955"
},
{
"$oid": "5ac5b0b1c4088d4082000a65"
},
{
"$oid": "5acdb09ac2cf3f6e72003c3a"
},
{
"$oid": "5ace1de88e6bd599b7004a8c"
},
{
"$oid": "5acec2a27102dac338004a5d"
},
{
"$oid": "5afb1cd68e6bd508220014be"
},
{
"$oid": "5b051541d784c0cba100f52a"
},
{
"$oid": "5b0f8820cd869da4a003414c"
},
{
"$oid": "5b1f6dca1437b6fc0b00034b"
},
{
"$oid": "5b2077766ccbcdb3b302aec5"
},
{
"$oid": "5b20904bf1404ab61400e171"
},
{
"$oid": "5b29e761616a3017ce08a954"
},
{
"$oid": "5b2a33b1f1404aafb300308f"
},
{
"$oid": "5b3af81fd38b0edbfd001c99"
},
{
"$oid": "5b4624f61670dcc4be002e59"
},
{
"$oid": "5b4858fa2a892ee308003ceb"
},
{
"$oid": "5b4ed99fa9d215275100635c"
},
{
"$oid": "5b4ed99476d09532a70060e1"
},
{
"$oid": "5b59473f9b92b045b0001583"
},
{
"$oid": "5b5ae3d7a9d21578f00083db"
},
{
"$oid": "5b6167272a892e992601da05"
},
{
"$oid": "5b63b0f22a892e7c1400097f"
},
{
"$oid": "5b67a9b8fab9664f30013370"
},
{
"$oid": "5b72832e05ccbfadb90246c5"
},
{
"$oid": "5b78fe16ad9f54028b0028c2"
},
{
"$oid": "5b7cfeccf1404a08930038d7"
},
{
"$oid": "5b7e38b3f1404a03450023d4"
},
{
"$oid": "5b83d3ccf1404a10a9001472"
},
{
"$oid": "5b8f9ce24af4b82fbe0003b2"
},
{
"$oid": "5b91e52f6dbb1e9e20000b14"
},
{
"$oid": "5b961e616df94a45c80007fc"
},
{
"$oid": "5b9897bd6ecab49b66000003"
},
{
"$oid": "5ba08df56df94a690e00080e"
},
{
"$oid": "5ba34c2eb8b58d2617001a15"
},
{
"$oid": "5ba45a36f6cec42b39000fcc"
},
{
"$oid": "5bac982009161189890002c4"
},
{
"$oid": "5bb448b74af4b82f67000522"
},
{
"$oid": "5bb5dada6df94a394e0010ed"
},
{
"$oid": "5bbffb146df94a29f60008b7"
},
{
"$oid": "5bc698b1fc3c5cdcf2000222"
},
{
"$oid": "5bc6de2cf0644e9142001c1b"
},
{
"$oid": "5bc6de0bf0644e9210001dc0"
},
{
"$oid": "5bd2c278fc3c5c675a0024ec"
},
{
"$oid": "5bdbbb346df94a40490011c6"
},
{
"$oid": "5bdfee18ac6cb1b287004033"
},
{
"$oid": "5be00248f6cec4b284000d85"
},
{
"$oid": "5be122f4cd516505b4000981"
},
{
"$oid": "5be4315afc3c5cc820001def"
},
{
"$oid": "5bf281072c5c61b696000b3a"
},
{
"$oid": "5bf53a2cabb322879e000c8a"
},
{
"$oid": "5bf6300e40b70f719f000dc0"
},
{
"$oid": "5bfb7e9d40b70f53d2000386"
},
{
"$oid": "5bfba65e0ee3820d5f00091f"
},
{
"$oid": "5bffd2635c20eca338002257"
},
{
"$oid": "5c061f745c31ca9b430001c3"
},
{
"$oid": "5c062752b7b9ae46e5000d89"
},
{
"$oid": "5c08ea51dc0e16270c00063e"
},
{
"$oid": "5c09ec1567806d9781000fee"
},
{
"$oid": "5c0a4187c5659d178f0031ae"
},
{
"$oid": "5c0dd51502fffe22a5000050"
},
{
"$oid": "5c0deb0840b70f1b880023cc"
},
{
"$oid": "5c0dec0e40b70f0c55001b6a"
},
{
"$oid": "5c0de9a6465d0a466a000a31"
},
{
"$oid": "5c0df25b5c20ec283c002282"
},
{
"$oid": "5c0e19ec07eb5b2595000a58"
},
{
"$oid": "5c0f40c14661fe1721006aff"
},
{
"$oid": "5c0f4874c533bf46580012e6"
},
{
"$oid": "5c11c5894eee2220f3000149"
},
{
"$oid": "5c11f43f40b70f5224001aed"
},
{
"$oid": "5c12119841ffd86177000ca3"
},
{
"$oid": "5c123416b60bdd4d670024ec"
},
{
"$oid": "5c131e1440b70f59ef0018dc"
},
{
"$oid": "5c16fcbca74543315f00075c"
},
{
"$oid": "5c1728660ee3821ffb000ea4"
},
{
"$oid": "5c189e9b8ccb99fab7001e69"
},
{
"$oid": "5c1af4a00ee382e7f5000115"
},
{
"$oid": "5c1afec340b70f1f21000adc"
},
{
"$oid": "5c1c5dab62f8cb938f001898"
},
{
"$oid": "5c231769c63f7d410c001304"
},
{
"$oid": "5c2321dd58c9051d02004996"
},
{
"$oid": "5c23232f51f3e2dda7002299"
},
{
"$oid": "5c2329e140b70ffab100282a"
},
{
"$oid": "5c25e7177b4016d169000217"
},
{
"$oid": "5c25f35758c905f128009c2e"
},
{
"$oid": "5c25f49158c905f27a00d114"
},
{
"$oid": "5c3076ea415ee3e27b0003ee"
},
{
"$oid": "5c3307da58c90558a80092eb"
},
{
"$oid": "5c3317e14661fe2360000489"
},
{
"$oid": "5c3317fd7ea3800cbf003c07"
},
{
"$oid": "5c3319504661fe23bf00088b"
},
{
"$oid": "5c3456cb506058094b008a76"
},
{
"$oid": "5c355ba441ffd882db0005a2"
},
{
"$oid": "5c356104267f9c34180013f2"
},
{
"$oid": "5c357a870ee3828bf2000c9a"
},
{
"$oid": "5c35873c4661feed6b001e79"
},
{
"$oid": "5c35888658c9052154007d3d"
},
{
"$oid": "5c3589d9c99752c21c00364f"
},
{
"$oid": "5c358a914661feedd5002457"
},
{
"$oid": "5c358b2b58c9052154007fee"
},
{
"$oid": "5c358b954661feed6b00249d"
},
{
"$oid": "5c35b2b00ee382f87e00035f"
},
{
"$oid": "5c35b64d20d972269c0004cc"
},
{
"$oid": "5c36f98f40b70fde4c001260"
},
{
"$oid": "5c36f84541ffd8ad86001729"
},
{
"$oid": "5c37131f1374327f67000b3b"
},
{
"$oid": "5c3801df4661fe58d90037a4"
},
{
"$oid": "5c38042458c90502120023bf"
},
{
"$oid": "5c380398506058130f002455"
},
{
"$oid": "5c381940d66bd74e29000c2a"
},
{
"$oid": "5c3d41b019e429f0cb000be5"
},
{
"$oid": "5c3d41b15c20ec1483000781"
},
{
"$oid": "5c3d822b0ee382ef1f000eb8"
},
{
"$oid": "5c3d8cc807eb5ba3910003f6"
},
{
"$oid": "5c3d8e55daa0cec9ad001764"
},
{
"$oid": "5c3e8f747d74a94047000b85"
},
{
"$oid": "5c3ea1b440b70f5cf5000cdb"
},
{
"$oid": "5c4016816b673a5ae40003cd"
},
{
"$oid": "5c4173e141ffd8812f000967"
},
{
"$oid": "5c4186dc6b673aeaf3000e20"
},
{
"$oid": "5c4431944ab5f8b5a5001803"
},
{
"$oid": "5c46bc8b41ffd849f6000780"
},
{
"$oid": "5c46d7125c20ec037900172f"
},
{
"$oid": "5c47f75a4661fe137d006e26"
},
{
"$oid": "5c480cba1947355ce80005bb"
},
{
"$oid": "5c482782ef2ea65d4e000202"
},
{
"$oid": "5c4827a607eb5b76ae00189e"
},
{
"$oid": "5c483052c812a2585200018a"
},
{
"$oid": "5c484ffec812a2581c000ecb"
},
{
"$oid": "5c496a0e0ee382f84000001b"
},
{
"$oid": "5c4ab6960ee3824094000ab3"
},
{
"$oid": "5c4abba20ee3824094000c09"
},
{
"$oid": "5c4d48f4fd2cb93368000275"
},
{
"$oid": "5c4eba550ee382c36a000bd2"
},
{
"$oid": "5c4fe0b841ffd89490000ba2"
},
{
"$oid": "5c50283dcf1cae84900003c9"
},
{
"$oid": "5c502b2007eb5b9443001665"
},
{
"$oid": "5c5036941387255e320008bb"
},
{
"$oid": "5c50530d40b70f6406002668"
},
{
"$oid": "5c510f070ee382c4ee000490"
},
{
"$oid": "5c510efb40b70fdf9c001070"
},
{
"$oid": "5c5165734406a5585a001c54"
},
{
"$oid": "5c516c364406a5586f002274"
},
{
"$oid": "5c517e08c421ec5973000177"
},
{
"$oid": "5c517e7a0172675bfc0001bc"
},
{
"$oid": "5c517dc307eb5b2657000e7f"
}
]
}
}
},
"planSummary": "IXSCAN \{ deleted_at: 1, shop_id: 1, st: 1 }, IXSCAN \{ deleted_at: 1, shop_id: 1, st: 1 }",
"cursorid": {
"$numberLong": "8607675802982378262"
},
"keysExamined": 23229740,
"docsExamined": 23229740,
"fromMultiPlanner": true,
"numYields": 433327,
"nreturned": 12478,
"reslen": 16776510,
"locks": {
"ReplicationStateTransition": {
"acquireCount": {
"w": 433328
}
},
"Global": {
"acquireCount": {
"r": 433328
}
},
"Database": {
"acquireCount": {
"r": 433328
}
},
"Collection": {
"acquireCount": {
"r": 433328
}
},
"Mutex": {
"acquireCount": {
"r": 1
}
}
},
"storage": {
"data": {
"bytesRead": {
"$numberLong": "543712288884"
},
"bytesWritten": 8159393,
"timeReadingMicros": {
"$numberLong": "2303346467"
},
"timeWritingMicros": 12094801
},
"timeWaitingMicros": {
"cache": 32903002
}
},
"protocol": "op_msg",
"durationMillis": 5104820,
"v": "4.4.8"
}
Edited by MongoDB for formatting purposes
- duplicates
-
SERVER-21697 Plan ranking should take query and index keys into consideration for breaking ties
-
- Closed
-