[SERVER-71071] [CQF] Investigate optimization failure when hinting with projection Created: 03/Nov/22  Updated: 20/Apr/23  Resolved: 27/Feb/23

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

Type: Task Priority: Major - P3
Reporter: Hana Pearlman Assignee: Matt Boros
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
is depended on by SERVER-73194 [CQF] Allow hints to go through CQF Closed
Duplicate
is duplicated by SERVER-73194 [CQF] Allow hints to go through CQF Closed
Related
related to SERVER-74403 Complete TODO listed in SERVER-71071 Closed
Assigned Teams:
Query Optimization
Sprint: QO 2023-02-06, QO 2023-02-20, QO 2023-03-06
Participants:

 Description   

The following query results in an optimization failure:

 > db.adminCommand({setParameter: 1, internalQueryFrameworkControl: "forceBonsai"})
{ "was" : "tryBonsai", "ok" : 1 }
 > db.foo.drop()
true
 > db.foo.insert({a: 1})
WriteResult({ "nInserted" : 1 })
 > db.foo.createIndex({a: 1})
{
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "createdCollectionAutomatically" : false,
        "ok" : 1
}
 > db.foo.find({}, {a: 1}).hint({a: 1})
Error: error: {
        "ok" : 0,
        "errmsg" : "Optimization failed.",
        "code" : 6808706,
        "codeName" : "Location6808706"
}
 > db.foo.find({a: 1}, {a: 1}).hint({a: 1})
{ "_id" : ObjectId("6364141d2262a3abfef2fb2e"), "a" : 1 }

So, we see an optimization failure when the query just has a projection, but the query succeeds when it has a filter and a projection.

This behavior is only visible when the hint is present, otherwise both queries succeed (they both choose a physical scan).



 Comments   
Comment by Matt Boros [ 20/Apr/23 ]

Oh yea sure, thanks for pointing that out.

Comment by Matt Boros [ 20/Apr/23 ]

Attaching the notes here regarding why we decided Bonsai should not produce a full index scan + fetch: https://docs.google.com/document/d/11PNR1Oss6PF7q8dWnM1TIdk_JSACdj15t04oSzvWNp4/edit?usp=sharing

Comment by Hana Pearlman [ 03/Nov/22 ]

Note that this is only possible when forcing bonsai, because we fall back to classic if there is a hint specified on the command. Also note on classic both queries succeed

Generated at Thu Feb 08 06:17:58 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.