[SERVER-15985] plan change in 2.6.5 causes query to run slower than in 2.6.4 Created: 06/Nov/14 Updated: 08/Feb/23 Resolved: 06/Nov/14 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.6.5 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | yosi oren | Assignee: | Ramon Fernandez Marina |
| Resolution: | Done | Votes: | 2 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Attachments: |
|
||||||||||||
| Issue Links: |
|
||||||||||||
| Operating System: | ALL | ||||||||||||
| Participants: | |||||||||||||
| Description |
|
when same query and index run on 2.6.4 it is much faster then on 2.6.5 , looking at explain show second scan to not use the indexes. the attached js file has both query and explain results for both versions, can see millis difference . 150015 on 2.6.5 compared to 0 on 2.6.4 server. follow is [ObjectId()] query use $in and $nin . the affect is noticed when users is more then 110000 documents on a server with 1GB RAM. |
| Comments |
| Comment by Ramon Fernandez Marina [ 06/Nov/14 ] | ||||||||||||||||||||||||||
|
nabulaer, this query is performing faster in 2.6.4 because hash-based index intersection is being used. Here's the relevant part of the explain() output that shows what's going on:
The query planner runs two index scans (IXSCAN), one for the {$in:[...318e]} predicate and one for the {$nin:[...318f']} predicate, with the goal of intersecting the results at the end (see the ComplexPlan and AND_HASH markers above). Pretty soon it finds out there's no documents where the follow field contains the ObjectID ending in 318e (see isEOF field), and the intersection of the empty set with anything else is the empty set so the query planner returns almost instantly with 0 results. Hash-based index intersection was disabled by default in 2.6.5 because we found it could cause significant performance degradation in specific cases (see You can always enable hash-based index intersection as described above if it helps you achieve better performance (or stay with 2.6.4), but please be aware of the potential implications described in Regards, | ||||||||||||||||||||||||||
| Comment by Ramon Fernandez Marina [ 06/Nov/14 ] | ||||||||||||||||||||||||||
|
This is related to hash-based index intersection, which was disabled on 2.6.5 as described in Can you try re-enabling hash-based index intersection and running your queries again? Here's how:
Also, can you please send us the output of the following command?
Thanks, |