[SERVER-11307] Large $or clauses are extremely slow when compared to other access methods Created: 22/Oct/13 Updated: 10/Dec/14 Resolved: 14/Jul/14 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.5.3 |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Kyle Housley | Assignee: | Ramon Fernandez Marina |
| Resolution: | Done | Votes: | 0 |
| Labels: | query | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
nodejs driver |
||
| Attachments: |
|
| Backwards Compatibility: | Fully Compatible |
| Participants: |
| Description |
|
I was building a joining utility, in order to retrieve subdocuments, I built a query of or clauses matching each sub document to retrieve. The results were not terribly surprising at first, the query was very slow to respond. What was surprising was when I began to page my actual queries, that is to say divide the entries in the $or clause into many separate db queries matching 50 of the joining documents at a time, the database began responding much much faster. A join query for 1000 document took a minute without paging. With a small page size of 5 documents per join query, that time went down to 177ms. Querying for one document at a time was still at 214ms, way faster than where I started. So what I mean to say is that I think there is something wrong with the $or clause if it basically takes longer to execute a single query rather that 1000 small queries. Perhaps there is a limitation I'm not aware and that's why I marked this as an improvement and not a bug Here are the results of my testing 10000 1000 59929 |
| Comments |
| Comment by Ramon Fernandez Marina [ 14/Jul/14 ] |
|
khous, we haven't heard back from you for a while, so we're going to mark this ticket as resolved. If this is still an issue for you, feel free to re-open and provide additional information. Regards, |
| Comment by Ramon Fernandez Marina [ 03/Jul/14 ] |
|
Hi khous, is this still an issue for you? If you've upgraded to older versions and you're still seeing slow $or queries, could you please let us know? If the behavior you describe wasn't fixed in 2.5.5 we would like to investigate why. |
| Comment by Ramon Fernandez Marina [ 20/Jun/14 ] |
|
khous, apologies for the late reply. Have you seen Asya's comment and try to reproduce in 2.5.5? If you upgrade to a version >= 2.5.5 or anything in the 2.6 series and continue to see the behavior you describe, can you please send us the actual query so we can try to reproduce and investigate on our end? |
| Comment by Asya Kamsky [ 26/Oct/13 ] |
|
I didn't see your exact query, but it's possible that this is a duplicate of |