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

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

    • Query Optimization

      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 Unassigned
            Reporter:
            mathias@mongodb.com Mathias Stearn
            Votes:
            0 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated: