Indexes on MapField

[Nathan]

I have a (simplified) data model that looks like this:

class Counts(EmbeddedDocument):
num1 = IntField()
...
numN = IntField()

class Stats(Document):
count_map = MapField(EmbeddedDocumentField(Counts))


The main query I run on these models looks like this:

Stats.objects(_id=ID).only('count_map.key1', ... ,'count_map.keyN')

The count_map is often times very large (tens of thousands of entries)
so this query can take several seconds. Is there a way to improve this
via indexes? Ideally, I'd like to have a covered index over every
entry in Stats.count_map so it can be serviced w/o hitting disk at
all. Is there a way to add an index into the MapField? Ive tried
several variations and have not been able to improve this query speed.

Doing an explain on this query looks like the following:

{u'nYields': 0, u'allPlans': [{u'cursor': u'BtreeCursor _id_',
u'indexBounds': {u'_id': [[u'ID1', u'ID1']]}}, {u'cursor':
u'BtreeCursor _types_1', u'indexBounds': {u'_types': [[u'StatsModel',
u'StatsModel']]}}, {u'cursor': u'BtreeCursor _types_1_cnts_1',
u'indexBounds': {u'_types': [[u'StatsModel', u'StatsModel']], u'cnts':
[[{u'$minElement': 1}, {u'$maxElement': 1}]]}}, {u'cursor':
u'BtreeCursor _types_1_cnts.*_1', u'indexBounds': {u'_types':
[[u'StatsModel', u'StatsModel']], u'cnts.*': [[{u'$minElement': 1},
{u'$maxElement': 1}]]}}, {u'cursor': u'BtreeCursor _types_1_cnts.a_1',
u'indexBounds': {u'_types': [[u'StatsModel', u'StatsModel']],
u'cnts.a': [[{u'$minElement': 1}, {u'$maxElement': 1}]]}}, {u'cursor':
u'BasicCursor', u'indexBounds': {}}], u'nChunkSkips': 0, u'millis': 1,
u'n': 1, u'cursor': u'BtreeCursor _id_', u'oldPlan': {u'cursor':
u'BtreeCursor _id_', u'indexBounds': {u'_id': [[u'ID1', u'ID1']]}},
u'indexBounds': {u'_id': [[u'ID1', u'ID1']]}, u'nscannedObjects': 1,
u'isMultiKey': False, u'indexOnly': False, u'nscanned': 1}

[Dan Crosta]

Hi Nathan,

I'm not sure if an index here will help you, especially since you are looking up by _id already. There are some limitations with covered indexes, specifically that they can only return top-level document entries (though this may change in the future). However, even if this were not the case, since the index would have to hold the count_map contents, it would likely be about as large as your collection itself, so you would be no more likely to be able to serve the results of this query from RAM from a covered index than from the collection itself.

One possible cause of the slowness you're seeing could be in how either MongoDB or Mongoengine are processing the field inclusion/exclusion lists. If you have many arguments to the .only(...) call (you don't say how many you are specifying), one could imagine that both in the database and in the ODM processing this list could take considerable time, possibly more than in your application code itself. It's certainly the case that MongoDB has to do more work with field inclusion/exclusion, as it has to create a new BSON document to return to you, rather than just streaming the BSON from memory in the data files. Have you tried returning full documents from MongoDB, and then only selecting the sub-documents that you need (e.g. in a loop over the equivalent of the arguments you're passing to only())?

Hope this helps,
- Dan Crosta