[SERVER-31729] Views: index on underlying collection are not used if field is renamed by $project (first stage) Created: 26/Oct/17  Updated: 17/Nov/17  Resolved: 26/Oct/17

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

Type: Bug Priority: Major - P3
Reporter: Henri-Maxime Ducoulombier Assignee: Mark Agarunov
Resolution: Duplicate Votes: 0
Labels: read-only-views
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-27115 Track fields renamed by $project in a... Closed
Operating System: ALL
Participants:

 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)



 Comments   
Comment by Henri-Maxime Ducoulombier [ 26/Oct/17 ]

Thanks for the reply and heads-up on the fix coming in 3.6.

Still, do you think one should udpdate the doc for branch 3.4 since it appears it won't make it in version pre-3.6 ?

Comment by Mark Agarunov [ 26/Oct/17 ]

Hello hmducoulombier@marketing1by1.com,

Thank you for the report. Looking over this, it appears that the desired behavior you're describing is the same as the behavior detailed in SERVER-27115, tracking fields renamed by $project so that the index can be used, so I've closed this as a duplicate. Fortunately this is marked as fixed in version 3.5.8, therefore will be fixed in the upcoming MongoDB version 3.6 release. For more information and updates on this issue, please see SERVER-27115.

Thanks,
Mark

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