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

$nin operation choose IXSCAN but not COLLSCAN stag

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Querying
    • None
    • Query
    • Fully Compatible
    • ALL

      $nin always need to return most of the data.so collscan always more efficiency than index scan.as index scan need to query twice.
      but have found mongo does not choose COLLSCAN for $nin operation.

      have created a collection conn in mongo 3.4 env.

      db.conn.insert({
      sourceId:
      targetId:
      })
      
      db.conn.createIndex({targetId:1})
      

      but when I execute

      db.conn.find({targetId: {$nin:['xxx']}}).explain("executionStats")

      find that mongo choose IXSCAN instead of COLLSCAN stage.

      when the document record is 100K,1M.
      it always choose COLLSCAN.

      and the result is that IXSCAN spend more time than COLLSCAN

      COLLSCAN:

      "executionStats" : {
      		"executionSuccess" : true,
      		"nReturned" : 999004,
      		"executionTimeMillis" : 406,
      		"totalKeysExamined" : 0,
      		"totalDocsExamined" : 1000000,
      		"executionStages" : {
      			"stage" : "COLLSCAN",
      			"filter" : {
      				"$not" : {
      					"targetId" : {
      						"$in" : [
      							"abc1234"
      						]
      					}
      				}
      			},
      

      INSCAN:

      "executionStats" : {
      		"executionSuccess" : true,
      		"nReturned" : 999004,
      		"executionTimeMillis" : 1863,
      		"totalKeysExamined" : 999005,
      		"totalDocsExamined" : 999004,
      		"executionStages" : {
      

      can anyone help explain this?

            Assignee:
            backlog-server-query Backlog - Query Team (Inactive)
            Reporter:
            cnStoneFang stone [X]
            Votes:
            0 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:
              Resolved: