[SERVER-52868] Optimizing update query Created: 14/Nov/20 Updated: 08/Jan/21 Resolved: 08/Jan/21 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | NOVALUE Mitar | Assignee: | Edwin Zhou |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||
| Participants: | |||||||||
| Description |
|
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:
With the:
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. |
| Comments |
| Comment by Edwin Zhou [ 08/Jan/21 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi mitar, thanks for your reply. To clarify SERVER-13065, it is true that the query is selecting most of the documents, but the fundamental problem is similar: the winning plan performs multiple key scans in addition to a document scan, even though a faster collection scan would have given the same results. We agree that it would be beneficial if the system could figure out before executing the query that a COLLSCAN plan would be expected to outperform the indexed OR plan. I'm going to close this as a duplicate of SERVER-13065. At the moment, scheduling SERVER-13065 is likely contingent on a pretty dramatic overhaul of how the query optimizer works. We are currently toying with the idea of eventually rewriting how we select access paths to work like a more traditional optimizer, in which case we would indeed estimate the cost of a COLLSCAN plan versus the available indexed access paths, thereby achieving the suggestion tracked by SERVER-13065. I appreciate your participation in this discussion. Best, | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by NOVALUE Mitar [ 07/Jan/21 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Thank you for more insights and explanations. This is really useful for me to get better grasp. > Could you clarify which query you're referring to was auto-generated? The slower one, the one with $ne in the query. It tried to be smart and make the most selective query for the update. > At the moment, I suspect the issue we're narrowing down may be related to, or a duplicate of SERVER-13065, which addresses using a collection scan when it's faster than an index scan for queries. Do you think this ticket tracks the issue you're describing? I do not think so. In that case the slowness is because query is selecting almost all documents. But here this is not the case. I mean, at the high level, maybe. But I am not sure if the fix would be the same? > Choosing a more selective query predicate for your update by excluding the $ne stages, i.e., {{{ id: "foo" }}} can also help. Yes, so I think the question of this issue is, how could planner automatically determine to exclude those stages? Also, we should probably see which approach is fastest a) having indices and using $ne clauses b) having indices and not using $ne clauses c) not having indices and using $ne clauses d) not having indices and not using $ne clauses. I think we know that: b is faster than a c is faster than a But what about other options? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Edwin Zhou [ 10/Dec/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi mitar, I'd first like to extend my appreciation for this discussion! It really helps with narrowing down the specifics of your suggested improvement. I'd like to continue to clarify, and ask for your clarification on a few things.
This is not entirely true. There's truth that $ne can be a more expensive operator because it's not as selective and often matches a large portion of documents. However, indexes are used to perform this operation in your specific query. Taking a closer look at the execution stats, we can see that the performance is negatively impacted because the query performs two index scans to satisfy the non-selective $ne terms within $or. In my reproduction, 2,000,000 additional index keys are scanned along with 1,000,000 documents and takes 4246ms.
After dropping the indexes, the same exact query performs a collection scan instead, which only performs 1,000,000 document scans and takes 1733ms.
I hope this clarifies the negative performance impact you observed when querying on more precise conditions. Choosing a more selective query predicate for your update by excluding the $ne stages, i.e., {{{ id: "foo" }}} can also help. While it is expected behavior for MongoDB to select an indexed plan if one is available, I agree that it makes sense for the query optimizer to select a faster plan if available.
Could you clarify which query you're referring to was auto-generated? At the moment, I suspect the issue we're narrowing down may be related to, or a duplicate of SERVER-13065, which addresses using a collection scan when it's faster than an index scan for queries. Do you think this ticket tracks the issue you're describing? Best, Edwin | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by NOVALUE Mitar [ 10/Dec/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
I mean, convert the whole update query like that. So what I am trying to say is that it seems it is known that `$ne` conditions are expensive, or can be expensive, because indices cannot be used. But together with `$set`, one could infer that some of `$ne` are redundant. The query above was auto-generated. So now in retrospect I know how to fix our generator, but I was still surprised that such query with `$ne` costs more, I was even assuming, before profiling it, that it costs less because it is more precise in conditions. So I am surprised that if there are more conditions which should narrow down the search, the result is slower. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Edwin Zhou [ 09/Dec/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi mitar, Could you clarify what you mean by "make MongoDB query planer optimize one into the other?" Do you mean convert the query
into
as to avoid the added IXSCANs that the two terms in $or enable so the updates are faster? The issue I see here is there are two queries that are both different internally and externally. Is it accurate to narrow down the issue to why the query optimizer selects indexed plans over collection scan plans when the collection scan plan is evidently faster? Best, | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by NOVALUE Mitar [ 09/Dec/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Yes, I understand that internally they are different, but the issue is about trying to make MongoDB query planer optimize one into the other? Also, to me it is surprising that it is faster to just blindly write into all documents instead of trying to select those which to write and then decide not to write any. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Edwin Zhou [ 09/Dec/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi mitar, I apologize for being unclear about why I used .find() instead of .update(). To clarify, .updateMany() uses .find() under the hood to look for documents to update. You can verify this by running db.collection.updateMany without the parentheses. Since we can't use .explain() on .updateMany(), we can perform the same query using .find() with .explain(). Alternatively, we can use db.collection.explain(...).update(...,{ multi: true }):
These updates will still need to execute a .find() before updating the documents, and executionStats will show the performance of the query. The first query has an additional $or which will scan through all of the documents AND perform an additional 2 IXSCANs. While the queries have the same result, the plans executed are very different. Does this help clarify my observation that the queries are different? Best, | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by NOVALUE Mitar [ 08/Dec/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
My queries are update queries, not find queries. The two update queries are the same, they have the same result. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Edwin Zhou [ 08/Dec/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi mitar, The two queries in your description appear to be different. The first query includes an $or statement on two indexed fields.
The second query doesn't include $or and only matches on id.
Both queries scan the entire collection. The first query, which includes $or, performs 2 additional IXSCANS, explaining the slower response. Could you clarify what you mean when you say "the queries are equivalent"? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by NOVALUE Mitar [ 19/Nov/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
And this is for the second:
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by NOVALUE Mitar [ 19/Nov/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
So this is the profiler record for the first query:
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by David Storch [ 18/Nov/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
mitar eric.sedor, running explain on update operations has been supported since MongoDB 3.0. There is an example of how to do so using the mongo shell in the documentation here: https://docs.mongodb.com/manual/reference/method/db.collection.explain/#allplansexecution-mode | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Eric Sedor [ 17/Nov/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
mitar that's true but you can run the equivalent find operations to give us an idea of what plans are considered and chosen. Uploading artifacts to this ticket rather than hosting them elsewhere will be of big help us, so thank you in advance! | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by NOVALUE Mitar [ 16/Nov/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
It is not possible to use explain() with update queries. I am using 4.4.1. This and more you can see in my reproduction repository I linked above: https://gitlab.com/mitar/benchmark-mongo-ne-query. I have there logs from the profiler. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Eric Sedor [ 16/Nov/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi mitar, can you please attach the results of explain(true) for each of these queries, and let us know the MongoDB version you are using? |