title: Poor performance using $lte and $gte MongoDB
body: <p>I am currently working with MongoDB 3.6.1, and I am facing an strange behavior using <strong>compound index</strong>. I have a collection with around 2 millions of documents, and here is the structure of one document: </p>
  
  <pre><code>{
  "_id" : ObjectId("5af478b408e0f42946ff14ec"),
  "IpStart" : NumberLong(50331649),
  "IpEnd" : NumberLong(54525951),
  "Latitude" : "47.634",
  "Longitude" : "-122.342",
  "PostalCode" : "98109",
  "MarketId" : 308,
  "City" : "Seattle",
  "State" : "WA"
  }
  </code></pre>
  
  <p>And my indexes look like: </p>
  
  <pre><code>[
          {
              "v" : 2,
              "key" : {
                  "_id" : 1
              },
              "name" : "_id_",
              "ns" : "locations_fact.IpRangeLocations"
          },
          {
              "v" : 2,
              "unique" : true,
              "key" : {
                  "IpStart" : 1,
                  "IpEnd" : 1
              },
              "name" : "IpStart_1_IpEnd_1",
              "ns" : "locations_fact.IpRangeLocations"
          }
  ]
  </code></pre>
  
  <p>When I made this query:</p>
  
  <pre><code>db.IpRangeLocations.find({"IpStart":{$lt:1209192009}, "IpEnd":{$gt:1209192009}}).explain("executionStats")
  </code></pre>
  
  <p>I got a response of 5 secs, when it is checking only 955043 keys.</p>
  
  <p>Here is the response with the explain option enable:</p>
  
  <pre><code>{
      "queryPlanner" : {
          "plannerVersion" : 1,
          "namespace" : "locations_fact.IpRangeLocations",
          "indexFilterSet" : false,
          "parsedQuery" : {
              "$and" : [ 
                  {
                      "IpStart" : {
                          "$lt" : 1209192009.0
                      }
                  }, 
                  {
                      "IpEnd" : {
                          "$gt" : 1209192009.0
                      }
                  }
              ]
          },
          "winningPlan" : {
              "stage" : "FETCH",
              "inputStage" : {
                  "stage" : "IXSCAN",
                  "keyPattern" : {
                      "IpStart" : 1,
                      "IpEnd" : 1
                  },
                  "indexName" : "IpStart_1_IpEnd_1",
                  "isMultiKey" : false,
                  "multiKeyPaths" : {
                      "IpStart" : [],
                      "IpEnd" : []
                  },
                  "isUnique" : true,
                  "isSparse" : false,
                  "isPartial" : false,
                  "indexVersion" : 2,
                  "direction" : "forward",
                  "indexBounds" : {
                      "IpStart" : [ 
                          "[-inf.0, 1209192009.0)"
                      ],
                      "IpEnd" : [ 
                          "(1209192009.0, inf.0]"
                      ]
                  }
              }
          },
          "rejectedPlans" : []
      },
      "executionStats" : {
          "executionSuccess" : true,
          "nReturned" : 1,
          "executionTimeMillis" : 5019,
          "totalKeysExamined" : 955043,
          "totalDocsExamined" : 1,
          "executionStages" : {
              "stage" : "FETCH",
              "nReturned" : 1,
              "executionTimeMillisEstimate" : 4823,
              "works" : 955043,
              "advanced" : 1,
              "needTime" : 955041,
              "needYield" : 0,
              "saveState" : 7530,
              "restoreState" : 7530,
              "isEOF" : 1,
              "invalidates" : 0,
              "docsExamined" : 1,
              "alreadyHasObj" : 0,
              "inputStage" : {
                  "stage" : "IXSCAN",
                  "nReturned" : 1,
                  "executionTimeMillisEstimate" : 4773,
                  "works" : 955043,
                  "advanced" : 1,
                  "needTime" : 955041,
                  "needYield" : 0,
                  "saveState" : 7530,
                  "restoreState" : 7530,
                  "isEOF" : 1,
                  "invalidates" : 0,
                  "keyPattern" : {
                      "IpStart" : 1,
                      "IpEnd" : 1
                  },
                  "indexName" : "IpStart_1_IpEnd_1",
                  "isMultiKey" : false,
                  "multiKeyPaths" : {
                      "IpStart" : [],
                      "IpEnd" : []
                  },
                  "isUnique" : true,
                  "isSparse" : false,
                  "isPartial" : false,
                  "indexVersion" : 2,
                  "direction" : "forward",
                  "indexBounds" : {
                      "IpStart" : [ 
                          "[-inf.0, 1209192009.0)"
                      ],
                      "IpEnd" : [ 
                          "(1209192009.0, inf.0]"
                      ]
                  },
                  "keysExamined" : 955043,
                  "seeks" : 955042,
                  "dupsTested" : 0,
                  "dupsDropped" : 0,
                  "seenInvalidated" : 0
              }
          }
      },
      "serverInfo" : {
          "host" : "SOLANOD",
          "port" : 27017,
          "version" : "3.6.1",
          "gitVersion" : "025d4f4fe61efd1fb6f0005be20cb45a004093d1"
      },
      "ok" : 1.0
  }
  </code></pre>
  
  <p>Then when I removed the compound index, I got better results (1.932 sec), but it is checking 1726879 documents (more that using Index)</p>
  
  <pre><code>{
      "queryPlanner" : {
          "plannerVersion" : 1,
          "namespace" : "locations_fact.IpRangeLocations",
          "indexFilterSet" : false,
          "parsedQuery" : {
              "$and" : [ 
                  {
                      "IpStart" : {
                          "$lt" : 1209192009.0
                      }
                  }, 
                  {
                      "IpEnd" : {
                          "$gt" : 1209192009.0
                      }
                  }
              ]
          },
          "winningPlan" : {
              "stage" : "COLLSCAN",
              "filter" : {
                  "$and" : [ 
                      {
                          "IpStart" : {
                              "$lt" : 1209192009.0
                          }
                      }, 
                      {
                          "IpEnd" : {
                              "$gt" : 1209192009.0
                          }
                      }
                  ]
              },
              "direction" : "forward"
          },
          "rejectedPlans" : []
      },
      "executionStats" : {
          "executionSuccess" : true,
          "nReturned" : 1,
          "executionTimeMillis" : 1916,
          "totalKeysExamined" : 0,
          "totalDocsExamined" : 1726879,
          "executionStages" : {
              "stage" : "COLLSCAN",
              "filter" : {
                  "$and" : [ 
                      {
                          "IpStart" : {
                              "$lt" : 1209192009.0
                          }
                      }, 
                      {
                          "IpEnd" : {
                              "$gt" : 1209192009.0
                          }
                      }
                  ]
              },
              "nReturned" : 1,
              "executionTimeMillisEstimate" : 1697,
              "works" : 1726881,
              "advanced" : 1,
              "needTime" : 1726879,
              "needYield" : 0,
              "saveState" : 13531,
              "restoreState" : 13531,
              "isEOF" : 1,
              "invalidates" : 0,
              "direction" : "forward",
              "docsExamined" : 1726879
          }
      },
      "serverInfo" : {
          "host" : "SOLANOD",
          "port" : 27017,
          "version" : "3.6.1",
          "gitVersion" : "025d4f4fe61efd1fb6f0005be20cb45a004093d1"
      },
      "ok" : 1.0
  }
  </code></pre>
  
  <p>Also I tried creating separate index (one for IpStart and another one for IpEnd) with no improvement.</p>
  
  <p>When I made the same query on SQL using between statement and with a compound index there:</p>
  
  <pre><code>SELECT * FROM [dbo].[ip_range_location] WHERE ipaddress BETWEEN ip_start AND ip_end
  </code></pre>
  
  <p>it takes no more than 300ms. </p>
  
  <p>I was expecting that Mongo will answer under 100ms, but I am not sure what will be wrong here.</p>
  
  <p>Any idea is great, thanks</p>