Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-100753

Some queries can be executed faster with a COLLSCAN even if an index exists

    • Type: Icon: Improvement Improvement
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Optimization

      Currently, we always use an index scan when there is an index that the query can take care of. However, sometimes, a collection scan can significantly outperform an index scan. 

      This was also observed as part of SERVER-23406, and WiredTiger has been improved so that an index scan is now less expensive relative a collection scan from the perspective of storage.

      However, even with the WiredTiger improvement, it still in the case that for some queries, we are better off running a collection scan. See https://jira.mongodb.org/browse/SERVER-29967?focusedCommentId=7058329&focusedId=7058329&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-7058329

      A multi-key index with a filter is only worthwhile if there are bounds that significantly reduces the number of keys that are exmained. I have not tested yet whether a multi-key index without a filter performs better than a collection scan if the bounds do not significantly reduce the number of keys that are examined.

      This ticket specifically focuses on giving the optimizer the ability to detect that a collection scan could be cheaper and choosing that option.

            Assignee:
            Unassigned Unassigned
            Reporter:
            andi.wang@mongodb.com Andi Wang
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: