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

Views: index on underlying collection are not used if field is renamed by $project (first stage)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Duplicate
    • Affects Version/s: 3.4.9
    • Fix Version/s: None
    • Component/s: None
    • Labels:
    • Operating System:
      ALL

      Description

      This may be a clarification to be made in the documentation, but here is my bug report anyway on this subject.

      Say I have a collection of products (I removed ObjectID for readability) with an index on "unique_identifier" :

      Collection : products
      db.products.insert([
          {"unique_identifier":"A", "name": "My first product"},
          {"unique_identifier":"B", "name": "My second product"},
          {"unique_identifier":"C", "name": "My third product"}
      ]);
      db.products.createIndex({"unique_identifier":1});
      

      And I created a view on this collection, with a simple $project stage to rename the "unique_identifier" field to "sku" (because I need to)

          db.createView("pview", "products", [{"$project":{"sku":"$unique_identifier", "name":1}}]);
      

      Also, for demonstration purpose, I have another view without a rename but just to remove _id field:

          db.createView("pview2", "products", [{"$project":{"_id": 0}}]);
      

      Using explain on a simple find() operation, we can see that on pview, the index on "unique_identifier" is not used and a COLLSCAN is used, whether on pview2, it's an IXSCAN.

      db.pview.find({'unique_identifier':'A'}).explain()['stages'][0]["$cursor"]["queryPlanner"]["winningPlan"];
      { "stage" : "COLLSCAN", "direction" : "forward" }
       
       
      db.pview2.find({'unique_identifier':'A'}).explain()['stages'][0]["$cursor"]["queryPlanner"]["winningPlan"];
      {
      	"stage" : "FETCH",
      	"inputStage" : {
      		"stage" : "IXSCAN",
      		"keyPattern" : {
      			"unique_identifier" : 1
      		},
      		"indexName" : "unique_identifier_1",
      		"isMultiKey" : false,
      		"multiKeyPaths" : {
      			"unique_identifier" : [ ]
      		},
      		"isUnique" : false,
      		"isSparse" : false,
      		"isPartial" : false,
      		"indexVersion" : 2,
      		"direction" : "forward",
      		"indexBounds" : {
      			"unique_identifier" : [
      				"[\"A\", \"A\"]"
      			]
      		}
      	}
      }
      

      Note: I found out about this using $lookup on views with renamed fields to avoid a $lookup then a $project (without the index, the $lookup takes forever, obviously)

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: