Description
It seems that the query optimizer doesn't understand that a boolean variable is the equivalent of an equality operation, thereby ignoring valid indexes under the following circumstances.
I have a collection 'clients' with the following index:
indexes: [{type: 'hash', fields: ['online','userid'], minLength: 3, unique : false}]
(online is a boolean, and userid is a string)
when I call:
LET userId = "users/e8c7dd8f4d5cfecaf19620d21dd13d92d7c2a4b5"
FOR y IN clients
FILTER y.online == true && y.userid == userId
RETURN y._id`
the plan uses the correct index like this:
Id NodeType Est. Comment
1 SingletonNode 1 * ROOT
8 IndexRangeNode 1 - FOR y IN clients /* hash index scan /
4 CalculationNode 1 - LET #2 = y.online== true && y.userid== > "users/e8c7dd8f4d5cfecaf19620d21dd13d92d7c2a4b5" / simple expression / / collections used: y : clients /
5 FilterNode 1 - FILTER #2
6 CalculationNode 1 - LET #4 = y._id/ attribute expression / / collections used: y : clients /
7 ReturnNode 1 - RETURN #4/
However when I call:
LET userId = "users/e8c7dd8f4d5cfecaf19620d21dd13d92d7c2a4b5"
FOR y IN clients
FILTER y.online && y.userid == userId
RETURN y._id`
The execution plan resorts to a full collection scan:
Execution plan:
Id NodeType Est. Comment
1 SingletonNode 1 * ROOT
3 EnumerateCollectionNode 748 - FOR y IN clients /* full collection scan /
4 CalculationNode 748 - LET #2 = y.online&& y.userid== "users/e8c7dd8f4d5cfecaf19620d21dd13d92d7c2a4b5" / simple expression / / collections used: y : clients /
5 FilterNode 748 - FILTER #2
6 CalculationNode 748 - LET #4 = y._id/ attribute expression / / collections used: y : clients */
7 ReturnNode 748 - RETURN #4