[SERVER-33511] Same Query Shape with different regex can give suboptimal index selection Created: 27/Feb/18 Updated: 27/Apr/22 Resolved: 01/Mar/18 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Performance, Querying |
| Affects Version/s: | 3.4.13 |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Minor - P4 |
| Reporter: | Oliver Butterfield | Assignee: | Chris Harris |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||
| Participants: | |||||||||||||||||||||
| Description |
|
Hi there We have a collection with a large number of documents and indexes. When we run the following query:
we see that it uses this index:
This is fine. It leads to the following query plan being cached:
This is fine as well. But as soon as we change the regex for one where we aren't using the caret to symbolise "begins with", the performance is drastically reduced:
The issue here is that (for whatever reason) it is not using the existing cached query plan for this shape, and ends up picking a poorer choice of index - the two queries with the different regexes still have the same shape. Moreover, once it has done this, this new (poor) choice is cached and will then be used for the /^mohamed ali/ queries leading to much worse performance than they had originally. Essentially, the upshot is that, following a flush of the query plan cache, you can get great performance on the "begins with" version of the query, until you do a single search without the caret, and from then on, the begins-with version will also perform badly. Is this expected behaviour? Many thanks |
| Comments |
| Comment by Oliver Butterfield [ 08/Mar/18 ] | ||
|
Hi Dave Many thanks for following up on this! | ||
| Comment by David Storch [ 07/Mar/18 ] | ||
|
We've opened Best, | ||
| Comment by Chris Harris [ 01/Mar/18 ] | ||
|
Hi Oliver, Thank you for opening this ticket with detailed information about your observations and investigation. Currently the pattern applied to a regex query is not a distinguishing factor when it comes to query shapes. As a result, you are observing a manifestation of As such, we will proceed to close this ticket out as a duplicate of Best, | ||
| Comment by Oliver Butterfield [ 27/Feb/18 ] | ||
|
Apologies, my query pasting was a bit messed up. Hopefully it should be clear that instead of
it should read
|