[SERVER-5331] Use heuristics to choose selective index bounds within $all Created: 19/Mar/12  Updated: 13/Jul/23

Status: Backlog
Project: Core Server
Component/s: Querying
Affects Version/s: 2.0.3
Fix Version/s: None

Type: Improvement Priority: Minor - P4
Reporter: Nic Cottrell (Personal) Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 8
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
is related to SERVER-1000 $all with query optimizer Closed
Assigned Teams:
Query Optimization
Participants:

 Description   

I was just tracking some slow queries and found queries like:

db.Example.find({ "indices.text":

{ $all: [ "le", "mois", "suivant" ] }

, lc: "fra", group: "all" })

giving

{
"cursor" : "BtreeCursor indices.text_1_lc_1_group_1",
"nscanned" : 244471,
"nscannedObjects" : 244471,
"n" : 34,
"millis" : 6211,
"nYields" : 6,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" :

{ "indices.text" : [ [ "le", "le" ] ], "lc" : [ [ "fra", "fra" ] ], "group" : [ [ "all", "all" ] ] }

}

but

> db.Example.find({ "indices.text":

{ $all: [ "suivant", "le", "mois" ] }

, lc: "fra", group: "all" }).explain();
{
"cursor" : "BtreeCursor indices.text_1_lc_1_group_1",
"nscanned" : 1948,
"nscannedObjects" : 1948,
"n" : 34,
"millis" : 102,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" :

{ "indices.text" : [ [ "suivant", "suivant" ] ], "lc" : [ [ "fra", "fra" ] ], "group" : [ [ "all", "all" ] ] }

}

I can manually re-order my inputs to the $all putting longest strings first, but perhaps Mongo should have similar logic built in since longer strings should naturally be less common and therefore need less scanning?


Generated at Thu Feb 08 03:08:34 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.