[SERVER-38102] Project stage prevents sort stage from using index Created: 13/Nov/18  Updated: 06/Dec/22  Resolved: 13/Nov/18

Status: Closed
Project: Core Server
Component/s: Aggregation Framework, Index Maintenance
Affects Version/s: 4.0.4, 4.1.5
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: ChangZhuo Chen Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-26442 Push $sort before $project and $addFi... Open
Assigned Teams:
Query
Operating System: ALL
Steps To Reproduce:

MongoDB version is 4.1.5

> db.version()
4.1.5

Create base collection, and its index.

> db.base.createIndex({x:1})
{
        "createdCollectionAutomatically" : true,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

Explain sort query on base collection. It uses IXSCAN.

> db.base.find({}).sort({x:1}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.base",
                "indexFilterSet" : false,
                "parsedQuery" : {
 
                },
                "queryHash" : "B7791BAD",
                "planCacheKey" : "B7791BAD",
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "x" : 1
                                },
                                "indexName" : "x_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "x" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "x" : [
                                                "[MinKey, MaxKey]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "8ab9c3a1df86",
                "port" : 27017,
                "version" : "4.1.5",
                "gitVersion" : "f3349bac21f200cf2f9854eb51b359d3cbee3617"
        },
        "ok" : 1
}

Create a view based on base collection

> db.createView("view", "base", [{$project:{x:1}}])
{ "ok" : 1 }

Explain sort query on view collection. It uses COLLSCAN.

> db.view.find({}).sort({x:1}).explain()
{
        "stages" : [
                {
                        "$cursor" : {
                                "query" : {
 
                                },
                                "fields" : {
                                        "x" : 1,
                                        "_id" : 1
                                },
                                "queryPlanner" : {
                                        "plannerVersion" : 1,
                                        "namespace" : "test.base",
                                        "indexFilterSet" : false,
                                        "parsedQuery" : {
 
                                        },
                                        "queryHash" : "8B3D4AB8",
                                        "planCacheKey" : "8B3D4AB8",
                                        "winningPlan" : {
                                                "stage" : "COLLSCAN",
                                                "direction" : "forward"
                                        },
                                        "rejectedPlans" : [ ]
                                }
                        }
                },
                {
                        "$project" : {
                                "_id" : true,
                                "x" : true
                        }
                },
                {
                        "$sort" : {
                                "sortKey" : {
                                        "x" : 1
                                }
                        }
                }
        ],
        "ok" : 1
}

Participants:

 Description   

View cannot use underlying collection index properly for sort.



 Comments   
Comment by Danny Hatcher (Inactive) [ 13/Nov/18 ]

Per an internal discussion, we will close this ticket as a duplicate of SERVER-26442 as the solution there will resolve this problem for views.

Comment by Danny Hatcher (Inactive) [ 13/Nov/18 ]

Through some testing on 4.0.4, it appears that this is not restricted to views. Aggregation is not using the index for a sort if the field is first projected.

> db.base.createIndex({x:1})
{
	"createdCollectionAutomatically" : true,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
> db.base.explain().aggregate([{$match:{}},{$sort:{x:1}}])
{
	"stages" : [
		{
			"$cursor" : {
				"query" : {
 
				},
				"sort" : {
					"x" : 1
				},
				"queryPlanner" : {
					"plannerVersion" : 1,
					"namespace" : "foo.base",
					"indexFilterSet" : false,
					"parsedQuery" : {
 
					},
					"winningPlan" : {
						"stage" : "FETCH",
						"inputStage" : {
							"stage" : "IXSCAN",
							"keyPattern" : {
								"x" : 1
							},
							"indexName" : "x_1",
							"isMultiKey" : false,
							"multiKeyPaths" : {
								"x" : [ ]
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 2,
							"direction" : "forward",
							"indexBounds" : {
								"x" : [
									"[MinKey, MaxKey]"
								]
							}
						}
					},
					"rejectedPlans" : [ ]
				}
			}
		}
	],
	"ok" : 1
}
> db.base.explain().aggregate([{$match:{}},{$project:{x:1}},{$sort:{x:1}}])
{
	"stages" : [
		{
			"$cursor" : {
				"query" : {
 
				},
				"fields" : {
					"x" : 1,
					"_id" : 1
				},
				"queryPlanner" : {
					"plannerVersion" : 1,
					"namespace" : "foo.base",
					"indexFilterSet" : false,
					"parsedQuery" : {
 
					},
					"winningPlan" : {
						"stage" : "COLLSCAN",
						"direction" : "forward"
					},
					"rejectedPlans" : [ ]
				}
			}
		},
		{
			"$project" : {
				"_id" : true,
				"x" : true
			}
		},
		{
			"$sort" : {
				"sortKey" : {
					"x" : 1
				}
			}
		}
	],
	"ok" : 1
}

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