Is there a way to disable MongoDB's aggregation pipeline optimization?

Subash_Balakrishnan · 2024-12-18T17:29:45.595Z

Hello everyone,

I’m working with MongoDB aggregation pipelines and I’ve noticed that MongoDB automatically applies optimizations to the aggregation stages, such as reordering operations and merging certain stages. In some cases, this automatic optimization can affect the behavior of my pipeline in ways I don’t expect.

Is there any way to disable or control these optimizations, especially in cases where I want the pipeline to execute exactly as written? Are there any settings, flags, or techniques to prevent MongoDB from altering the execution order of aggregation stages?

Any insights or workarounds would be greatly appreciated!

Thank you!

steevej · 2024-12-18T20:13:57.214Z

Subash_Balakrishnan:

In some cases, this automatic optimization can affect the behavior of my pipeline in ways I don’t expect.

Could you please give an example of the above? It never happened to me but may be it does and I did not see it. So this is scary.

Subash_Balakrishnan · 2024-12-19T13:25:06.686Z

Hi steevej ,

I’m facing an issue where MongoDB’s aggregation pipeline optimizations are affecting the behavior of $sort and $skip stages unexpectedly.

Scenario:
Data:

{ “_id”: 1, “x”: “abc”, “y”: “def” }
{ “_id”: 2, “x”: “abc”, “y”: “efg” }
{ “_id”: 3, “x”: “xxx”, “y”: “xxx” }

Aggregation query:

db.col.aggregate([ { $sort: { x: 1 } }, { $skip: 0 } ])
Result: _id: 1, _id: 2, _id: 3 (as expected)

When changing $skip to 1:

db.col.aggregate([ { $sort: { x: 1 } }, { $skip: 1 } ])
Result: _id: 1, _id: 3 instead of _id: 2, _id: 3.

Problem:
It seems MongoDB is optimizing the pipeline and skipping documents in a non-deterministic way when there are duplicates for the sort key (x = “abc”). This behavior seems related to internal optimizations that reorder stages.

Question:

How can I prevent MongoDB from reordering or merging stages like $sort and $skip?
Is there a workaround to ensure $skip behaves as expected?

Thanks in advance for your help!

steevej · 2024-12-19T21:15:46.244Z

This has nothing to do with aggregation optimization or not.

This is a misunderstanding of the process. You $sort on x:1 only. There is no specified order on the fields you do not $sort on. You do not sort on _id, you must not expect a specific order. You must supply a tie breaker when values are equals. In your case, it looks like _id is important, so you must $sort with x:1,_id:1. For other, y would be more important so they would sort on x:1,y:1.

If you do not specify a tie breaker, the server assumes you do not care and output what is more efficient for it to do.

steevej · 2025-01-02T13:56:40.221Z

@Subash_Balakrishnan, 2 weeks ago I provided you with some information about your issue.

I would appreciate a followup from you. You may perhaps mark my post as the solution. Please do not let your thread die.

Thanks in advance.