Match regex is not utilizing index correctly

Working with Data
1

I have two indexes {timestamp:1} and {code:1,timestamp:1}

timestamp is date, while code is string.

code contains a string like the ones below:

1_ABC_1
1_ABC_2
1_PQR_1
1_XYZ_1

Following is my match stage:

{
    "code": {
        "$regex": "WMS"
    },
    "timestamp": {
        "$gte": {
            "$date": 1596220200000
        },
        "$lte": {
            "$date": 1597429799999
        }
    }
}

Yet, the winning plan ends up being the following:

{
    "stage" : "FETCH",
    "filter" : {
        "device" : {
                "$regex" : "ABC"
        }
    },
    "inputStage" : {
        "stage" : "IXSCAN",
        "keyPattern" : {
            "timestamp" : 1
        },
        "indexName" : "timestamp_1",
        "isMultiKey" : false,
        "multiKeyPaths" : {
            "timestamp" : [ ]
        },
        "isUnique" : false,
        "isSparse" : false,
        "isPartial" : false,
        "indexVersion" : 2,
        "direction" : "forward",
        "indexBounds" : {
            "timestamp" : [
                    "[{ $date: 1596220200000.0 }, { $date: 1597429799999.0 }]"
            ]
        }
    }
}
2

Hi @Dushyant_Bangal,

The $regex will not utilize an index when the search is not anchored.

Can you search ^1_WMS instead?

If not consider using, text index ot Atlas and Atlas search to better index regular expressions.

Best regards
Pavel

1 Like
3

Hi @Pavel_Duchovny,

I tried that, but it didnt work.
I also tried with a small set of data on my local machine, still the same. When I dropped the timestamp single index, then it took the correct Index.

But I need the timestamp Index.

4

Hi @Dushyant_Bangal,

You can recreate the timestamp index. Its better to clear query cache or use a hint on the query to force q better index and avoid remove necessary index just for a plan recompute.

Best
Pavel