[SERVER-14400] Using $min and $max on shard key doesn't target queries Created: 01/Jul/14 Updated: 18/Oct/23 |
|
| Status: | Backlog |
| Project: | Core Server |
| Component/s: | Querying, Sharding |
| Affects Version/s: | 2.4.10, 2.6.3, 2.7.2 |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Minor - P4 |
| Reporter: | Kevin Pulo | Assignee: | Backlog - Query Optimization |
| Resolution: | Unresolved | Votes: | 4 |
| Labels: | QFB, asya, neweng, query-44-grooming | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||
| Assigned Teams: |
Query Optimization
|
||||||||||||||||
| Sprint: | Query 2017-08-21 | ||||||||||||||||
| Participants: | |||||||||||||||||
| Description |
|
Using $gte and $lt on the shard key fields inside a query will cause it the query to be targeted to only the appropriate shards. However, using the $min and $max query operators (while hinting on the shard key) doesn't target the query to the relevant shard(s) in the same way. The explain() is missing indexBounds in this case, which may be the cause or at least related? I also tested 2.4.10 and 2.7.2 and the issue is present both. |
| Comments |
| Comment by Evan Goldenberg [ 18/Oct/23 ] | ||
|
Using $min/$max/$hint may be useful for operations that need to process all documents in a collection that uses hashed sharding, especially when the collection is very large and the operation needs to be split into many smaller pieces. For example, consider a collection foo that is sharded by {_id: "hashed"}. The collection has millions of documents, and we want to gradually visit them all, in small (but potentially parallel) batches - say 100 at a time. We can do this by partitioning the range of a 64-bit long (used for hashed sharding) into sub-ranges such that each sub-range should have approximately 100 documents in it. Then the query for a single batch might look like this:
Another benefit of doing it this way is that each batch can theoretically be targeted to a small number of shards - but that doesn't actually happen. The impact of this scales with the size of the MongoDB deployment, and can be quite significant in clusters with 100+ shards and collections with billions of documents. | ||
| Comment by Kevin Pulo [ 03/Jul/14 ] | ||
Yes, exactly. I also tested against a standalone, and the behaviour (wrt indexBounds) is the same - with $gte/$lt, there are indexBounds, with $min/$max there are no indexBounds. Despite this, in both cases, only the relevant range of the index is scanned. The use case (for me) is trying to write (simple) mongo shell code that works with documents within each chunk range. I could look up the shard and connect directly to it (and that's what orphanage.js does), but it's a pain and complicates the code considerably. For example, I would like to be be able to write the following code (to check for empty chunks - yes, I know 2.6 has features for this, but this is for 2.4, and this is just one example) and have it correctly scale by numchunks, not numchunks * numshards.
To do this by connecting to each shard individually, or trying to rewrite the chunk min/max as $gte/$lt on each component, would stop this from being a simple one-liner. | ||
| Comment by Asya Kamsky [ 01/Jul/14 ] | ||
|
Just tested this - the query is correct, results are correct and the explain looks correct to me as well, except for index bounds being missing, however, that matches the explain run directly on a primary of the shard so all mongos is doing is merging the results of explain. I think the only issue is that the query is not targeted when it could be. | ||
| Comment by Greg Studer [ 01/Jul/14 ] | ||
|
This is a pretty rarely-used query (and one with strange semantics) - is there a certain type of client operation this impacts badly? Generally this would result in very fast index lookups with no results on all but one shard. Also, just to clarify, there's no correctness problem aside from bounds not showing up in explain? Do they show up when run against a single server? |