-
Type: Bug
-
Resolution: Gone away
-
Priority: Major - P3
-
None
-
Affects Version/s: 3.4.9
-
Component/s: Querying
-
None
-
Query
-
ALL
I'm trying to implement paging using range queries. I have a collection called events that has, among others, a time attribute (type date).
I defined a compound index with key
{"time" : 1, "_id" : 1}. I use the script below to retrieve the events in pages.
The query latency is very good (< 20 ms) for all queries except the last one. An explain shows that, unlike the other queries, the last query does not use the compound index. It looks like this has something to do with the value of startTime being equal to or very close to the value of lastTime.
var startTime = ISODate("2017-09-18T00:00:00Z"); var endTime = ISODate("2017-09-18T00:30:00Z"); var pageSize = 555; var maxTimeMS = 6000; var dbName = "foo"; var totalLatency = 0; var skip = 0; var lastTime = null; var lastEventId = null; var firstPage = true; while (true) { var start = new Date(); var eventsRead = 0; if (firstPage) { firstPage = false; db.getSiblingDB(dbName).events.find({ time: { $gte: startTime, $lte: endTime}}).sort({ "time" : -1 , "_id" : -1}).limit(pageSize).maxTimeMS(maxTimeMS).forEach(function(s) { eventsRead++; lastTime = s.time; lastEventId = s._id; } ); } else { db.getSiblingDB(dbName).events.find({ time: { $gte: startTime, $lte: endTime}, "$or" : [ { "time" : { "$lt" : lastTime}} , { "time" : lastTime , "_id" : { "$lt" : lastEventId }} ] }).sort({ "time" : -1 , "_id" : -1}).limit(pageSize).maxTimeMS(maxTimeMS).forEach(function(s) { eventsRead++; lastTime = s.time; lastEventId = s._id; } ); } var elapsed = new Date() - start; totalLatency += elapsed; skip = skip + eventsRead; print("page " + skip + ", elapsed " + elapsed); if (eventsRead < pageSize) { break; } } print("Total events read: " + skip + ", total latency: " + totalLatency);
- related to
-
SERVER-13732 Predicates in top-level implicit AND query not considered when generating index access plan for contained OR
- Closed
-
SERVER-24027 The planner does not consider reversing index scan direction in order to obtain a SORT_MERGE plan
- Closed