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

$nin operation choose IXSCAN but not COLLSCAN stag

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major - P3
    • Resolution: Duplicate
    • None
    • None
    • Querying
    • None
    • Fully Compatible
    • ALL

    Description

      $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?

      Attachments

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved: