[SERVER-20492] Incorrect index used for count query using wiredTiger that causes slow queries Created: 18/Sep/15  Updated: 09/Jan/16  Resolved: 09/Jan/16

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 3.0.4, 3.0.6
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: David Tsai Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Participants:

 Description   

In our slow query logs we can see that this query below is using the incorrect index which causes it to run slowly.

[conn3522] command haa_msgs.$cmd command: count { count: "messages", query: { $or: [ { targetId: ObjectId('55fc54eecb05d8d2547334a1') }, { targetId: null } ], type: { $in: [ 2 ] }, createdAt: { $gt: new Date(0) } }, fields: null } planSummary: IXSCAN { createdAt: -1.0 } keyUpdates:0 writeConflicts:0 numYields:271 reslen:44 locks:{ Global: { acquireCount: { r: 544 } }, Database: { acquireCount: { r: 272 } }, Collection: { acquireCount: { r: 272 } } } 103ms

We have an index on this collection that I would expect to be used. But the log implies it uses a different index to run the query.

{targetId:1, type:1, createdAt:-1}

However, if I manually run a find query using the same query conditions, the explain plan uses the expected index.

This type of query has always behaved as expected in the past. This problem occurs on version 3.0.4 and 3.0.6.

Not sure what is going on here, but this is causing serious performance problems in our database.



 Comments   
Comment by Ramon Fernandez Marina [ 09/Jan/16 ]

davidt, we haven't heard back from you for some time so I'm going to close this ticket. If this is still an issue for you please send the explain(true) information requested above and we'll investigate further.

Regards,
Ramón.

Comment by Ramon Fernandez Marina [ 13/Nov/15 ]

Apologies for the long delay in responding davidt. If this is still an issue for you, can you please send the explain(true) output for the operation that uses the wrong index, for the find() command that uses the expected index, and for the original operation removing the targetId portion of the query?

Thanks,
Ramón.

Comment by David Tsai [ 18/Sep/15 ]


Experimenting a bit, it seems like removing the

{targetId:null}

portion of the query, solves the problem. As far I understand it, a null equality condition should work with compound indexes?

Generated at Thu Feb 08 03:54:22 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.