[ https://issues.apache.org/jira/browse/OAK-2007?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14079323#comment-14079323 ]
Przemo Pakulski edited comment on OAK-2007 at 7/30/14 2:50 PM: --------------------------------------------------------------- On my instance (50k chunks) query with _id_ hint takes up to 8 minutes {code} > db.blobs.find({lastMod : {$lte :1306726400279}}, > {_id:1}).hint({_id:1}).explain() { "cursor" : "BtreeCursor _id_", "isMultiKey" : false, "n" : 0, "nscannedObjects" : 50204, "nscanned" : 50204, "nscannedObjectsAllPlans" : 50204, "nscannedAllPlans" : 50204, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 15280, "nChunkSkips" : 0, "millis" : 462653, "indexBounds" : { "_id" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] }, "server" : "aem6-stable:27017", "filterSet" : false } {code} The same query without any hints and index on _lastMod_ created is instant {code} > db.blobs.find({lastMod : {$lte :1306726400279}}, {_id:1}).explain() { "cursor" : "BtreeCursor lastMod_1", "isMultiKey" : false, "n" : 0, "nscannedObjects" : 0, "nscanned" : 0, "nscannedObjectsAllPlans" : 0, "nscannedAllPlans" : 0, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { "lastMod" : [ [ -Infinity, 1306726400279 ] ] }, "server" : "aem6-stable:27017", "filterSet" : false } {code} was (Author: ppakulski): On my instance (50k chunks) query with _id_ hint takes up to 8 minutes {code} > db.blobs.find({lastMod : {$lte :1306726400279}}, > {_id:1}).hint({_id:1}).explain() { "cursor" : "BtreeCursor _id_", "isMultiKey" : false, "n" : 0, "nscannedObjects" : 50204, "nscanned" : 50204, "nscannedObjectsAllPlans" : 50204, "nscannedAllPlans" : 50204, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 15280, "nChunkSkips" : 0, "millis" : 462653, "indexBounds" : { "_id" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] }, "server" : "cqzg-aem6-stable:27017", "filterSet" : false } {code} The same query without any hints and index on _lastMod_ created is instant {code} > db.blobs.find({lastMod : {$lte :1306726400279}}, {_id:1}).explain() { "cursor" : "BtreeCursor lastMod_1", "isMultiKey" : false, "n" : 0, "nscannedObjects" : 0, "nscanned" : 0, "nscannedObjectsAllPlans" : 0, "nscannedAllPlans" : 0, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { "lastMod" : [ [ -Infinity, 1306726400279 ] ] }, "server" : "cqzg-aem6-stable:27017", "filterSet" : false } {code} > MongoBlobStore improvements > --------------------------- > > Key: OAK-2007 > URL: https://issues.apache.org/jira/browse/OAK-2007 > Project: Jackrabbit Oak > Issue Type: Improvement > Components: blob, mongomk > Affects Versions: 1.0.2 > Reporter: Przemo Pakulski > Attachments: OAK-2007.patch > > > To collect all chunk identifiers (getAllChunkIds) hint is used to force "_id" > index usage. > This index doesn't help because query is using "lastMod" field. It also > doesn't allow to leverage custom indexes if created. In result queries are > pretty slow. > Additionally consider to create index on "lastMod" field to speed-up all > queries using this criteria. -- This message was sent by Atlassian JIRA (v6.2#6252)