[SERVER-13065] Consider a collection scan even if indexed plans are available Created: 05/Mar/14 Updated: 06/Dec/22 |
|
| Status: | Backlog |
| Project: | Core Server |
| Component/s: | Index Maintenance |
| Affects Version/s: | 2.4.9, 2.6.0-rc0 |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Michael Kennedy | Assignee: | Backlog - Query Optimization |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | bonsai | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Attachments: |
|
||||||||||||||||||||||||||||
| Issue Links: |
|
||||||||||||||||||||||||||||
| Assigned Teams: |
Query Optimization
|
||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||
| Description |
|
Restore the database from the attached BSON data (I called it BookStore FWIW). Then this query without indexes takes about 300 ms and uses basic cusor as you would expect:
Running explain we see it is using a cursor and it takes 891 ms.
Now, remove that index:
And you will see it runs in 300ms!
It seems like mongo should either:
To make matters much worse, I was actually running just a count operation via the C# driver and go even worse perf. Seems like there should be almost no perf cost for a count operation on an index set. |
| Comments |
| Comment by Asya Kamsky [ 27/Mar/18 ] |
|
Ah yes, got it. |
| Comment by Matthew Kruse [ 27/Mar/18 ] |
|
Asya, this issue was closed a a duplicate of this one. It has my summary of what I experienced on 3.6.2 |
| Comment by Asya Kamsky [ 27/Mar/18 ] |
|
mkruse@adobe.com can you please clarify what exactly you are observing? |
| Comment by Matthew Kruse [ 26/Mar/18 ] |
|
I'm still seeing this behavior on mongo 3.6.2. Be nice not to have to control for this with query hints and have the optimizer do this for everyone. |
| Comment by Michael Kennedy [ 10/Mar/14 ] |
|
Thanks. I know it's kind of extreme, but I appreciate you following up on it.! |
| Comment by David Storch [ 10/Mar/14 ] |
|
Hi Michael, Thanks for another detailed bug report and for attaching the data so that we can reproduce the issue. The discrepancy in performance between the collection scan and the indexed plan occurs because your example query returns nearly all of the documents in the collection. There are 271380 documents in the "Books" collection, and the query returns 270171 (or 99.55%). It is not surprising that the indexed solution takes longer in this case: both plans scan through about 270 thousand documents, but the indexed plan incurs the additional cost of scanning through about 270 thousand index keys. In both versions 2.4.9 and 2.6.0-rc0, MongoDB will select an indexed plan if one is available. This is why you see the slower indexed plan being chosen over the collection scan solution. Careful design of schema and indices can prevent such a scenario. Nevertheless, you could imagine a query optimizer which would be able to intelligently choose among indexed plans and a collection scan in order to avoid this automatically. I'm going to retitle this ticket to that effect and get it triaged. |
| Comment by Michael Kennedy [ 05/Mar/14 ] |
|
Sorry I forgot to add this when I submitted: I tested this both on 2.4.9 and 2.6.0_rc0. |