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

count({$or: [{x: {$exists: false}}, {x: false}]}}) is extremely slow

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major - P3 Major - P3
    • None
    • None
    • None
    • None
    • ALL

    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.

       

      Attachments

        Activity

          People

            eric.sedor@mongodb.com Eric Sedor
            pva Peter
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: