[SERVER-20989] Distinct not uses indexes Created: 17/Oct/15  Updated: 09/Dec/15  Resolved: 19/Oct/15

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 3.0.6, 3.0.7
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Konstantin Bodnia Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-13298 distinct doesn't use index to distinc... Closed
Operating System: ALL
Participants:

 Description   

Hi!
I have noticed that MongoDB won't use indexes when querying for a distinct value on a field. I will use it on some fields, but won't on others.

Here's the example:

db.product.createIndex({"_indexed.preventieve_mondzorg-max_bedrag_p_jr": 1});
db.runCommand({distinct: "product", key:"_indexed.preventieve_mondzorg-max_bedrag_p_jr"});

And that's what it produces:

{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 50,
	"numIndexesAfter" : 50,
	"note" : "all indexes already exist",
	"ok" : 1
}
{
	"values" : [
		"€ 250,- | 75%",
		"Geen dekking",
		"...",
	],
	"stats" : {
		"n" : 33660,
		"nscanned" : 0,
		"nscannedObjects" : 33660,
		"timems" : 12531,
		"planSummary" : "COLLSCAN"
	},
	"ok" : 1
}

On the other hand

db.product.createIndex({"free_choice.value": 1});
db.runCommand({distinct: "product", key:"free_choice.value"});

Will the index:

{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 50,
	"numIndexesAfter" : 50,
	"note" : "all indexes already exist",
	"ok" : 1
}
{
	"values" : [
		"gedeeltelijk",
		"geen",
		"ja"
	],
	"stats" : {
		"n" : 4,
		"nscanned" : 4,
		"nscannedObjects" : 4,
		"timems" : 2,
		"planSummary" : "DISTINCT { free_choice.value: 1.0 }"
	},
	"ok" : 1
}

So... what could be the difference between those two fields?
Is it a bug, or I am doing something wrong?



 Comments   
Comment by Daniel Pasette (Inactive) [ 19/Oct/15 ]

The important point is that they are multikey, dotted field name index definitions, not compound or not.

Comment by Konstantin Bodnia [ 19/Oct/15 ]

But my indexes are not compound. They're just a regular indexes. And they both have dot-notated field.

Comment by Daniel Pasette (Inactive) [ 19/Oct/15 ]

Please watch and vote up the duplicate issue: SERVER-13298

Generated at Thu Feb 08 03:55:54 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.