distinct('x', {y:1}) should be able to use DISTINCT_SCAN with an index on {x:1, y:1}

XMLWordPrintableJSON

    • Query Optimization
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      This would work similarly to distinct('x'), but for each distinct x value discovered, it should then seek to the first entry with that x and a y that would match the query. If you land in a matching x and y, then record the value and jump. Either way, after that jump to the next possible x and repeat. This should do roughly 2x(number of distinct x values) seeks into the index, and no record fetches.

      Right now it just does a table scan which is likely to be much more expensive:

      > db.foo.createIndex({x:1, y:1})
{
	"numIndexesBefore" : 2,
	"numIndexesAfter" : 3,
	"createdCollectionAutomatically" : false,
	"ok" : 1
}
> db.foo.explain().distinct('x', {y:1})
{
	"explainVersion" : "1",
	"queryPlanner" : {
		"namespace" : "test.foo",
		"parsedQuery" : {
			"y" : {
				"$eq" : 1
			}
		},
		"indexFilterSet" : false,
		"queryHash" : "3F4E3077",
		"planCacheKey" : "75804815",
		"optimizationTimeMillis" : 0,
		"maxIndexedOrSolutionsReached" : false,
		"maxIndexedAndSolutionsReached" : false,
		"maxScansToExplodeReached" : false,
		"prunedSimilarIndexes" : false,
		"winningPlan" : {
			"isCached" : false,
			"stage" : "COLLSCAN",
			"filter" : {
				"y" : {
					"$eq" : 1
				}
			},
			"direction" : "forward"
		},
		"rejectedPlans" : [ ]
	},
	"command" : {
		"distinct" : "foo",
		"key" : "x",
		"query" : {
			"y" : 1
		},
		"$db" : "test"
	},
	"serverInfo" : {
		"host" : "ws",
		"port" : 27017,
		"version" : "8.1.0-alpha-1988-gb141e74",
		"gitVersion" : "b141e743215ced3bfea98f2353355b9f3a982369"
	},
	"serverParameters" : {
		"internalQueryFacetBufferSizeBytes" : 104857600,
		"internalQueryFacetMaxOutputDocSizeBytes" : 104857600,
		"internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600,
		"internalDocumentSourceGroupMaxMemoryBytes" : 104857600,
		"internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600,
		"internalQueryProhibitBlockingMergeOnMongoS" : 0,
		"internalQueryMaxAddToSetBytes" : 104857600,
		"internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600,
		"internalQueryFrameworkControl" : "trySbeRestricted",
		"internalQueryPlannerIgnoreIndexWithCollationForRegex" : 1
	},
	"ok" : 1
}

            Assignee:
            Unassigned
            Reporter:
            Mathias Stearn
            Votes:
            0 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated: