[SERVER-41252] cannot get covered query with $expr with $eq only Created: 21/May/19  Updated: 06/Feb/24

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

Type: Improvement Priority: Major - P3
Reporter: Miguel Angel Nieto Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: query-director-triage, query-product-scope-1, query-product-urgency-2, query-product-value-2
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-83529 Generate covered projections with $expr Closed
Related
Assigned Teams:
Query Optimization
Sprint: QO 2024-02-05, QO 2024-02-19
Participants:
Case:

 Description   

Hello,

When using $expr, a query that should be covered by the index actually examine documents.

Not only that, it searches for the values twice, once during the index scan and a second time during the fetch stage.



 Comments   
Comment by Ivan Fefer [ 23/Nov/23 ]

This is very relevant to $lookup with pipelines, as they are forced to use $expr to reference let variables.

Comment by Ivan Fefer [ 23/Nov/23 ]

if we are using $expr, we don’t produce covered projections. 
Simple way to reproduce:

If index on a is present,

This will have fetch + projection simple
db.test.explain().aggregate([{$match: {$expr: {$eq: ["$a", 1]}}}, {$project: {_id: 0, a: 1}}])

But this will have projection covered:
db.test.explain().aggregate(\\{$match: {a: 1}}, \\{$project: {_id: 0, a: 1}})

Comment by Craig Homa [ 03/Jun/19 ]

Scheduling to the backlog as this improvement will be somewhat time consuming for the team to implement until we invest in the internal representation of the expression tree.

We believe this optimization would be incorrect if the index is multikey.

Comment by Asya Kamsky [ 21/May/19 ]

The issue is that after rewriting $expr $eq portion, we leave that predicate in the tree and therefore need a FETCH to apply it to test. This ticket is meant to track improving this (if possible).

Generated at Thu Feb 08 04:57:13 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.