Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-32186

Query Planner selects wrong index

    • Query
    • ALL

      I have a collection that has documents of the following form:

      // Some comments here
      {
      	"_id:": ObjectId(),
      	"timestamp": ISODate(),
      	"fieldA": Number,
      	"fieldB": String,
      	.... // irrelevant fields
      }
      

      and the following indexes:

      1. _id index { _id :1}
      2. timestamp_index: { timestamp: 1 }
      3. compound_index: { fieldA: 1, fieldB: 1 }

      The collection stores around 2.5 million documents

      When I issue the query :

      db.collection.find(
      {
        "timestamp": {
          "$gte": ISODate("2017-11-29T14:55:15.682Z"),
          "$lte": ISODate("2017-12-06T10:49:36.833Z")
        },
        "fieldA": 0,
        "fieldB": "0013A20041673C35"
      }).sort({timestamp: -1}).toArray()
      

      The result is about 3500 documents
      The queryPlanner evaluates the usage of first using the compound_index and rejects it. It finally uses the timestamp_index as a first IXSCAN stage and the rest of the query in a FETCH stage... This operation results in 2.5 seconds execution time

      However if I enforce the usage of the compound index in the IXSCAN stage with the

      .hint({fieldA: 1, fieldB: 1})

      the execution time becomes 60 ms!!!

      So how does the query planner evaluate the performance of each plan and reject it?

      The real problem is that this is used in an aggregation where I cannot use the *hint * functionality to enforce index usage, so this is a big stepdown in performance...

            Assignee:
            backlog-server-query Backlog - Query Team (Inactive)
            Reporter:
            dimitris_ha@hotmail.com Dimitris Halatsis
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: