[SERVER-42733] Query not using index intersection Created: 09/Aug/19  Updated: 15/Aug/19  Resolved: 15/Aug/19

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying, WiredTiger
Affects Version/s: 3.6.13
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Dimitris Xalatsis Assignee: Danny Hatcher (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-12923 Plan ranking is bad for plans with bl... Backlog
Participants:

 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?



 Comments   
Comment by Danny Hatcher (Inactive) [ 15/Aug/19 ]

This is part of several known issues with index intersection where we conservatively assume the use of a single index is more optimal than combining two indexes. There are several tickets that are targeted at tackling this issue, most notably SERVER-12923. I recommend reorganizing your data and queries to take advantage of compound indexes rather than index intersection. For example, if all your queries always contain those four fields, it would most likely be a large advantage to put all of them into an index.

Generated at Thu Feb 08 05:01:17 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.