[SERVER-36994] $expr does not use index for $in Created: 05/Sep/18  Updated: 05/Sep/18  Resolved: 05/Sep/18

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 3.6.7, 4.0.2
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Mathias C. Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-32549 allow $expr with $in expression use a... Backlog
Operating System: ALL
Steps To Reproduce:

Create a new collection.

Create index :

db.debug.createIndex({ 'item': 1 })

Insert data :

db.debug.insert({ item: "card", qty: 15 })
db.debug.insert({ item: "table", qty: 15 })
db.debug.insert({ item: "piano", qty: 15 })
db.debug.insert({ item: "house", qty: 15 })
db.debug.insert({ item: "test", qty: 15 })

Query :

db.debug.explain().aggregate([{
"$match": {"$expr": {"$in": ["$item", ["card", "test"]]}}
}])

Wining Plan

"winningPlan" : {
    "stage" : "COLLSCAN", 
    "filter" : {
        "$expr" : {
            "$in" : [
                "$item", 
                {
                    "$const" : [
                        "card", 
                        "test"
                    ]
                }
            ]
        }
    }, 
    "direction" : "forward"
},

 

Participants:

 Description   

An $expr expression in a query will not use an index when used with $in.

The goal was to used it in a $lookup pipeline.

The problem seems to exists in 3.6.3 as well as 4.0.2



 Comments   
Comment by James Wahlin [ 05/Sep/18 ]

Hi Mathias,

This issue is currently being tracked by SERVER-32549. Please follow that ticket for updates and feel free to vote and/or add comments.

Best,

James

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