-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
Labels:None
-
Query Optimization
-
Query 2017-10-23
-
(copied to CRM)
The MultiPlanStage picks the best plan by "working all the plans, stopping when a plan hits EOF or returns some fixed number of results". But if the query returns no results, this stopping condition can take a very long time and we've essentially done a full index scan before returning.
Original Description
I updated my insert benchmark scripts to do full index scans for the PK and secondary indexes. The queries are written to do full index scans and find no rows. There are two loops for the timing – first run the query, second explain the query. Two of the queries have slow explains – queries 2 and 4. From the explain output they each have rejected plans that use a sort.
For the output below, there are 4 queries. Query 1 is the PK scan and then queries 2, 3 and 4 are full scans of each secondary index. The data below is from a setup with 10M docs in one collection. This is the time to scan each index:
Query 1 scan 9 seconds for 1 tables and explain 0 Query 2 scan 24 seconds for 1 tables and explain 0 Query 3 scan 21 seconds for 1 tables and explain 0 Query 4 scan 26 seconds for 1 tables and explain 0
And this is the time to do the explain for each query. Note that the explain for queries 2 and 4 takes more than 10 seconds. For the queries with slow explains there were rejected plans that use a sort.
Query 1 scan 0 seconds for 1 tables and explain 1 Query 2 scan 12 seconds for 1 tables and explain 1 Query 3 scan 0 seconds for 1 tables and explain 1 Query 4 scan 11 seconds for 1 tables and explain 1
A sample command line for repeating the test using https://github.com/mdcallag/mytools/blob/master/bench/ibench/iql.sh
bash iql.sh mongo "" /usr/bin/mongo /data/m/mo sdb 1 1 yes no no 0 no 10000000
Planner output for the slow explain with query 2. There is an index on (price,customerid) and the query is:
db.pi1.find"'({ price : { $gte : 0 }, customerid : { $lt : 0 } }, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "ib.pi1",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"customerid" : {
"$lt" : 0
}
},
{
"price" : {
"$gte" : 0
}
}
]
},
"winningPlan" : {
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"price" : 1,
"customerid" : 1
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"price" : 1,
"customerid" : 1
},
"indexName" : "pc",
"isMultiKey" : false,
"multiKeyPaths" : {
"price" : [ ],
"customerid" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"price" : [
"[0.0, inf.0]"
],
"customerid" : [
"[-inf.0, 0.0)"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "PROJECTION",
"transformBy" : {
"_id" : 0,
"price" : 1,
"customerid" : 1
},
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"price" : 1,
"customerid" : 1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"price" : 1,
"dateandtime" : 1,
"customerid" : 1
},
"indexName" : "pdc",
"isMultiKey" : false,
"multiKeyPaths" : {
"price" : [ ],
"dateandtime" : [ ],
"customerid" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"price" : [
"[0.0, inf.0]"
],
"dateandtime" : [
"[MinKey, MaxKey]"
],
"customerid" : [
"[-inf.0, 0.0)"
]
}
}
}
}
}
}
]
},
"serverInfo" : {
"host" : "nuc1",
"port" : 27017,
"version" : "3.4.6-1.7",
"gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2"
},
"ok" : 1
}
- is related to
-
SERVER-24312 Mongo Query Taking Lot of Time Selecting Index
-
- Closed
-
-
-
- Backlog
-
- related to
-
-
- Closed
-
-
-
- Closed
-