Meaning of SUBSCAN in explain() query

Working with Data
1

Hi All,

I run an explain on my query and get a “SUBSCAN” meaning stage, but could find a documentation for it. What does it mean ?

Below is the full result:

{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "db.collection",
		"winningPlan" : {
			"stage" : "SUBSCAN",
			"inputStage" : {
				"stage" : "SORT",
				"sortPattern" : {
					"A" : 1,
					"B" : 1
				},
				"inputStage" : {
					"stage" : "IXSCAN",
					"indexName" : "my_index",
					"direction" : "forward"
				}
			}
		}
	},
	"serverInfo" : {
		"host" : "myinstance",
		"port" : 27017,
		"version" : "3.6.0"
	},
	"ok" : 1
}

so the query is basically:

db.collection.find({conditions}).sort({A:1, B:1}).explain()

If I remove the sort, then it’s a straightforward IXSCAN case:

{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "db.collection",
		"winningPlan" : {
			"stage" : "IXSCAN",
			"indexName" : "my_index",
			"direction" : "forward"
		}
	},
	"serverInfo" : {
		"host" : "myinstance",
		"port" : 27017,
		"version" : "3.6.0"
	},
	"ok" : 1
}

My wild guess would be, after getting all the documents that match the query criteria (which uses the index my_index), it must sort all the values manually based on the sort values (since there’s no range that it could leverage), hence the stage is “SUBSCAN”, so not quite COLSCAN but not optimal either. Am I getting it correctly ?

Thanks for all comments and suggestions.
Tuan

2

Hi @Tuan_Dinh1,

Yes your guess seems to be correct .

When an index can only satisfy a sort it has to do additionally a partial scan to filter the criteria section.

Our recommendation is to build index supporting Equity , Sort and Range order.

Thanks
Pavel