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.
- is duplicated by
-
SERVER-15049 Database is read-overloaded after upgrading to 2.6.4
- Closed
-
SERVER-15196 Hashed index causes 'Overflow hashed AND stage buffered data usage' error
- Closed
-
SERVER-15300 Wrong index is being used, causing inefficient queries
- Closed
- related to
-
SERVER-14311 skipping of index keys is not accounted for in plan ranking by the index scan stage
- Closed
-
SERVER-15279 Disable hash-based index intersection (AND_HASH) by default
- Closed