[SERVER-59337] Bad index selection when using $in Created: 13/Aug/21  Updated: 11/Oct/21  Resolved: 11/Oct/21

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Johnny Shields Assignee: Eric Sedor
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-21697 Plan ranking should take query and in... Closed
Operating System: ALL
Participants:

 Description   

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



 Comments   
Comment by Eric Sedor [ 11/Oct/21 ]

Hi again Johnny,

Thanks for your patience. The planner can be surprising at times, and we ​do want to improve behavior in cases where multiple indexes are viable and specific queried values are leading to poor plan choices in unpredictable ways. As you've probably already found, hints are often necessary in these scenarios, as is attention to keeping the shared prefixes among indexes to a minimum. When reporting such issues, the output of explain("allPlansExecution") is most helpful when it comes to distinguishing among some identified weaknesses.

In this case a colleague has pointed me to SERVER-21697 which when plans tie would allow the planner to prefer indexes based fields are shared between the index specification and the query filter. A related improvement, SERVER-14423 should reduce ties or reduce the impact of ties in some cases.

Comment by Johnny Shields [ 18/Aug/21 ]

Hi Eric, I've been using MongoDB for 8 years. This QueryPlanner behavior is strange. Unfortunately I don't have the time to start a thread on MongoDB forums and I've already taken countermeasures on my side, but I really think someone involved in QueryPlanner design should look at this edge-case; I raised this issue for your benefit. If no one wants to look at it, then fine to close.

Comment by Eric Sedor [ 18/Aug/21 ]

Hi shields@tablecheck.com,

One thing I can quickly suggest is that you will want to dig into the explain plan for the query to reason about why a given selection occurred. The best place to start a discussion around this will be by posting on the MongoDB Developer Community Forums. The community there can help with initial considerations around query planning, reading explain plans, and ruling out other common reasons for certain plan selections.

If the discussion there leads you to suspect a bug in the MongoDB server, then we'd want to investigate it as a possible bug here in the SERVER project.

Sincerely,
Eric

Generated at Thu Feb 08 05:46:59 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.