[SERVER-14464] Index not being used when there is $exists inside a or clause. Created: 04/Jul/14  Updated: 10/Dec/14  Resolved: 07/Jul/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Dharshan Rangegowda Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Participants:

 Description   

The server version is 2.4.8. The following query is not using indexes.

S-haystackdbec2-0:PRIMARY> db.streams.find({$or:[ {statusNeedsRefresh:true},{site:'youtube', status:'ondemand', publishedDate:{$exists:false}}] }).explain();
 
       "cursor" : "BasicCursor",
       "isMultiKey" : false,
       "n" : 6,
       "nscannedObjects" : 1992659,
       "nscanned" : 1992659,
       "nscannedObjectsAllPlans" : 1992659,
       "nscannedAllPlans" : 1992659,
       "scanAndOrder" : false,
       "indexOnly" : false,
       "nYields" : 178,
       "nChunkSkips" : 0,
       "millis" : 10890,
       "indexBounds" : {
 
       },
       "server" : "ip-172-31-42-250:27017"
 

This query seems to work

RS-haystackdbec2-0:PRIMARY> db.streams.find({$or:[{statusNeedsRefresh:true}, {publishedDate:null, site:'youtube',status:'ondemand'}]}).explain();
{
        "clauses" : [
                {
                        "cursor" : "BtreeCursor statusNeedsRefresh_1",
                        "isMultiKey" : false,
                        "n" : 6,
                        "nscannedObjects" : 6,
                        "nscanned" : 6,
                        "nscannedObjectsAllPlans" : 6,
                        "nscannedAllPlans" : 6,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 1,
                        "indexBounds" : {
                                "statusNeedsRefresh" : [
                                        [
                                                true,
                                                true
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "BtreeCursor status_1_lang_1_publishedDate_1",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 3,
                        "nscanned" : 75,
                        "nscannedObjectsAllPlans" : 255,
                        "nscannedAllPlans" : 327,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 2,
                        "indexBounds" : {
                                "status" : [
                                        [
                                                "ondemand",
                                                "ondemand"
                                        ]
                                ],
                                "lang" : [
                                        [
                                                {
                                                        "$minElement" : 1
                                                },
                                                {
                                                        "$maxElement" : 1
                                                }
                                        ]
                                ],
                                "publishedDate" : [
                                        [
                                                null,
                                                null
                                        ]
                                ]
                        }
                }
        ],
        "n" : 6,
        "nscannedObjects" : 9,
        "nscanned" : 81,
        "nscannedObjectsAllPlans" : 261,
        "nscannedAllPlans" : 333,
        "millis" : 3,
        "server" : "ip-172-31-42-250:27017"
}
 

Here are all the indexes

RS-haystackdbec2-0:PRIMARY> db.streams.getIndexes();
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "_id_"
        },
        {
                "v" : 1,
                "key" : {
                        "sourceUrl" : 1
                },
                "unique" : true,
                "ns" : "haystackdb.streams",
                "name" : "sourceUrl_1",
                "background" : true,
                "safe" : null
        },
        {
                "v" : 1,
                "key" : {
                        "tsLastUpdated" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "tsLastUpdated_1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "tweetsPerHour" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "tweetsPerHour_1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "publishedDate" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "publishedDate_1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "twitterTags" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "twitterTags_1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "popularityScore" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "popularityScore_1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "_id" : 1,
                        "sourceUrl" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "_id_1_sourceUrl_1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "site" : 1,
                        "author" : 1,
                        "category" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "site_1_author_1_category_1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "tags" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "tags_1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "lang" : 1,
                        "tweetsPerHour" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "lang_1_tweetsPerHour_1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "trackedChannel" : 1,
                        "site" : 1,
                        "publishedDate" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "trackedChannel_1_site_1_publishedDate_1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "site" : 1,
                        "status" : 1,
                        "tsLastBookkeeping" : 1,
                        "trackedChannel" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "site_1_status_1_tsLastBookkeeping_1_trackedChannel_1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "title" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "title_1"
        },
        {
                "v" : 1,
                "key" : {
                        "titleReverse" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "titleReverse_1",
                "sparse" : true,
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "highestPopularityScore" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "highestPopularityScore_1",
                "sparse" : true,
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "status" : 1,
                        "lang" : 1,
                        "publishedDate" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "status_1_lang_1_publishedDate_1"
        },
        {
                "v" : 1,
                "key" : {
                        "haystackTags" : 1,
                        "publishedDate" : -1
                },
                "ns" : "haystackdb.streams",
                "name" : "haystackTags_1_publishedDate_-1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "category" : 1,
                        "publishedDate" : -1,
                        "popularityScore" : -1
                },
                "ns" : "haystackdb.streams",
                "name" : "category_1_publishedDate_-1_popularityScore_-1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "author" : 1,
                        "publishedDate" : 1,
                        "popularityScore" : -1
                },
                "ns" : "haystackdb.streams",
                "name" : "author_1_publishedDate_1_popularityScore_-1",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "statusNeedsRefresh" : 1
                },
                "ns" : "haystackdb.streams",
                "name" : "statusNeedsRefresh_1",
                "sparse" : true,
                "background" : true
        }
]
RS-haystackdbec2-0:PRIMARY>
 



 Comments   
Comment by J Rassi [ 07/Jul/14 ]

I verified that the query you posted does get correctly indexed in version 2.6.3 of the server (SERVER-10026 allowed for {$exists: false} queries to be indexed by non-sparse indexes). Please upgrade to 2.6 and re-open this ticket if you continue to encounter this issue.

Generated at Thu Feb 08 03:34:57 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.