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

Plan ranker favors intersection plans if predicate generates empty range index scan

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 2.6.5, 2.7.6
    • Affects Version/s: 2.6.4
    • Component/s: Querying
    • None
    • ALL

      Issue Status as of Aug 21, 2014

      ISSUE SUMMARY
      A change was introduced to the query engine in version 2.6.4 of the server, which affected the behavior of the query plan ranker. This change causes index intersection plans to be incorrectly chosen instead of a single-index plans, for certain types of queries with eligible index intersection plans. The incorrect choice of an index intersection plan in these cases introduces a performance regression to the affected queries.

      A query is affected by this issue if it meets both of the following conditions:

      • the query includes a logical AND condition over multiple indexed predicates (i.e. the query is eligible for index intersection)
      • one of the indexed predicates in the AND condition matches no documents

      Consider the following shell snippet:

      db.foo.drop()
      db.foo.ensureIndex({a:1})
      db.foo.ensureIndex({b:1})
      db.foo.insert({a:1,b:1})
      db.foo.find({a:0,b:{$gte:1}})
      

      The query on line 5 above generates an index intersection plan, and no documents from the collection match the indexed predicate {a: 0}. Thus, the query is affected by this issue.

      USER IMPACT
      Any query that is incorrectly assigned index intersection plans in this way will return correct results, but may suffer from reduced performance. In addition, the intersection plan will be saved in the query plan cache, such that subsequently executions of a query with the same shape may also suffer from reduced performance.

      WORKAROUNDS
      If the use of the intersection plan adversely affects performance, users may disable the query planner from generating intersection plans altogether by running the following from the shell:

      db.adminCommand({setParameter: 1, internalQueryPlannerEnableIndexIntersection: 0});
      

      Alternatively, users can restart mongod with option --setParameter internalQueryPlannerEnableIndexIntersection=0 to achieve the same effect.

      AFFECTED VERSIONS
      Only the MongoDB 2.6.4 production release is affected by this issue.

      FIX VERSION
      The fix is included in the 2.6.5 production release.

      RESOLUTION DETAILS
      The IndexScan query execution stage was changed such that single index plans are now correctly ranked higher than index intersection plans for the affected queries.

      Original description

      After upgrading to 2.6.4 the query optimizer is chosing crazy indexes (or combination of). For example, this query:

      sprawk2:PRIMARY> db.Example.find( { clExists: true, lc: "eng", trans: "ara", tLcLeft: "conference", textLc: "conference", pattern: false, group: "all" }).explain();
      {
      	"cursor" : "Complex Plan",
      	"n" : 0,
      	"nscannedObjects" : 0,
      	"nscanned" : 2,
      	"nscannedObjectsAllPlans" : 0,
      	"nscannedAllPlans" : 11,
      	"nYields" : 4,
      	"nChunkSkips" : 0,
      	"millis" : 17320,
      	"server" : "tor:27018",
      	"filterSet" : false
      }
      

      I have an index

      {trans:1,tLcLeft:1,group:1,lc:1}

      also a sparse index on

      {clExists:1,tLcLeft:1}

      . Since about 1% of documents have a clExists field, this is pretty small, but either of these indices alone should give a quick response I would think. What is this complex query and why is it so slow?
      Our whole cluster is overloading with these queries.

            Assignee:
            rassi J Rassi
            Reporter:
            niccottrell Nic Cottrell (Personal)
            Votes:
            0 Vote for this issue
            Watchers:
            17 Start watching this issue

              Created:
              Updated:
              Resolved: