I am using MongoDB version 4.0 and performing the following aggregate query.
db.collection.aggregate([
"$match": {
"$expr": {
"$gte": [
"$created_at",
{
"$subtract": [
{
"$dateFromParts": {
"day": {
"$dayOfMonth": "$$NOW"
},
"hour": 0,
"millisecond": 0,
"minute": 0,
"month": {
"$month": "$$NOW"
},
"second": 0,
"timezone": "+0530",
"year": {
"$year": "$$NOW"
}
}
},
{
"$multiply": [
1,
86400000
]
}
]
}
]
}
}
])
I have the below index in place, but its not getting used by the MongoDB query.
{
"v" : 2,
"key" : {
"candidate_id" : 1,
"created_at" : -1
},
"name" : "candCreatedAtIndex",
"ns" : "proddb.applications",
"background" : true
},
{
"v" : 2,
"key" : {
"establishment_id" : 1,
"created_at" : -1
},
"name" : "estCreatedAtIndex",
"ns" : "proddb.applications",
"background" : true
},
{
"v" : 2,
"key" : {
"created_at" : -1
},
"name" : "createdAtIndex",
"background" : true,
"ns" : "proddb.applications"
},
So, I made use of the $hint method to force MongoDB in using that aggregation query.
var dt = new Date();
db.applications.aggregate([
{
"$match": {
"$expr": {
"$gte": [
"$created_at",
{
"$subtract": [
{
"$dateFromParts": {
"day": {
"$dayOfMonth": dt
},
"hour": 0,
"millisecond": 0,
"minute": 0,
"month": {
"$month": dt
},
"second": 0,
"timezone": "+0530",
"year": {
"$year": dt
}
}
},
{
"$multiply": [
1,
86400000
]
}
]
}
]
}
},
}
], {
'hint': 'createdAtIndex'
}).explain()
When I use $hint in MQL, its utilizing the index for the $match query, but its still performing COLLSCAN in PyMongo.
Below is the PyMongo code I used.
dt = datetime.utcnow()
cursor = applications_col.aggregate([
{
"$match": {
"$expr": {
"$gte": [
"$created_at",
{
"$subtract": [
{
"$dateFromParts": {
"day": {
"$dayOfMonth": dt
},
"hour": 0,
"millisecond": 0,
"minute": 0,
"month": {
"$month": dt
},
"second": 0,
"timezone": "+0530",
"year": {
"$year": dt
}
}
},
{
"$multiply": [
1,
86400000
]
}
]
}
]
}
},
}
], hint='createdAtIndex')