[SERVER-40150] count({$or: [{x: {$exists: false}}, {x: false}]}}) is extremely slow Created: 15/Mar/19  Updated: 18/Mar/19  Resolved: 18/Mar/19

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

Type: Bug Priority: Major - P3
Reporter: Peter Assignee: Eric Sedor
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Participants:

 Description   

db.collection.count({$or: [{deleted: {$exists: false}}, \{deleted: false}]}}) is extremmely slow:

2019-03-15T10:16:06.783+0300 I COMMAND [conn32026] command smotreshka-purchase.accounts command: count { count: "accounts", query: { $or: [ { deleted:

Unknown macro: { $exists}

}, { deleted: false } ] } } planSummary: IXSCAN { deleted: 1 } keysExamined:775629 docsExamined:775628 numYields:6065 reslen:44 locks:{ Global: { acquireCount:

Unknown macro: { r}

}, Database: { acquireCount:

Unknown macro: { r}

}, Collection: { acquireCount:

Unknown macro: { r}

} } protocol:op_query 2373ms

 

I do have the index on deleted and as I understand it is expected to work. But it is not working in mongodb 3.4.10 and mongodb still examins lots of documents.

 

 

frontend:PRIMARY> db.accounts.find({ $or: [ { deleted: { $exists: false } }, { deleted: false } ] }).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "smotreshka-purchase.accounts",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$or" : [
				{
					"deleted" : {
						"$eq" : false
					}
				},
				{
					"$nor" : [
						{
							"deleted" : {
								"$exists" : true
							}
						}
					]
				}
			]
		},
		"winningPlan" : {
			"stage" : "SUBPLAN",
			"inputStage" : {
				"stage" : "FETCH",
				"filter" : {
					"$or" : [
						{
							"deleted" : {
								"$eq" : false
							}
						},
						{
							"$nor" : [
								{
									"deleted" : {
										"$exists" : true
									}
								}
							]
						}
					]
				},
				"inputStage" : {
					"stage" : "IXSCAN",
					"keyPattern" : {
						"deleted" : 1
					},
					"indexName" : "deleted_1",
					"isMultiKey" : false,
					"multiKeyPaths" : {
						"deleted" : [ ]
					},
					"isUnique" : false,
					"isSparse" : false,
					"isPartial" : false,
					"indexVersion" : 1,
					"direction" : "forward",
					"indexBounds" : {
						"deleted" : [
							"[null, null]",
							"[false, false]"
						]
					}
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "b612-app9-mongodb",
		"port" : 27017,
		"version" : "3.4.10",
		"gitVersion" : "078f28920cb24de0dd479b5ea6c66c644f6326e9"
	},
	"ok" : 1
}

I've tryed to google answer, but I failed to find solution. I've tryed sparse indes, but as I read documentation it is not supposed to work, so now I have ordinary index -  db.accounts.ensureIndex({deleted:1}).

Any hints are really appreciated.

 



 Comments   
Comment by Eric Sedor [ 18/Mar/19 ]

The behavior of count with respect to examining documents is described here. If you need further assistance devising a data model that allows you to count using contiguous key values, please post on the mongodb-user group or on Stack Overflow with the mongodb tag.

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