[SERVER-23999] Query fully covered by index still reads documents Created: 01/May/16  Updated: 08/May/17  Resolved: 02/May/16

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 3.2.6
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Andrey Hohutkin Assignee: David Storch
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: Zip Archive activities2.zip    
Issue Links:
Duplicate
duplicates SERVER-15221 Planner sort analysis should not add ... Closed
Participants:

 Description   

I have a collection "activities2":

Index

{
    action: 1,
    userId: 1,
    time: 1
}

I run a query:

Query

db.activities2.find({
	userId: {
		$in: [
							ObjectId("5373bcfef88919d05eee3149"),
							ObjectId("537468fff88919d05eee322c"),
							ObjectId("537f543778f03be40e598ade"),
							ObjectId("5382f987832983fb3a5ae9d4"),
							ObjectId("53a561b2663e61b528241bc4"),
							ObjectId("53a80878cde71b653ed82539"),
							ObjectId("53a8cc6511b9cc964959c365"),
							ObjectId("53acbfb3cb9f89250d02c48a"),
							ObjectId("53af045ebcc79931692a4aa7"),
							ObjectId("53b494fb9809bceb3fd25c89"),
							ObjectId("53b6eae20c4a07fe63db10b3"),
							ObjectId("53b831b49809bceb3fd2cfab"),
							ObjectId("53bab1e2b5067de0061a4567"),
							ObjectId("53bb05e1386b18ce5b20d86b"),
		]
	},
	action: {
		$in: [
							"ANSAttempt",
							"GooglePlusAttempt",
							"TwitterAttempt",
							"articleLike",
							"articleShare",
							"fbMessengerAttempt",
							"fbShare",
							"follow",
							"forumComment_add",
							"forumPost_add",
							"forumPost_edit",
							"like",
							"objectLike_forumComment",
							"objectLike_forumPost",
							"objectLike_userProfileImage",
							"objectLike_userUpdatedTagline",
							"objectShare_forumComment",
							"objectShare_forumPost",
							"share",
							"signin",
							"userUpdatedPicture",
							"userUpdatedTagline"
		]
	},
	time: {
		"$lt" : toDate,
//		"$gte": fromDate,
	}
}, {
	action: 1,
	userId: 1,
	time: 1,
	_id: 0,
})
.sort({
	time: -1
})
.limit(100)
.hint({
	action: 1,
	userId: 1,
	time: 1,
})
.explain(1)

Partial explain result:

Explain

{
    "executionSuccess" : true, 
    "nReturned" : NumberInt(100), 
    "executionTimeMillis" : NumberInt(10), 
    "totalKeysExamined" : NumberInt(2210), 
    "totalDocsExamined" : NumberInt(2210), 
    "executionStages" : {
        "stage" : "PROJECTION", 
        "nReturned" : NumberInt(100), 
        "executionTimeMillisEstimate" : NumberInt(10), 
        "works" : NumberInt(2313), 
        "advanced" : NumberInt(100), 
        "needTime" : NumberInt(2212), 
        "needYield" : NumberInt(0), 
        "saveState" : NumberInt(18), 
        "restoreState" : NumberInt(18), 
        "isEOF" : NumberInt(1), 
        "invalidates" : NumberInt(0), 
        "transformBy" : {
            "action" : NumberInt(1), 
            "userId" : NumberInt(1), 
            "time" : NumberInt(1), 
            "_id" : NumberInt(0)
        }, 
        "inputStage" : {
            "stage" : "SORT", 
            "nReturned" : NumberInt(100), 
            "executionTimeMillisEstimate" : NumberInt(10), 
            "works" : NumberInt(2313), 
            "advanced" : NumberInt(100), 
            "needTime" : NumberInt(2212), 
            "needYield" : NumberInt(0), 
            "saveState" : NumberInt(18), 
            "restoreState" : NumberInt(18), 
            "isEOF" : NumberInt(1), 
            "invalidates" : NumberInt(0), 
            "sortPattern" : {
                "time" : NumberInt(-1)
            }, 
            "memUsage" : NumberInt(9432), 
            "memLimit" : NumberInt(33554432), 
            "limitAmount" : NumberInt(100), 
            "inputStage" : {
                "stage" : "SORT_KEY_GENERATOR", 
                "nReturned" : NumberInt(0), 
                "executionTimeMillisEstimate" : NumberInt(10), 
                "works" : NumberInt(2212), 
                "advanced" : NumberInt(0), 
                "needTime" : NumberInt(1), 
                "needYield" : NumberInt(0), 
                "saveState" : NumberInt(18), 
                "restoreState" : NumberInt(18), 
                "isEOF" : NumberInt(1), 
                "invalidates" : NumberInt(0), 
                "inputStage" : {
                    "stage" : "FETCH", 
                    "nReturned" : NumberInt(2210), 
                    "executionTimeMillisEstimate" : NumberInt(10), 
                    "works" : NumberInt(2211), 
                    "advanced" : NumberInt(2210), 
                    "needTime" : NumberInt(0), 
                    "needYield" : NumberInt(0), 
                    "saveState" : NumberInt(18), 
                    "restoreState" : NumberInt(18), 
                    "isEOF" : NumberInt(1), 
                    "invalidates" : NumberInt(0), 
                    "docsExamined" : NumberInt(2210), 
                    "alreadyHasObj" : NumberInt(0), 
                    "inputStage" : {
                        "stage" : "IXSCAN", 
                        "nReturned" : NumberInt(2210), 
                        "executionTimeMillisEstimate" : NumberInt(10), 
                        "works" : NumberInt(2211), 
                        "advanced" : NumberInt(2210), 
                        "needTime" : NumberInt(0), 
                        "needYield" : NumberInt(0), 
                        "saveState" : NumberInt(18), 
                        "restoreState" : NumberInt(18), 
                        "isEOF" : NumberInt(1), 
                        "invalidates" : NumberInt(0), 
                        "keyPattern" : {
                            "action" : NumberInt(1), 
                            "userId" : NumberInt(1), 
                            "time" : NumberInt(1)
                        }, 
                        "indexName" : "action_1_userId_1_time_1", 
                        "isMultiKey" : false, 
                        "isUnique" : false, 
                        "isSparse" : false, 
                        "isPartial" : false, 
                        "indexVersion" : NumberInt(1), 
                        "direction" : "backward", 
                        "indexBounds" : {
                            "action" : [
                                "[\"userUpdatedTagline\", \"userUpdatedTagline\"]", 
                                "[\"userUpdatedPicture\", \"userUpdatedPicture\"]", 
                                "[\"signin\", \"signin\"]", 
                                "[\"share\", \"share\"]", 
                                "[\"objectShare_forumPost\", \"objectShare_forumPost\"]", 
                                "[\"objectShare_forumComment\", \"objectShare_forumComment\"]", 
                                "[\"objectLike_userUpdatedTagline\", \"objectLike_userUpdatedTagline\"]", 
                                "[\"objectLike_userProfileImage\", \"objectLike_userProfileImage\"]", 
                                "[\"objectLike_forumPost\", \"objectLike_forumPost\"]", 
                                "[\"objectLike_forumComment\", \"objectLike_forumComment\"]", 
                                "[\"like\", \"like\"]", 
                                "[\"forumPost_edit\", \"forumPost_edit\"]", 
                                "[\"forumPost_add\", \"forumPost_add\"]", 
                                "[\"forumComment_add\", \"forumComment_add\"]", 
                                "[\"follow\", \"follow\"]", 
                                "[\"fbShare\", \"fbShare\"]", 
                                "[\"fbMessengerAttempt\", \"fbMessengerAttempt\"]", 
                                "[\"articleShare\", \"articleShare\"]", 
                                "[\"articleLike\", \"articleLike\"]", 
                                "[\"TwitterAttempt\", \"TwitterAttempt\"]", 
                                "[\"GooglePlusAttempt\", \"GooglePlusAttempt\"]", 
                                "[\"ANSAttempt\", \"ANSAttempt\"]"
                            ], 
                            "userId" : [
                                "[ObjectId('53bb05e1386b18ce5b20d86b'), ObjectId('53bb05e1386b18ce5b20d86b')]", 
                                "[ObjectId('53bab1e2b5067de0061a4567'), ObjectId('53bab1e2b5067de0061a4567')]", 
                                "[ObjectId('53b831b49809bceb3fd2cfab'), ObjectId('53b831b49809bceb3fd2cfab')]", 
                                "[ObjectId('53b6eae20c4a07fe63db10b3'), ObjectId('53b6eae20c4a07fe63db10b3')]", 
                                "[ObjectId('53b494fb9809bceb3fd25c89'), ObjectId('53b494fb9809bceb3fd25c89')]", 
                                "[ObjectId('53af045ebcc79931692a4aa7'), ObjectId('53af045ebcc79931692a4aa7')]", 
                                "[ObjectId('53acbfb3cb9f89250d02c48a'), ObjectId('53acbfb3cb9f89250d02c48a')]", 
                                "[ObjectId('53a8cc6511b9cc964959c365'), ObjectId('53a8cc6511b9cc964959c365')]", 
                                "[ObjectId('53a80878cde71b653ed82539'), ObjectId('53a80878cde71b653ed82539')]", 
                                "[ObjectId('53a561b2663e61b528241bc4'), ObjectId('53a561b2663e61b528241bc4')]", 
                                "[ObjectId('5382f987832983fb3a5ae9d4'), ObjectId('5382f987832983fb3a5ae9d4')]", 
                                "[ObjectId('537f543778f03be40e598ade'), ObjectId('537f543778f03be40e598ade')]", 
                                "[ObjectId('537468fff88919d05eee322c'), ObjectId('537468fff88919d05eee322c')]", 
                                "[ObjectId('5373bcfef88919d05eee3149'), ObjectId('5373bcfef88919d05eee3149')]"
                            ], 
                            "time" : [
                                "(new Date(1461292516649), true)"
                            ]
                        }, 
                        "keysExamined" : NumberInt(2210), 
                        "dupsTested" : NumberInt(0), 
                        "dupsDropped" : NumberInt(0), 
                        "seenInvalidated" : NumberInt(0)
                    }
                }
            }
        }
    }, 
    "allPlansExecution" : [
    ]
}

A collection data is attached.



 Comments   
Comment by Kay Agahd [ 08/May/17 ]

I just tested if an index which satisfies the sort request will be covered as suggested by david.storch. So, either one of the following indexes would do the trick.

{
    time: 1,
    action: 1,
    userId: 1
}

The query which is using this index is covered:

> db.act.find(q,p).sort(s).hint(h2).explain(true)
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.act",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"time" : {
						"$lt" : ISODate("2015-12-28T15:59:52.636Z")
					}
				},
				{
					"time" : {
						"$gte" : ISODate("2014-05-15T11:04:18.193Z")
					}
				},
				{
					"action" : {
						"$in" : [
							"ANSAttempt",
							"GooglePlusAttempt",
							"TwitterAttempt",
							"articleLike",
							"articleShare",
							"fbMessengerAttempt",
							"fbShare",
							"follow",
							"forumComment_add",
							"forumPost_add",
							"forumPost_edit",
							"like",
							"objectLike_forumComment",
							"objectLike_forumPost",
							"objectLike_userProfileImage",
							"objectLike_userUpdatedTagline",
							"objectShare_forumComment",
							"objectShare_forumPost",
							"share",
							"signin",
							"userUpdatedPicture",
							"userUpdatedTagline"
						]
					}
				},
				{
					"userId" : {
						"$in" : [
							ObjectId("5373bcfef88919d05eee3149"),
							ObjectId("537468fff88919d05eee322c"),
							ObjectId("537f543778f03be40e598ade"),
							ObjectId("5382f987832983fb3a5ae9d4"),
							ObjectId("53a561b2663e61b528241bc4"),
							ObjectId("53a80878cde71b653ed82539"),
							ObjectId("53a8cc6511b9cc964959c365"),
							ObjectId("53acbfb3cb9f89250d02c48a"),
							ObjectId("53af045ebcc79931692a4aa7"),
							ObjectId("53b494fb9809bceb3fd25c89"),
							ObjectId("53b6eae20c4a07fe63db10b3"),
							ObjectId("53b831b49809bceb3fd2cfab"),
							ObjectId("53bab1e2b5067de0061a4567"),
							ObjectId("53bb05e1386b18ce5b20d86b")
						]
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "PROJECTION",
			"transformBy" : {
				"action" : 1,
				"userId" : 1,
				"time" : 1,
				"_id" : 0
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"time" : 1,
					"action" : 1,
					"userId" : 1
				},
				"indexName" : "time_1_action_1_userId_1",
				"isMultiKey" : false,
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 1,
				"direction" : "backward",
				"indexBounds" : {
					"time" : [
						"(new Date(1451318392636), new Date(1400151858193)]"
					],
					"action" : [
						"[\"userUpdatedTagline\", \"userUpdatedTagline\"]",
						"[\"userUpdatedPicture\", \"userUpdatedPicture\"]",
						"[\"signin\", \"signin\"]",
						"[\"share\", \"share\"]",
						"[\"objectShare_forumPost\", \"objectShare_forumPost\"]",
						"[\"objectShare_forumComment\", \"objectShare_forumComment\"]",
						"[\"objectLike_userUpdatedTagline\", \"objectLike_userUpdatedTagline\"]",
						"[\"objectLike_userProfileImage\", \"objectLike_userProfileImage\"]",
						"[\"objectLike_forumPost\", \"objectLike_forumPost\"]",
						"[\"objectLike_forumComment\", \"objectLike_forumComment\"]",
						"[\"like\", \"like\"]",
						"[\"forumPost_edit\", \"forumPost_edit\"]",
						"[\"forumPost_add\", \"forumPost_add\"]",
						"[\"forumComment_add\", \"forumComment_add\"]",
						"[\"follow\", \"follow\"]",
						"[\"fbShare\", \"fbShare\"]",
						"[\"fbMessengerAttempt\", \"fbMessengerAttempt\"]",
						"[\"articleShare\", \"articleShare\"]",
						"[\"articleLike\", \"articleLike\"]",
						"[\"TwitterAttempt\", \"TwitterAttempt\"]",
						"[\"GooglePlusAttempt\", \"GooglePlusAttempt\"]",
						"[\"ANSAttempt\", \"ANSAttempt\"]"
					],
					"userId" : [
						"[ObjectId('53bb05e1386b18ce5b20d86b'), ObjectId('53bb05e1386b18ce5b20d86b')]",
						"[ObjectId('53bab1e2b5067de0061a4567'), ObjectId('53bab1e2b5067de0061a4567')]",
						"[ObjectId('53b831b49809bceb3fd2cfab'), ObjectId('53b831b49809bceb3fd2cfab')]",
						"[ObjectId('53b6eae20c4a07fe63db10b3'), ObjectId('53b6eae20c4a07fe63db10b3')]",
						"[ObjectId('53b494fb9809bceb3fd25c89'), ObjectId('53b494fb9809bceb3fd25c89')]",
						"[ObjectId('53af045ebcc79931692a4aa7'), ObjectId('53af045ebcc79931692a4aa7')]",
						"[ObjectId('53acbfb3cb9f89250d02c48a'), ObjectId('53acbfb3cb9f89250d02c48a')]",
						"[ObjectId('53a8cc6511b9cc964959c365'), ObjectId('53a8cc6511b9cc964959c365')]",
						"[ObjectId('53a80878cde71b653ed82539'), ObjectId('53a80878cde71b653ed82539')]",
						"[ObjectId('53a561b2663e61b528241bc4'), ObjectId('53a561b2663e61b528241bc4')]",
						"[ObjectId('5382f987832983fb3a5ae9d4'), ObjectId('5382f987832983fb3a5ae9d4')]",
						"[ObjectId('537f543778f03be40e598ade'), ObjectId('537f543778f03be40e598ade')]",
						"[ObjectId('537468fff88919d05eee322c'), ObjectId('537468fff88919d05eee322c')]",
						"[ObjectId('5373bcfef88919d05eee3149'), ObjectId('5373bcfef88919d05eee3149')]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 2209,
		"executionTimeMillis" : 4,
		"totalKeysExamined" : 2209,
		"totalDocsExamined" : 0,
		"executionStages" : {
			"stage" : "PROJECTION",
			"nReturned" : 2209,
			"executionTimeMillisEstimate" : 0,
			"works" : 2210,
			"advanced" : 2209,
			"needTime" : 0,
			"needYield" : 0,
			"saveState" : 17,
			"restoreState" : 17,
			"isEOF" : 1,
			"invalidates" : 0,
			"transformBy" : {
				"action" : 1,
				"userId" : 1,
				"time" : 1,
				"_id" : 0
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"nReturned" : 2209,
				"executionTimeMillisEstimate" : 0,
				"works" : 2210,
				"advanced" : 2209,
				"needTime" : 0,
				"needYield" : 0,
				"saveState" : 17,
				"restoreState" : 17,
				"isEOF" : 1,
				"invalidates" : 0,
				"keyPattern" : {
					"time" : 1,
					"action" : 1,
					"userId" : 1
				},
				"indexName" : "time_1_action_1_userId_1",
				"isMultiKey" : false,
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 1,
				"direction" : "backward",
				"indexBounds" : {
					"time" : [
						"(new Date(1451318392636), new Date(1400151858193)]"
					],
					"action" : [
						"[\"userUpdatedTagline\", \"userUpdatedTagline\"]",
						"[\"userUpdatedPicture\", \"userUpdatedPicture\"]",
						"[\"signin\", \"signin\"]",
						"[\"share\", \"share\"]",
						"[\"objectShare_forumPost\", \"objectShare_forumPost\"]",
						"[\"objectShare_forumComment\", \"objectShare_forumComment\"]",
						"[\"objectLike_userUpdatedTagline\", \"objectLike_userUpdatedTagline\"]",
						"[\"objectLike_userProfileImage\", \"objectLike_userProfileImage\"]",
						"[\"objectLike_forumPost\", \"objectLike_forumPost\"]",
						"[\"objectLike_forumComment\", \"objectLike_forumComment\"]",
						"[\"like\", \"like\"]",
						"[\"forumPost_edit\", \"forumPost_edit\"]",
						"[\"forumPost_add\", \"forumPost_add\"]",
						"[\"forumComment_add\", \"forumComment_add\"]",
						"[\"follow\", \"follow\"]",
						"[\"fbShare\", \"fbShare\"]",
						"[\"fbMessengerAttempt\", \"fbMessengerAttempt\"]",
						"[\"articleShare\", \"articleShare\"]",
						"[\"articleLike\", \"articleLike\"]",
						"[\"TwitterAttempt\", \"TwitterAttempt\"]",
						"[\"GooglePlusAttempt\", \"GooglePlusAttempt\"]",
						"[\"ANSAttempt\", \"ANSAttempt\"]"
					],
					"userId" : [
						"[ObjectId('53bb05e1386b18ce5b20d86b'), ObjectId('53bb05e1386b18ce5b20d86b')]",
						"[ObjectId('53bab1e2b5067de0061a4567'), ObjectId('53bab1e2b5067de0061a4567')]",
						"[ObjectId('53b831b49809bceb3fd2cfab'), ObjectId('53b831b49809bceb3fd2cfab')]",
						"[ObjectId('53b6eae20c4a07fe63db10b3'), ObjectId('53b6eae20c4a07fe63db10b3')]",
						"[ObjectId('53b494fb9809bceb3fd25c89'), ObjectId('53b494fb9809bceb3fd25c89')]",
						"[ObjectId('53af045ebcc79931692a4aa7'), ObjectId('53af045ebcc79931692a4aa7')]",
						"[ObjectId('53acbfb3cb9f89250d02c48a'), ObjectId('53acbfb3cb9f89250d02c48a')]",
						"[ObjectId('53a8cc6511b9cc964959c365'), ObjectId('53a8cc6511b9cc964959c365')]",
						"[ObjectId('53a80878cde71b653ed82539'), ObjectId('53a80878cde71b653ed82539')]",
						"[ObjectId('53a561b2663e61b528241bc4'), ObjectId('53a561b2663e61b528241bc4')]",
						"[ObjectId('5382f987832983fb3a5ae9d4'), ObjectId('5382f987832983fb3a5ae9d4')]",
						"[ObjectId('537f543778f03be40e598ade'), ObjectId('537f543778f03be40e598ade')]",
						"[ObjectId('537468fff88919d05eee322c'), ObjectId('537468fff88919d05eee322c')]",
						"[ObjectId('5373bcfef88919d05eee3149'), ObjectId('5373bcfef88919d05eee3149')]"
					]
				},
				"keysExamined" : 2209,
				"dupsTested" : 0,
				"dupsDropped" : 0,
				"seenInvalidated" : 0
			}
		},
		"allPlansExecution" : [ ]
	},
	"serverInfo" : {
		"host" : "mongo-082",
		"port" : 27017,
		"version" : "3.2.12",
		"gitVersion" : "ef3e1bc78e997f0d9f22f45aeb1d8e3b6ac14a14"
	},
	"ok" : 1
}

Or using the index:

{
    time: 1,
    userId: 1,
    action: 1
}

This query is covered too:

> db.act.find(q,p).sort(s).hint(h3).explain(true)
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.act",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"time" : {
						"$lt" : ISODate("2015-12-28T15:59:52.636Z")
					}
				},
				{
					"time" : {
						"$gte" : ISODate("2014-05-15T11:04:18.193Z")
					}
				},
				{
					"action" : {
						"$in" : [
							"ANSAttempt",
							"GooglePlusAttempt",
							"TwitterAttempt",
							"articleLike",
							"articleShare",
							"fbMessengerAttempt",
							"fbShare",
							"follow",
							"forumComment_add",
							"forumPost_add",
							"forumPost_edit",
							"like",
							"objectLike_forumComment",
							"objectLike_forumPost",
							"objectLike_userProfileImage",
							"objectLike_userUpdatedTagline",
							"objectShare_forumComment",
							"objectShare_forumPost",
							"share",
							"signin",
							"userUpdatedPicture",
							"userUpdatedTagline"
						]
					}
				},
				{
					"userId" : {
						"$in" : [
							ObjectId("5373bcfef88919d05eee3149"),
							ObjectId("537468fff88919d05eee322c"),
							ObjectId("537f543778f03be40e598ade"),
							ObjectId("5382f987832983fb3a5ae9d4"),
							ObjectId("53a561b2663e61b528241bc4"),
							ObjectId("53a80878cde71b653ed82539"),
							ObjectId("53a8cc6511b9cc964959c365"),
							ObjectId("53acbfb3cb9f89250d02c48a"),
							ObjectId("53af045ebcc79931692a4aa7"),
							ObjectId("53b494fb9809bceb3fd25c89"),
							ObjectId("53b6eae20c4a07fe63db10b3"),
							ObjectId("53b831b49809bceb3fd2cfab"),
							ObjectId("53bab1e2b5067de0061a4567"),
							ObjectId("53bb05e1386b18ce5b20d86b")
						]
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "PROJECTION",
			"transformBy" : {
				"action" : 1,
				"userId" : 1,
				"time" : 1,
				"_id" : 0
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"time" : 1,
					"userId" : 1,
					"action" : 1
				},
				"indexName" : "time_1_userId_1_action_1",
				"isMultiKey" : false,
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 1,
				"direction" : "backward",
				"indexBounds" : {
					"time" : [
						"(new Date(1451318392636), new Date(1400151858193)]"
					],
					"userId" : [
						"[ObjectId('53bb05e1386b18ce5b20d86b'), ObjectId('53bb05e1386b18ce5b20d86b')]",
						"[ObjectId('53bab1e2b5067de0061a4567'), ObjectId('53bab1e2b5067de0061a4567')]",
						"[ObjectId('53b831b49809bceb3fd2cfab'), ObjectId('53b831b49809bceb3fd2cfab')]",
						"[ObjectId('53b6eae20c4a07fe63db10b3'), ObjectId('53b6eae20c4a07fe63db10b3')]",
						"[ObjectId('53b494fb9809bceb3fd25c89'), ObjectId('53b494fb9809bceb3fd25c89')]",
						"[ObjectId('53af045ebcc79931692a4aa7'), ObjectId('53af045ebcc79931692a4aa7')]",
						"[ObjectId('53acbfb3cb9f89250d02c48a'), ObjectId('53acbfb3cb9f89250d02c48a')]",
						"[ObjectId('53a8cc6511b9cc964959c365'), ObjectId('53a8cc6511b9cc964959c365')]",
						"[ObjectId('53a80878cde71b653ed82539'), ObjectId('53a80878cde71b653ed82539')]",
						"[ObjectId('53a561b2663e61b528241bc4'), ObjectId('53a561b2663e61b528241bc4')]",
						"[ObjectId('5382f987832983fb3a5ae9d4'), ObjectId('5382f987832983fb3a5ae9d4')]",
						"[ObjectId('537f543778f03be40e598ade'), ObjectId('537f543778f03be40e598ade')]",
						"[ObjectId('537468fff88919d05eee322c'), ObjectId('537468fff88919d05eee322c')]",
						"[ObjectId('5373bcfef88919d05eee3149'), ObjectId('5373bcfef88919d05eee3149')]"
					],
					"action" : [
						"[\"userUpdatedTagline\", \"userUpdatedTagline\"]",
						"[\"userUpdatedPicture\", \"userUpdatedPicture\"]",
						"[\"signin\", \"signin\"]",
						"[\"share\", \"share\"]",
						"[\"objectShare_forumPost\", \"objectShare_forumPost\"]",
						"[\"objectShare_forumComment\", \"objectShare_forumComment\"]",
						"[\"objectLike_userUpdatedTagline\", \"objectLike_userUpdatedTagline\"]",
						"[\"objectLike_userProfileImage\", \"objectLike_userProfileImage\"]",
						"[\"objectLike_forumPost\", \"objectLike_forumPost\"]",
						"[\"objectLike_forumComment\", \"objectLike_forumComment\"]",
						"[\"like\", \"like\"]",
						"[\"forumPost_edit\", \"forumPost_edit\"]",
						"[\"forumPost_add\", \"forumPost_add\"]",
						"[\"forumComment_add\", \"forumComment_add\"]",
						"[\"follow\", \"follow\"]",
						"[\"fbShare\", \"fbShare\"]",
						"[\"fbMessengerAttempt\", \"fbMessengerAttempt\"]",
						"[\"articleShare\", \"articleShare\"]",
						"[\"articleLike\", \"articleLike\"]",
						"[\"TwitterAttempt\", \"TwitterAttempt\"]",
						"[\"GooglePlusAttempt\", \"GooglePlusAttempt\"]",
						"[\"ANSAttempt\", \"ANSAttempt\"]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 2209,
		"executionTimeMillis" : 4,
		"totalKeysExamined" : 2209,
		"totalDocsExamined" : 0,
		"executionStages" : {
			"stage" : "PROJECTION",
			"nReturned" : 2209,
			"executionTimeMillisEstimate" : 10,
			"works" : 2210,
			"advanced" : 2209,
			"needTime" : 0,
			"needYield" : 0,
			"saveState" : 17,
			"restoreState" : 17,
			"isEOF" : 1,
			"invalidates" : 0,
			"transformBy" : {
				"action" : 1,
				"userId" : 1,
				"time" : 1,
				"_id" : 0
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"nReturned" : 2209,
				"executionTimeMillisEstimate" : 10,
				"works" : 2210,
				"advanced" : 2209,
				"needTime" : 0,
				"needYield" : 0,
				"saveState" : 17,
				"restoreState" : 17,
				"isEOF" : 1,
				"invalidates" : 0,
				"keyPattern" : {
					"time" : 1,
					"userId" : 1,
					"action" : 1
				},
				"indexName" : "time_1_userId_1_action_1",
				"isMultiKey" : false,
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 1,
				"direction" : "backward",
				"indexBounds" : {
					"time" : [
						"(new Date(1451318392636), new Date(1400151858193)]"
					],
					"userId" : [
						"[ObjectId('53bb05e1386b18ce5b20d86b'), ObjectId('53bb05e1386b18ce5b20d86b')]",
						"[ObjectId('53bab1e2b5067de0061a4567'), ObjectId('53bab1e2b5067de0061a4567')]",
						"[ObjectId('53b831b49809bceb3fd2cfab'), ObjectId('53b831b49809bceb3fd2cfab')]",
						"[ObjectId('53b6eae20c4a07fe63db10b3'), ObjectId('53b6eae20c4a07fe63db10b3')]",
						"[ObjectId('53b494fb9809bceb3fd25c89'), ObjectId('53b494fb9809bceb3fd25c89')]",
						"[ObjectId('53af045ebcc79931692a4aa7'), ObjectId('53af045ebcc79931692a4aa7')]",
						"[ObjectId('53acbfb3cb9f89250d02c48a'), ObjectId('53acbfb3cb9f89250d02c48a')]",
						"[ObjectId('53a8cc6511b9cc964959c365'), ObjectId('53a8cc6511b9cc964959c365')]",
						"[ObjectId('53a80878cde71b653ed82539'), ObjectId('53a80878cde71b653ed82539')]",
						"[ObjectId('53a561b2663e61b528241bc4'), ObjectId('53a561b2663e61b528241bc4')]",
						"[ObjectId('5382f987832983fb3a5ae9d4'), ObjectId('5382f987832983fb3a5ae9d4')]",
						"[ObjectId('537f543778f03be40e598ade'), ObjectId('537f543778f03be40e598ade')]",
						"[ObjectId('537468fff88919d05eee322c'), ObjectId('537468fff88919d05eee322c')]",
						"[ObjectId('5373bcfef88919d05eee3149'), ObjectId('5373bcfef88919d05eee3149')]"
					],
					"action" : [
						"[\"userUpdatedTagline\", \"userUpdatedTagline\"]",
						"[\"userUpdatedPicture\", \"userUpdatedPicture\"]",
						"[\"signin\", \"signin\"]",
						"[\"share\", \"share\"]",
						"[\"objectShare_forumPost\", \"objectShare_forumPost\"]",
						"[\"objectShare_forumComment\", \"objectShare_forumComment\"]",
						"[\"objectLike_userUpdatedTagline\", \"objectLike_userUpdatedTagline\"]",
						"[\"objectLike_userProfileImage\", \"objectLike_userProfileImage\"]",
						"[\"objectLike_forumPost\", \"objectLike_forumPost\"]",
						"[\"objectLike_forumComment\", \"objectLike_forumComment\"]",
						"[\"like\", \"like\"]",
						"[\"forumPost_edit\", \"forumPost_edit\"]",
						"[\"forumPost_add\", \"forumPost_add\"]",
						"[\"forumComment_add\", \"forumComment_add\"]",
						"[\"follow\", \"follow\"]",
						"[\"fbShare\", \"fbShare\"]",
						"[\"fbMessengerAttempt\", \"fbMessengerAttempt\"]",
						"[\"articleShare\", \"articleShare\"]",
						"[\"articleLike\", \"articleLike\"]",
						"[\"TwitterAttempt\", \"TwitterAttempt\"]",
						"[\"GooglePlusAttempt\", \"GooglePlusAttempt\"]",
						"[\"ANSAttempt\", \"ANSAttempt\"]"
					]
				},
				"keysExamined" : 2209,
				"dupsTested" : 0,
				"dupsDropped" : 0,
				"seenInvalidated" : 0
			}
		},
		"allPlansExecution" : [ ]
	},
	"serverInfo" : {
		"host" : "mongo-082",
		"port" : 27017,
		"version" : "3.2.12",
		"gitVersion" : "ef3e1bc78e997f0d9f22f45aeb1d8e3b6ac14a14"
	},
	"ok" : 1
}
>

However, both index sizes are much higher than the initially used index:

"indexSizes" : {
		"_id_" : 32768,
		"action_1_userId_1_time_1" : 36864,
		"time_1_action_1_userId_1" : 90112,
		"time_1_userId_1_action_1" : 90112
	},


In our case it was better to build a bigger index than to have an uncovered query because the latter had to load documents into memory which resulted in spikes due to reads upto 600 MB/sec which made the database far less responsive during these spikes.

Comment by David Storch [ 02/May/16 ]

Hi andrey.hohutkin@gmail.com,

Thanks for the detailed issue report. Currently, queries which require an in-memory SORT stage (as opposed to queries which satisfy a sort request via an index scan), can never be covered. If you're curious, this is enforced by the query planner here:

https://github.com/mongodb/mongo/blob/r3.3.5/src/mongo/db/query/planner_analysis.cpp#L511-L518

There is an existing improvement request for allowing queries like this to be covered: see SERVER-15221. I am closing this ticket as a duplicate, but please watch and vote for SERVER-15221.

Best,
Dave

Comment by Ramon Fernandez Marina [ 02/May/16 ]

Thanks for your report andrey.hohutkin@gmail.com, I'm able to reproduce the behavior you describe and I'm investigating.

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