MongoDB refuses to use index intersection

Question

I use MongoDB 2.6.4.

My indexes looks like this:

    {
            "v" : 1,
            "key" : {
                    "isFolder" : 1
            },
            "name" : "isFolder_1",
            "ns" : "Tenant_51.files",
            "background" : true
    },
    {
            "v" : 1,
            "key" : {
                    "isForeign" : 1
            },
            "name" : "isForeign_1",
            "ns" : "Tenant_51.files",
            "background" : true
    },

My query looks like this:

db.files.find({ isFolder: true, isForeign: false }).explain(true)

For some reason, it chooses to use only 1 index (VERY SLOW: 680 seconds!!)

It looks like it does calculate the Complex Plan, however, decides not to use it, and I don't understand why. Here is the execution plan:

{
        "cursor" : "BtreeCursor isFolder_1",
        "isMultiKey" : false,
        "n" : 107441,
        "nscannedObjects" : 110580,
        "nscanned" : 110580,
        "nscannedObjectsAllPlans" : 110689,
        "nscannedAllPlans" : 110801,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 21056,
        "nChunkSkips" : 0,
        "millis" : 679121,
        "indexBounds" : {
                "isFolder" : [
                        [
                                true,
                                true
                        ]
                ]
        },
        "allPlans" : [
                {
                        "cursor" : "BtreeCursor isFolder_1",
                        "isMultiKey" : false,
                        "n" : 107441,
                        "nscannedObjects" : 110580,
                        "nscanned" : 110580,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                                "isFolder" : [
                                        [
                                                true,
                                                true
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "BtreeCursor isForeign_1",
                        "isMultiKey" : false,
                        "n" : 68,
                        "nscannedObjects" : 109,
                        "nscanned" : 110,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                                "isForeign" : [
                                        [
                                                false,
                                                false
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "Complex Plan",
                        "n" : 42,
                        "nscannedObjects" : 0,
                        "nscanned" : 111,
                        "nChunkSkips" : 0
                }
        ],
        "server" : "XXX",
        "filterSet" : false,
        "stats" : {
                "type" : "KEEP_MUTATIONS",
                "works" : 128743,
                "yields" : 21056,
                "unyields" : 21056,
                "invalidates" : 13834,
                "advanced" : 107441,
                "needTime" : 3140,
                "needFetch" : 18161,
                "isEOF" : 1,
                "children" : [
                        {
                                "type" : "FETCH",
                                "works" : 128743,
                                "yields" : 21056,
                                "unyields" : 21056,
                                "invalidates" : 13834,
                                "advanced" : 107441,
                                "needTime" : 3140,
                                "needFetch" : 18161,
                                "isEOF" : 1,
                                "alreadyHasObj" : 0,
                                "forcedFetches" : 0,
                                "matchTested" : 107441,
                                "children" : [
                                        {
                                                "type" : "IXSCAN",
                                                "works" : 110581,
                                                "yields" : 21056,
                                                "unyields" : 21056,
                                                "invalidates" : 13834,
                                                "advanced" : 110580,
                                                "needTime" : 1,
                                                "needFetch" : 0,
                                                "isEOF" : 1,
                                                "keyPattern" : "{ isFolder: 1 }",
                                                "isMultiKey" : 0,
                                                "boundsVerbose" : "field #0['isFolder']: [true, true]",
                                                "yieldMovedCursor" : 0,
                                                "dupsTested" : 0,
                                                "dupsDropped" : 0,
                                                "seenInvalidated" : 0,
                                                "matchTested" : 0,
                                                "keysExamined" : 110580,
                                                "children" : [ ]
                                        }
                                ]
                        }
                ]
        }
}

Did you ever find a solution to this? I'm looking at the same issue here (with MongoDB 3.2 and 3.4). I cannot figure out why it's not doing intersection, and worse still, whatever plan it falls back on has the same performance as a COLLSCAN. Actually deleting one of the indexes improves performance dramatically (but is obviously not a solution). — DanielSmedegaardBuus, Sep 26 '17 at 10:36
Unfortunately, the answer is that MongoDB's index intersection is very (very) poor and is being used in only very specific occasions. It's more marketing than engineering. Do not expect it to work in any real-world use case. Just use a single index. Sorry. — Roy Reznik, Oct 01 '17 at 20:01
Thanks, Roy :) I ended up with the same conclusion. My exact problem would never use intersection either way, as it included an `$exists` clause which makes it a range query, apparently. But even after getting rid of that clause, it still didn't use intersection. I ended up with a lot of single indices, and a couple of handfuls of compound ones, and overall it's still a massive improvement. Just have to forget all about index intersection ;) — DanielSmedegaardBuus, Oct 05 '17 at 08:38

score -4 · Answer 1 · answered Aug 13 '14 at 09:04

-4

From the MongoDB docs about indexing:

MongoDB can only use one index to support any given operation.

The solution, however, is as easy as the explanation: Use a compound index.

db.files.ensureIndex({isFolder:1,isForeign:1})

answered Aug 13 '14 at 09:04

Markus W Mahlberg

19,711
6
65
89

This is just a mistake in documentation. They implemented index intersection in version 2.6. You can look here: http://docs.mongodb.org/manual/core/index-intersection/ – Roy Reznik Aug 13 '14 at 11:09
Well, a compound index works and has no drawbacks. Your turn. – Markus W Mahlberg Aug 13 '14 at 18:09
3

It has drawbacks, it supports less query options. I can't create a compound index for every permutation of queries that I would like to do. This is the reason they added index intersection in the first place. – Roy Reznik Aug 14 '14 at 07:06
Which wasn't layed out in the question. I assume that the plan used was faster in returning the documents as a whole than the complex plan. You might want to check again using an according projection. – Markus W Mahlberg Aug 14 '14 at 10:25
Pretty simple, using the isForeign index won't be faster than a FETCH stage. What bothers me here is that 10 minutes for querying 100k documents is a huge smell, try better modeling your documents. – bateloche Jul 05 '16 at 18:51

MongoDB refuses to use index intersection

1 Answers1