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 })
}