How to specify hint for a deleteMany operation

MongoDB Atlas App Services & Realm Atlas Triggers & Functions
,
1

Hi everyone,

In order to clean once per day one of my collection with a two-field query, I wanted to create a Scheduled Trigger on Atlas. I just need to run a “deleteMany” operation on 2 fields. This collection is pretty big, as there are more than 175 million documents, so I created a specific index for the query I need to run.

For a mysterious reason, if I run my operation ({ fieldA: { "$eq": null }, fieldB: { "$lt": someDate } }), the selected index is not the one I created. Rather than selecting the compound index I created ({ fieldA: 1, fieldB: 1}), MongoDB selects the prefix ({ fieldA: 1 }). every time. My query runs a first IXSCAN with the “fieldA” index, and then runs a COLLSCAN inside that subset of documents. However, a subset of more than 80 millions documents is still huge, so the query runs for an eternity.

The “hint” method resolves this issue, but I cannot make it work inside a Trigger Function. In the MongoDB API documentation of the “deleteMany” operation, hint does not appear, which is really blocking me right now.

Am I missing something ? Is there no way to run a “deleteMany” operation with “hint” option inside a Trigger Function ?

Thanks in advance ! :slightly_smiling_face:

PS : the Atlas Cluster I am targeting runs MongoDB v4.4, and no, I cannot make a TTL Index to cover my use case.

2

Hi @Yannis_Pages,

Unfortunately, it’s indeed the case that hints are not supported in App Services Functions, and not only for delete operations: feel free to add the suggestion to our feedback portal.

As a general advice, though, ensure that the function connected to your trigger runs as System: running it as Application means that, for each document, permissions need to be checked, and that slows down things considerably.

3

If you have indexes:
{ fieldA: 1, fieldB: 1}
{ fieldA: 1 }

Then the { fieldA: 1 } is redundant as the compound index will cover any queries that this one would.

I would suggest you hide the { fieldA: 1 } index and eventually delete it after testing everything is well.

4

I will, thank you for the link. I think this feature should be available in case other people encounter this kind of issue.

5

I did not know that feature, that solves my issue, thanks !

The index on fieldA is a TTL Index but Hidden TTL Indexes still delete the expired documents, so that’s perfect.

6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.