Hi @wan,
Thanks for the response.
Based on the
allPlansExecution, both execution plans have really high number ofworks(1.3M+). This usually implies that there is no index that can efficiently support the query shape using the given predicate values. This is inline with the documentation $regex: Index Use.
While it may be true none of the indexes are “efficient” we have certainly observed that one of them is consistently much more efficient than the other. And it seems like the information shown under the allPlansExecution property demonstrates that the execution plan should have known which plan to choose. The plan that used the email_1 index had an executionTimeMillisEstimate of 1340 while the plan that used the case_insensitive_email index had an executionTimeMillisEstimate of 4130. Those estimates line up pretty closely with what we have observed, and so it’s confusing that the query seemingly is saying “this plan looks like it will take longer…and that’s the one we’ll use”.
We do have workarounds in place by either refactoring our queries or by utilizing the hint() method, but it still feels like this is a bug in the way that query plans are resolved for regex queries.
Thanks again!