[SERVER-92] $all doesn't work well with the query optimzer Created: 03/Jun/09  Updated: 12/Jul/16  Resolved: 05/Jun/09

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 0.9.4

Type: Improvement Priority: Major - P3
Reporter: Eliot Horowitz (Inactive) Assignee: Aaron Staple
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:

 Description   

take a look at the new test: jstests/index_check2.js

if this is too much - just assign it back to me.



 Comments   
Comment by Aaron Staple [ 05/Jun/09 ]

1fd840fd64b9147631262bd7ad09ede9b6f9fcbb

Comment by Aaron Staple [ 05/Jun/09 ]

You're right, that would be an improvement. I'll implement it.

Comment by Eliot Horowitz (Inactive) [ 04/Jun/09 ]

For $in, that makes sense, but for $all can't we just iterate only one of the entires? Ideally it would be the shortest, but random would be better than what is now.

I've updated the test to make it more clear.

Comment by Aaron Staple [ 04/Jun/09 ]

No, it's fine. 'nscanned' is the number of keys scanned, not documents.

Comment by Aaron Staple [ 04/Jun/09 ]

Seems like nscanned is too high though, I'll take a look.

Comment by Aaron Staple [ 04/Jun/09 ]

When you query an indexed field using $all, the index is scanned from the lowest value to the highest value in the $all array. We do the same thing for $in. It would be more efficient to skip quickly through the index from one value in the array to the next rather than scanning through all intermediate indexed values not present in the array, but that would require a fair amount of work to implement. You can see what's happening by looking at the start / end keys in the full explain() output:

> t.find( { tags:

{ $all: [ "tag6", "tag12" ] }

} ).explain();
{"cursor" : "BtreeCursor tags_1" , "startKey" :

{"tags" : "tag12"}

, "endKey" :

{"tags" : "tag6"}

, "nscanned" : 3460 , "n" : 60 , "millis" : 58 , "oldPlan" : {"cursor" : "BtreeCursor tags_1" , "startKey" :

{"tags" : "tag12"}

, "endKey" : {"tags" : "tag6"}} , "allPlans" : [{"cursor" : "BtreeCursor tags_1" , "startKey" :

{"tags" : "tag12"}

, "endKey" : {"tags" : "tag6"}}]}

A different query with explicit bounds has the same start and end keys:
> t.find( { tags:

{ $gte: "tag12", $lte: "tag6" }

} ).explain();
{"cursor" : "BtreeCursor tags_1" , "startKey" :

{"tags" : "tag12"}

, "endKey" :

{"tags" : "tag6"}

, "nscanned" : 3460 , "n" : 980 , "millis" : 81 , "oldPlan" : {"cursor" : "BtreeCursor tags_1" , "startKey" :

{"tags" : "tag12"}

, "endKey" : {"tags" : "tag6"}} , "allPlans" : [{"cursor" : "BtreeCursor tags_1" , "startKey" :

{"tags" : "tag12"}

, "endKey" : {"tags" : "tag6"}}]}

Generated at Thu Feb 08 02:53:00 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.