$expr with simple comparison of dates results in COLLSCAN

[sv...@investorise.com]

Hi!

I have a pretty simple query that uses $expr to compare between 2 date attributes of documents. It seems not to be using indexes and thus results in COLLSCAN and is very slow / resource hog. Does anyone have a good explanation for this behaviour?

MongoDB version is the newest 3.*

The query itself is:

db.getCollection("mycollection").find(
    {
        "$expr":{
            "$gt":["$updatedAt","$anotherTimestamp"]
        }
    }
)

Bother fields are indexed (separate indexes, updatedAt_1 and anotherTimestamp_1). I also tried with compound index updatedAt_1_anotherTimestamp_1 with no luck. 

Thanks,

Sven

[Krishnasagar Subhedarpage]

Hi,

Firstly $expr operator is used in aggregation pipeline. Secondly, it is used in $match stage of pipeline. Thirdly, documentation on $expr says, it does not support multikey indexes.

[Sven Varkel]

Hi,

Thanks for answer but unfortunately you're mistaken.

$expr can be used in queries since MongoDB 3.6.

https://docs.mongodb.com/manual/reference/operator/query/expr

I'm not querying over multikey indexes, but just normal single key indexes. The indexed values are of type datetime. Now you gave me an idea to check whether ALL indexed values are of the same type. I've seen strange behavior when indexes values are of mixed types.

Thanks,

Sven 

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to a topic in the Google Groups "mongodb-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mongodb-user/o4fe5htnOs0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/167e1cd3-71ab-4823-935f-5cabef00d9e6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Chief Engineer, Investorise

Mobile phone: +372 56697201

[sv...@investorise.com]

Hi!

After playing around it I came to conclusion that COLLSCAN is used whenever either attribute is missing/empty in the data. Additional filters should be used to avoid that and after that I can see from explain() results that winning plan is FETCH.

Example for making sure that only existing fields are used in $expr comparison:

db.getCollection("mycollection").find(
    {
        "updatedAt":{$exists:true},
        "anotherTimestamp":{$exists:true},

        "$expr":{
            "$gt":["$updatedAt","$anotherTimestamp"]
        }
    }
)