[SERVER-59341] Query planner choses collscan over covered index scan for huge collection Created: 13/Aug/21 Updated: 06/Dec/22 |
|
| Status: | Backlog |
| Project: | Core Server |
| Component/s: | None |
| Affects Version/s: | 5.0.2 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Geert Bosch | Assignee: | Backlog - Query Optimization |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
|||||||||||||||||||||||||||||||||||||||||||||
| Assigned Teams: |
Query Optimization
|
|||||||||||||||||||||||||||||||||||||||||||||
| Operating System: | ALL | |||||||||||||||||||||||||||||||||||||||||||||
| Steps To Reproduce: |
Without the hint, the plan is a collection scan:
|
|||||||||||||||||||||||||||||||||||||||||||||
| Participants: | ||||||||||||||||||||||||||||||||||||||||||||||
| Description |
|
On a collection of 4.3 GB, with an index of 14.8 MB, the query planner chooses a collection scan over a covered index scan.
This query takes 3.4s, but without the hint it take 40s. The collection here is the system.buckets.point_data collection for a time-series collection, but the issue is not specific to time-series. I understand that often a collection scan can be faster, but in this case the 290x size difference between the index and the collection size should have been decisive. Anyway, it doesn't look like the index scan was considered at all here. |
| Comments |
| Comment by James Wahlin [ 26/Aug/21 ] |
|
This behavior is intentional. We added the ability for the planner to choose a covered index scan for a query with an empty query predicate under kyle.suarez or david.storch may be able to provide more context, but from reading though the
|
| Comment by Louis Williams [ 19/Aug/21 ] |
|
george@qntify.co, that sounds like a different problem. This only affects time-series collections specifically. If you are still experiencing this problem, would you please file a new SERVER ticket with more details including the exact create index command, number of documents in the collection, and the replica set configuration? |
| Comment by George Mihailov [ 18/Aug/21 ] |
|
Don't know if this is related. We migrated to 5.0.2 a few days ago and index building takes a lot of time (haven't had this issue before). EXECUTION TIME 4871495 MS and counting ... |