title: MongoDB Selecting very bad index
body: <p>I'm running MongoDB version 2.6.7.  I have a collection with about 7 million documents in it.  Each document has a guid field on it which is unique.  Here is an example query which I expect should use the guid index but instead uses some crazy set of queries on a different index:</p>
  
  <pre><code>2015-02-02T23:05:13.349+0000 [conn6716672] query MyDB.persistentObject query: 
  { query: 
  { guid: { $in: [ "some_guid" ] }, 
  ownerId: { $in: [ 0, 1, 1025252577, 994090559, 1317724, 1088410057, 
  1125454113, 1122731922, 994089725, 1122732678, 1317394, 1129549288,1122731600, 
  1312755, 1115032827, 1122461088, 1315404, 1025251224, 1124535620, 1059802590, 
  1124023810, 1125620352, 991434980, 1115022865, 1115031571, 1124514149, 
  1124534953, 1124534178, 1126176542, 1124533461, 149095604, 810047314, 
  1069908537 ] } }, 
  orderby: { updated: -1 }, 
  planSummary: IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 },
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 },
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 }, IXSCAN { ownerId: 1.0, updated: -1.0 }, 
  IXSCAN { ownerId: 1.0, updated: -1.0 } 
  ntoreturn:500 ntoskip:0 nscanned:6739394 nscannedObjects:6739394 
  keyUpdates:0 numYields:7434 locks(micros) r:374790754 nreturned:1 reslen:55 274222ms
  </code></pre>
  
  <p>Here is the list of indexes on that collection:</p>
  
  <pre><code>shard1:PRIMARY&gt; db.MyDB.getIndexes()
  [
      {
          "v" : 1,
          "key" : {
              "_id" : 1
          },
          "name" : "_id_",
          "ns" : "MyDB.persistentObject"
      },
      {
          "v" : 1,
          "key" : {
              "id" : 1,
              "updated" : -1
          },
          "name" : "id_updated_index",
          "ns" : "MyDB.persistentObject",
          "dropDups" : false,
          "sparse" : false
      },
      {
          "v" : 1,
          "key" : {
              "guid" : 1
          },
          "name" : "guid",
          "ns" : "MyDB.persistentObject",
          "dropDups" : false,
          "sparse" : false
      },
      {
          "v" : 1,
          "key" : {
              "ownerId" : 1,
              "updated" : -1
          },
          "name" : "ownerId_1_updated_-1",
          "ns" : "MyDB.persistentObject"
      }
  ]
  </code></pre>
  
  <p>Since there is only one guid requested, but many ownerIds, I would expect the guid index to be used.  If it were used, it would result in <code>nscanned=1</code> and a very quick result. Instead, a very wasteful index is chosen.  This bad index gets selected over and over again on different queries, and the mongo instance becomes totally crippled.</p>
  
  <p>db.stats():</p>
  
  <pre><code>{
      "db" : "MyDB",
      "collections" : 7,
      "objects" : 6761839,
      "avgObjSize" : 496.46051968998376,
      "dataSize" : 3356986104,
      "storageSize" : 3918581760,
      "numExtents" : 28,
      "indexes" : 14,
      "indexSize" : 983450160,
      "fileSize" : 10666115072,
      "nsSizeMB" : 16,
      "dataFileVersion" : {
          "major" : 4,
          "minor" : 5
      },
      "extentFreeList" : {
          "num" : 10,
          "totalSize" : 475508736
          },
      "ok" : 1
  }
  </code></pre>
  
  <p>db.serverStatus().mem:</p>
  
  <pre><code>{
      "bits" : 64,
      "resident" : 946,
      "virtual" : 38232,
      "supported" : true,
      "mapped" : 18409,
      "mappedWithJournal" : 36818
  }
  </code></pre>
  
  <p>An example explain plan which correctly chooses the guid.  Most of the time, when I run explain, it does what I want.  But then something seems to happen once a day where it starts picking the bad index and we end up in a death spiral.</p>
  
  <pre><code>db.persistentObject.find({ guid: { $in: [ "some_guid" ] }, ownerId: { $in: [ 0, 1, 1025252577, 994090559, 1317724, 1088410057, 1125454113, 1122731922, 994089725, 
  1122732678, 1317394, 1129549288,1122731600, 1312755, 1115032827, 1122461088, 1315404, 1025251224, 1124535620, 1059802590, 1124023810, 1125620352, 991434980, 1115022865, 
  1115031571, 1124514149, 1124534953, 1124534178, 1126176542, 1124533461, 149095604, 810047314, 1069908537 ]}, orderby: { updated: -1 }}).explain()
  {
      "cursor" : "BtreeCursor guid",
      "isMultiKey" : false,
      "n" : 0,
      "nscannedObjects" : 1,
      "nscanned" : 1,
      "nscannedObjectsAllPlans" : 4,
      "nscannedAllPlans" : 4,
      "scanAndOrder" : false,
      "indexOnly" : false,
      "nYields" : 1,
      "nChunkSkips" : 0,
      "millis" : 20,
      "indexBounds" : {
          "guid" : [
              [
                  "some_guid",
                  "some_guid"
              ]
          ]
      },
      "server" : "myServer:27018",
      "filterSet" : false,
      "stats" : {
          "type" : "KEEP_MUTATIONS",
          "works" : 3,
          "yields" : 1,
          "unyields" : 1,
          "invalidates" : 0,
          "advanced" : 0,
          "needTime" : 1,
          "needFetch" : 0,
          "isEOF" : 1,
          "children" : [
              {
                  "type" : "FETCH",
                  "works" : 2,
                  "yields" : 1,
                  "unyields" : 1,
                  "invalidates" : 0,
                  "advanced" : 0,
                  "needTime" : 1,
                  "needFetch" : 0,
                  "isEOF" : 1,
                  "alreadyHasObj" : 0,
                  "forcedFetches" : 0,
                  "matchTested" : 0,
                  "children" : [
                      {
                          "type" : "IXSCAN",
                          "works" : 2,
                          "yields" : 1,
                          "unyields" : 1,
                          "invalidates" : 0,
                          "advanced" : 1,
                          "needTime" : 0,
                          "needFetch" : 0,
                          "isEOF" : 1,
                          "keyPattern" : "{ guid: 1 }",
                          "isMultiKey" : 0,
                          "boundsVerbose" : "field #0['guid']: [\"some_guid\", \"some_guid\"]",
                          "yieldMovedCursor" : 0,
                          "dupsTested" : 0,
                          "dupsDropped" : 0,
                          "seenInvalidated" : 0,
                          "matchTested" : 0,
                          "keysExamined" : 1,
                          "children" : [ ]
                      }
                  ]
              }
          ]
      }
  }
  </code></pre>