Skip to content

Instantly share code, notes, and snippets.

@lrakai
Last active September 28, 2016 04:58
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save lrakai/8c240ae123e0099b0e7b96a647a8ee9c to your computer and use it in GitHub Desktop.
Save lrakai/8c240ae123e0099b0e7b96a647a8ee9c to your computer and use it in GitHub Desktop.
Mongo range query slow on objects inside array
var setup = function (numDocs) {
var batchSize = 1000;
function makeDoc(maxSubOrders = 10, minDate = ISODate("2014-01-01T00:00:00Z"), maxDate = ISODate("2016-09-27T00:00:00Z")) {
function randomDate(minDate, maxDate) {
return new Date(minDate.getTime() + Math.random() * (maxDate.getTime() - minDate.getTime()));
}
var numSubOrders = Math.floor(Math.random() * (maxSubOrders + 1));
var subOrders = [];
for (var subOrder = 0; subOrder < numSubOrders; subOrder++) {
subOrders.push({
suborder_id: subOrder.toString(),
last_modified: randomDate(minDate, maxDate)
});
}
return {
last_modified: randomDate(minDate, maxDate),
suborders: subOrders
};
}
function makeDocBatch(size) {
var batch = [];
for (var docCount = 0; docCount < size; docCount++) {
batch.push(makeDoc());
}
return batch;
}
function insertDocBatch(batchSize) {
var batch = makeDocBatch(batchSize);
var response = db[collectionName].insertMany(
batch,
{ ordered: false }
);
}
function index() {
db[collectionName].createIndex({ "last_modified": 1 });
db[collectionName].createIndex({ "suborders.last_modified": 1 });
}
for (var docs = 0; docs < numDocs; docs += batchSize) {
insertDocBatch(batchSize);
}
if (numDocs % batchSize) {
insertDocBatch(numDocs % batchSize);
}
index();
};
var queryBuilder = (function () {
function buildQuery(field, startDate, endDate) {
query = {};
query[field] = { $lt: endDate, $gt: startDate };
return query;
}
return {
lastModified: function (startDate, endDate) {
return buildQuery("last_modified", startDate, endDate);
},
subFindLastModified: function (startDate, endDate) {
return buildQuery("suborders.last_modified", startDate, endDate);
},
subElemMatchLastModified: function (startDate, endDate) {
var query = this.lastModified(startDate, endDate);
return { "suborders": { $elemMatch: query } };
}
}
})();
var numDocs = 1000000;
var collectionName = "subDocs";
var calculateTotalSubOrders = false;
var found = db.getCollectionNames().find(function (name) {
return name === collectionName;
});
if (!found) {
setup(numDocs);
}
if (calculateTotalSubOrders) {
var numOrders = db[collectionName].aggregate([
{ $project: { numOrders: { $size: "$suborders" } } },
{ $group: { _id: 1, numOrders: { $sum: "$numOrders" } } }
]).next().numOrders;
printjson({
numDocs: numDocs,
numOrders: numOrders
});
}
printjson(db[collectionName].find(queryBuilder.lastModified(ISODate("2016-09-15T00:00:00Z"), ISODate("2016-09-16T00:00:00Z"))).explain("allPlansExecution"));
printjson(db[collectionName].find(queryBuilder.subFindLastModified(ISODate("2016-09-15T00:00:00Z"), ISODate("2016-09-16T00:00:00Z"))).explain("allPlansExecution"));
printjson(db[collectionName].find(queryBuilder.subElemMatchLastModified(ISODate("2016-09-15T00:00:00Z"), ISODate("2016-09-16T00:00:00Z"))).hint({"suborders.last_modified":1}).explain("allPlansExecution"));
//printjson(db[collectionName].find(queryBuilder.lastModified(ISODate("2016-09-15T00:00:00Z"), ISODate("2016-09-16T00:00:00Z"))).limit(10).hint({last_modified:1}).explain("allPlansExecution"));
//printjson(db[collectionName].find(queryBuilder.subLastModified(ISODate("2016-09-15T00:00:00Z"), ISODate("2016-09-16T00:00:00Z"))).limit(10).hint({"suborders.last_modified":1}).explain("allPlansExecution"));
@lrakai
Copy link
Author

lrakai commented Sep 28, 2016

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment