Custom query with data

MongoDB Atlas
1

Hi, we want to use Atlas Online Archive but we want to use custom query because removing just by time is not enough for us. We need to remove all items which have some status and are older then for example 60 days. And because the query must be valid JSON we cannot use any functions as ISODate, Date etc. So we created query like it is recommended in your documentation with expr and substract operators.

{
    "$expr": {
        "$lte": [
            "$createdDate",
            {
                "$subtract": [
                    "$$NOW",
                    53646818400
                ]
            }
        ]
    },
    "status": "DELETED"
}

But now this query is not able to use an index on the date property createdDate and we receive alerts Insufficient Indexes Alert When I try the query with simple ISODate without expr and substract then index is normally used. My index is:

{
    "status" : 1,
    "createdDate" : 1,
}

So how to create this query that uses index on a date property?

3

Hi @Tomas_Poledny,

Welcome to the community :wave:

But now this query is not able to use an index on the date property createdDate and we receive alerts Insufficient Indexes Alert When I try the query with simple ISODate without expr and substract then index is normally used

Would you be able to provide the db.collection.find.explain(“executionStats”) output for the custom filter you are using? The command should be similar to the below example:

db.collection.find({
    "$expr": {
        "$lte": [
            "$createdDate",
            {
                "$subtract": [
                    "$$NOW",
                    53646818400
                ]
            }
        ]
    },
    "status": "DELETED"
}).explain("executionStats")

Regards,
Jason

2 Likes
4

Hi, here is the explain with expr:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "xxxx",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "status" : {
                        "$eq" : "DELETED"
                    }
                }, 
                {
                    "$expr" : {
                        "$lte" : [ 
                            "$createdDate", 
                            {
                                "$subtract" : [ 
                                    "$$NOW", 
                                    {
                                        "$const" : 53646818400.0
                                    }
                                ]
                            }
                        ]
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "$expr" : {
                    "$lte" : [ 
                        "$createdDate", 
                        {
                            "$subtract" : [ 
                                "$$NOW", 
                                {
                                    "$const" : 53646818400.0
                                }
                            ]
                        }
                    ]
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "status" : 1
                },
                "indexName" : "status",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "status" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "status" : [ 
                        "[\"DELETED\", \"DELETED\"]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "FETCH",
                "filter" : {
                    "$expr" : {
                        "$lte" : [ 
                            "$createdDate", 
                            {
                                "$subtract" : [ 
                                    "$$NOW", 
                                    {
                                        "$const" : 53646818400.0
                                    }
                                ]
                            }
                        ]
                    }
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "status" : 1,
                        "createdDate" : 1
                    },
                    "indexName" : "status_createdDate",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "status" : [],
                        "createdDate" : []
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "status" : [ 
                            "[\"DELETED\", \"DELETED\"]"
                        ],
                        "createdDate" : [ 
                            "[MinKey, MaxKey]"
                        ]
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 0,
        "executionTimeMillis" : 801,
        "totalKeysExamined" : 15554,
        "totalDocsExamined" : 15554,
        "executionStages" : {
            "stage" : "FETCH",
            "filter" : {
                "$expr" : {
                    "$lte" : [ 
                        "$createdDate", 
                        {
                            "$subtract" : [ 
                                "$$NOW", 
                                {
                                    "$const" : 53646818400.0
                                }
                            ]
                        }
                    ]
                }
            },
            "nReturned" : 0,
            "executionTimeMillisEstimate" : 203,
            "works" : 15556,
            "advanced" : 0,
            "needTime" : 15554,
            "needYield" : 0,
            "saveState" : 243,
            "restoreState" : 243,
            "isEOF" : 1,
            "docsExamined" : 15554,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 15554,
                "executionTimeMillisEstimate" : 0,
                "works" : 15555,
                "advanced" : 15554,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 243,
                "restoreState" : 243,
                "isEOF" : 1,
                "keyPattern" : {
                    "status" : 1
                },
                "indexName" : "status",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "status" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "status" : [ 
                        "[\"DELETED\", \"DELETED\"]"
                    ]
                },
                "keysExamined" : 15554,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "xxxxx",
        "port" : 27017,
        "version" : "4.2.18",
        "gitVersion" : "f65ce5e25c0b26a00d091a4d24eec1a8b3a4c016"
    },
    "ok" : 1.0,
    "$clusterTime" : {
        "clusterTime" : Timestamp(1642502477, 1),
        "signature" : {
            "hash" : { "$binary" : "xxxxx", "$type" : "00" },
            "keyId" : NumberLong(xxxxx)
        }
    },
    "operationTime" : Timestamp(1642502477, 1)
}

And here is with ISODate which uses index:

{
    "createdDate": {"$lte": ISODate("2020-03-24T11:19:07.923Z")},
    "status": "DELETED"
}

explain:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "xxxxxx",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "status" : {
                        "$eq" : "DELETED"
                    }
                }, 
                {
                    "createdDate" : {
                        "$lte" : ISODate("2020-03-24T11:19:07.923Z")
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "status" : 1,
                    "createdDate" : 1
                },
                "indexName" : "status_createdDate",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "status" : [],
                    "createdDate" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "status" : [ 
                        "[\"DELETED\", \"DELETED\"]"
                    ],
                    "createdDate" : [ 
                        "(true, new Date(1585048747923)]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "FETCH",
                "filter" : {
                    "createdDate" : {
                        "$lte" : ISODate("2020-03-24T11:19:07.923Z")
                    }
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "status" : 1
                    },
                    "indexName" : "status",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "status" : []
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "status" : [ 
                            "[\"DELETED\", \"DELETED\"]"
                        ]
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 0,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 0,
        "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : 0,
            "executionTimeMillisEstimate" : 0,
            "works" : 2,
            "advanced" : 0,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "docsExamined" : 0,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 0,
                "executionTimeMillisEstimate" : 0,
                "works" : 1,
                "advanced" : 0,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "keyPattern" : {
                    "status" : 1,
                    "createdDate" : 1
                },
                "indexName" : "status_createdDate",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "status" : [],
                    "createdDate" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "status" : [ 
                        "[\"DELETED\", \"DELETED\"]"
                    ],
                    "createdDate" : [ 
                        "(true, new Date(1585048747923)]"
                    ]
                },
                "keysExamined" : 0,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "xxxxxxx",
        "port" : 27017,
        "version" : "4.2.18",
        "gitVersion" : "f65ce5e25c0b26a00d091a4d24eec1a8b3a4c016"
    },
    "ok" : 1.0,
    "$clusterTime" : {
        "clusterTime" : Timestamp(1642502937, 1),
        "signature" : {
            "hash" : { "$binary" : "xxxxxx", "$type" : "00" },
            "keyId" : NumberLong(xxxxxx)
        }
    },
    "operationTime" : Timestamp(1642502937, 1)
}
5

Thank you for providing the outputs of both queries @Tomas_Poledny

From the output you provided, it seems that besides the desired {"status":1, "createdDate":1} index, you also have a {"status":1} index in the collection. Since {"status":1} is the prefix of {"status":1, "createdDate":1} (see prefix index), the query planner can use either index to execute the query you have. Specifically in the case you’re seeing, it happens to choose the {"status":1} instead.

Since the {"status":1} index is redundant, you might want to drop that index and keep the {"status":1, "createdDate":1} index instead. This way, there is no ambiguity for the query planner about which index to choose for the query that you’re working on. See Create Compound Indexes to Support Several Different Queries for an in depth explanation why this is so.

Regards,
Jason

6

I am not sure but it does not use createdDate too. I would say there is no difference between using status and status_createdDate index because both use only index on status (this is the reason why does not care which one is used). So when I delete status index (which is of course redundant) it uses the right index but not createdDate is not used too ( "[MinKey, MaxKey]").

 "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "$expr" : {
                    "$lte" : [ 
                        "$createdDate", 
                        {
                            "$subtract" : [ 
                                "$$NOW", 
                                {
                                    "$const" : 53646818400.0
                                }
                            ]
                        }
                    ]
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "status" : 1,
                    "createdDate" : 1
                },
                "indexName" : "status_createdDate",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "status" : [],
                    "createdDate" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "status" : [ 
                        "[\"DELETED\", \"DELETED\"]"
                    ],
                    "createdDate" : [ 
                        "[MinKey, MaxKey]"
                    ]
                }
            }
        }

But when I use ISODate without $expr then createdDate is used:

"createdDate" : [ 
  "(true, new Date(1585048747923)"
]                   ]

Am I right?

7

Hi @Tomas_Poledny,

Based off the explain output, after the redundant single field index {"status":1} was dropped, the query is now using the {"status":1,"createdDate":1} index. However, you may be correct in saying there is a minimal difference due to the index bound ranges for the "createdDate" field.

From your initial explain output it states you are using MongoDB version of 4.2.18.

I performed some testing and it appears there are improvements regarding the $expr operator which you can find here for MongoDB version 5.0.

In my testing, I used the same query you provided with the same compound index for both version 4.2.18 and version 5.0.5. Please see the indexBounds details of the testing below.

Version 4.2.18:

[primary] newdb> db.collection.find({ "$expr": { "$lte": [ "$createdDate", { "$subtract": [ "$$NOW", 53646818400] }] }, "status": "DELETED" }).explain("executionStats")
...
        indexBounds: {
          status: [ '["DELETED", "DELETED"]' ],
          createdDate: [ '[MinKey, MaxKey]' ]
        }

Version 5.0.5:

[primary] newdb> db.collection.find({ "$expr": { "$lte": [ "$createdDate", { "$subtract": [ "$$NOW", 53646818400] }] }, "status": "DELETED" }).explain("executionStats")
...
        indexBounds: {
          status: [ '["DELETED", "DELETED"]' ],
          createdDate: [ '[MinKey, new Date(1588983435171)]' ]
        }

I would recommend testing on a separate version 5.0.5 cluster to see if there are any improvements against the custom filter query with the index {"status":1,"createdDate":1}. If you believe there are still issues with the index bound ranges in version 5.0, please add the details of the explain(“executionStats”) here.

Having said that, I would encourage you to upgrade to the latest MongoDB version if it’s feasible for you. This is because there have been much improvements in the 5.0 series. Note that upgrading from the 4.2 series would involve upgrading through the major versions, so 4.2 → 4.4 → 5.0. See Upgrade recommendations and checklist for more details.

Hope this helped.

Regards,
Jason

1 Like
8

Thank you so much. Unfortunately we cannot upgrade to 5.0 because we use Spring Data MongoDB which supports only 4.4. But I believe that the upgrading to 5.0 will help us.

1 Like
9

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.