-
Type:
Bug
-
Resolution: Won't Fix
-
Priority:
Major - P3
-
None
-
Affects Version/s: 2.4.10
-
Component/s: Querying
-
None
-
ALL
-
None
-
3
-
None
-
None
-
None
-
None
-
None
-
None
Hello,
Can't select the correct Index to Use !
Actually on forcing the use of a compounf Index that I have created : « PTY_OK_DS_DSL_PF_PT » with hint :
db.OperationalData.find(
{"PeriodType":"hour","OperatorKey":"DE-TSO-0004",
"PeriodFrom":{"$lte":"2014-11-01T00:00:00+01:00"},
"PeriodTo":{"$gte":"2014-10-01T00:00:00+02:00"},
"DataSetLabel":{"$in":["Generic Indicators","CMP Unsuccessful requests","CMP Auctions","CMP Unavailable","Interruptions"]}}
).hint('PTY_OK_DS_DSL_PF_PT').explain(true)
Results:
"cursor" : "BtreeCursor PTY_OK_DS_DSL_PF_PT multi", "isMultiKey" : false, "n" : 53879, "nscannedObjects" : 53879, "nscanned" : 62632, "nscannedObjectsAllPlans" : 53879, "nscannedAllPlans" : 62632, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 254,
If we leave the choise to Mongo to select the optimal index (we remove the « hint ») then :
db.OperationalData.find(
{"PeriodType":"hour","OperatorKey":"DE-TSO-0004",
"PeriodFrom":{"$lte":"2014-11-01T00:00:00+01:00"},
"PeriodTo":{"$gte":"2014-10-01T00:00:00+02:00"},
"DataSetLabel":{"$in":["Generic Indicators","CMP Unsuccessful requests","CMP Auctions","CMP Unavailable","Interruptions"]}}
).explain(true)
"cursor" : "BtreeCursor PeriodTo_1",
"isMultiKey" : false,
"n" : 53879,
"nscannedObjects" : 2792389,
"nscanned" : 2792389,
"nscannedObjectsAllPlans" : 2801318,
"nscannedAllPlans" : 2803528,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 10,
"nChunkSkips" : 0,
"millis" : 11815, !!!!!!
The difference is very Big !
Does this issue is fixed in later version?
It is clearly that Monogo have problems with calculating the correct index to use ...
This can produce very critical performance problem.....
Can you please check.
This is urgent.
Thanks to all the team.
BR,