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

Hashed index is not used for queries that can be rewritten as equalities

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Minor - P4 Minor - P4
    • None
    • Affects Version/s: 2.5.4
    • Component/s: Querying
    • Labels:
      None
    • Query Optimization

      Create a hashed index on field 'a':

      db.t.drop();
      db.t.ensureIndex({a: 1})
      

      Now consider the following query:

      db.t.find({a: {$gte: 3, $lte: 3}})
      

      The old query system recognized that this query means "a == 3" and therefore was able to take advantage of the hashed index. In the new query system, however, this query will just do a collection scan instead of generating an indexed solution. A jstest for this (see here) was disabled as part of migrating to the new query system.

      The problem with the new system occurs inside PlannerIXSelect::rateIndices. Here, the planner checks whether the hashed index is "compatible" with the subexpressions "a >= 3" and "a <= 3". But compatibility for hashed indices is defined as being an equality match expression or an $in (see the code here).

      This is a non-trivial problem to fix because the IX selection stage of the query planner happens before the index bounds builder runs. It is the index bounds builder that would normally take the intersection of the "a >= 3" and "a <= 3", thus recognizing that the query can be executed as an index scan over a point interval.

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            david.storch@mongodb.com David Storch
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated: