I have a system which automatically construct queries. I found out that some queries it is constructing are much worse than equivalent manually made queries. I was surprised by that so I am reporting it to maybe see if query planer could be improved.

I have published benchmarks I am using here: https://gitlab.com/mitar/benchmark-mongo-ne-query

I am comparing the following query:

{  id: 'foo',  $or: [    {      'obj.name': {$ne: 'Foo'},    },    {      'obj.avatar': {$ne: 'https://example.com/foo.jpg'},    }  ],},{  $set: {    'obj.name': 'Foo',    'obj.avatar': 'https://example.com/foo.jpg',  },}

With the:

{  id: 'foo'},{  $set: {    'obj.name': 'Foo',    'obj.avatar': 'https://example.com/foo.jpg',  },}

As you see, the queries are equivalent. But the first one takes 2327 ms in my tests, while the second one takes 470 ms.

In my case most of the time the values have not changed. I run the query to make sure the values are kept in sync with the main collection (that sub-document is a copy of the full document from the main collection). So every time a document in main collection changes, I run update queries over all sub-documents embedded elsewhere. I tried to optimize this by making query update only if something changed, but my benchmark now is showing that this is in fact worse.

It is also interesting to note that the query planer made way off estimate in the second case, but made a pretty close estimate in the first case.