0
\$\begingroup\$

There are 1.3M documents in my collection (5.0 and hosted directly on mongodb.com) and there is an index on "topic" and a combined index on "topic" and another field. However the pymongo query of

mng.distinct('topic', {"topic": {"$regex": "XXX-XXX/XX/X/XXX/.*"}})

takes extremely long. I tried speeding it up by using case insensitive regex queries etc. but the speed stays the same (about 30 - 45 seconds per query). What is weird too is how inefficient the regex of MongoDB is. If I just get the distinct first and then do regex in python like this:

for topic in mongo.distinct('topic'):
    if re.search(self.gen_topic_base(), str(topic)):
        pass

it only takes a fraction of the time (about 1s per query). However this is inefficient and also to slow. Is there a more efficient way to do this that I am unaware of? I tried an aggregation pipeline, but it was even slower.

Those are the executionStats:

{
  executionStats: 
   { executionSuccess: true,
     nReturned: 9110,
     executionTimeMillis: 1501,
     totalKeysExamined: 9111,
     totalDocsExamined: 9110,
     executionStages: 
      { stage: 'FETCH',
        nReturned: 9110,
        executionTimeMillisEstimate: 1400,
        works: 9112,
        advanced: 9110,
        needTime: 1,
        needYield: 0,
        saveState: 14,
        restoreState: 14,
        isEOF: 1,
        docsExamined: 9110,
        alreadyHasObj: 0,
        inputStage: 
         { stage: 'IXSCAN',
           filter: { topic: BSONRegExp("^XXX-XXX-XXX/XX/0/XX/.*", "") },
           nReturned: 9110,
           executionTimeMillisEstimate: 573,
           works: 9112,
           advanced: 9110,
           needTime: 1,
           needYield: 0,
           saveState: 14,
           restoreState: 14,
           isEOF: 1,
           keyPattern: { topic: 1 },
           indexName: 'topic_index',
           isMultiKey: false,
           multiKeyPaths: { topic: [] },
           isUnique: false,
           isSparse: false,
           isPartial: false,
           indexVersion: 2,
           direction: 'forward',
           indexBounds: 
            { topic: 
               [ '["XXX-XXX-XXX/XX/0/XX/", "XXX-XXX-XXX/XX/0/XX0")',
                 '[/^XXX-XXX-XXX/XX/0/XX/.*/, /^XXX-XXX-XXX/XX/0/XX/.*/]' ] },
           keysExamined: 9111,
           seeks: 2,
           dupsTested: 0,
           dupsDropped: 0 } } },
  command: 
   { distinct: 'XXX-XXX-XXX',
     key: 'topic',
     query: { topic: BSONRegExp("^XXX-XXX-XXX/XX/0/XX/.*", "") },
     '$db': 'prod' },
  serverInfo: 
   { host: 'XXXXXX.mongodb.net',
     port: XXXXX,
     version: '5.0.3',
     gitVersion: 'XXXXXX' },
  serverParameters: 
   { internalQueryFacetBufferSizeBytes: 104857600,
     internalQueryFacetMaxOutputDocSizeBytes: 104857600,
     internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
     internalDocumentSourceGroupMaxMemoryBytes: 104857600,
     internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
     internalQueryProhibitBlockingMergeOnMongoS: 0,
     internalQueryMaxAddToSetBytes: 104857600,
     internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600 },
  ok: 1,
  '$clusterTime': 
   { clusterTime: Timestamp({ t: 1637139518, i: 1 }),
     signature: 
      { hash: Binary(Buffer.from("HASH", "hex"), 0),
        keyId: KEY_ID } },
  operationTime: Timestamp({ t: 1637139518, i: 1 })
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.