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

Query not using index intersection

    XMLWordPrintable

    Details

    • Type: Question
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Duplicate
    • Affects Version/s: 3.6.13
    • Fix Version/s: None
    • Component/s: Indexing, Querying, WiredTiger
    • Labels:
      None

      Description

      There seems to be some difficulty using index intersection in queries.

      For example in our collection we have the following query (you will understand the fields from the filter object):

      db.getCollection('Measurement').find({
        assetId: ObjectId('5c180b16fd86b100119fc400'),
        timestamp: { $gte: ISODate('2018-08-08T10:13:00'), $lte: ISODate('2019-08-08T10:13:00')},
        source: { $in: [0,2,3]},
        sourceId: { $in: ['5C180B16FD86B100119FC400_VIRTUAL_1', '5C180B16FD86B100119FC400_VIRTUAL_2', 'DEMO_NODE_ID-2'] }
      })

       

      In the collection amongst others  the following indexes:

      { assetId: 1 }
      

      { timestamp: 1, sourceId: 1, source: 1}
      

      However, running the query with explain, shows that the query planner selects to run the query only using assetId index and not an intersection of the other two which would completely cover the query.

      Why is this happenning?

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: