2

I've been trying to diagnose some long running queries and whilst profiling them I've noticed some very strange behaviour that I can't explain (pun intended); which I'm hoping someone here can...

Given an abridged example document of:

{
    author: "laura",
    keywords: ["foo", "bar", "hello"],
    created_date: ISODate("2017-01-18T00:00:00Z"),
    type: "content"
    ...
}

And indexes of:

{type: 1, author: 1, created_date: -1}
{type: 1, keywords: 1, created_date: -1}

If I run the following query:

db.items.explain().find({$or: [
    {
       type: "content", 
       author: "kate", 
       created_date: {
         $lte: ISODate("2017-01-25T00:00:00Z"), 
         $gte: ISODate("2017-01-18T00:00:00Z")
       }
    },{
       type: "article", 
       keyword: "bar", 
       created_date: {
         $lte: ISODate("2017-01-25T00:00:00Z"), 
         $gte: ISODate("2017-01-18T00:00:00Z")
       }
    }
]})

In the executionPlans, I notice that for the author component of the query, the entire query scans the index:

    {
            "stage" : "IXSCAN",
            ...
            "keyPattern" : {
                "type" : 1,
                "author" : 1,
                "created_date" : -1
            },
            ...
            "indexBounds" : {
                "type" : [
                    "[\"content\", \"content\"]"
                ],
                "author" : [
                    "[\"kate\", \"kate\"]"
                ],
                "created_date" : [
                    "[new Date(1485388799000), new Date(1484697600000)]"
                ]
            }
        }

Where as the keywords query splits the date out into a fetch stage as well:

{
        "stage" : "FETCH",
        "filter" : {
            "created_date" : {
                "$gte" : ISODate("2017-01-18T00:00:00Z")
            }
        },
        ...
        "inputStage" : {
            "stage" : "IXSCAN",
            ...
            "keyPattern" : {
                "type" : 1,
                "keywords" : 1,
                "created_date" : -1
            },
            ...
            "indexBounds" : {
                "type" : [
                    "[\"article\", \"article\"]"
                ],
                "keywords" : [
                    "[\"bar\", \"bar\"]"
                ],
                "created_date" : [
                    "[new Date(1485388799000), true)"
                ]
            }
        }
    }

I know that when indexing on a list/array field, a document is created for each element in that field so wonder if it's a product of that, but it's strikes me as bizarre that this is the behaviour.

Any thoughts would be gratefully recieved

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.