Geek asks and answers

Why does the MongoDB "$ and" operator sometimes use a different plan without specifying inline criteria?

It seems to me that the following two queries should have exactly the same "explanatory" output:

Request 1:

{
    $and: [
        { $or: [
            { Foo: "123" },
            { Bar: "456" }
        ] },
        { Baz: { $in: ["abc", "def"] } }
    ]
}

Request 2:

{
    $or: [
        { Foo: "123" },
        { Bar: "456" }
    ],
    Baz: { $in: ["abc", "def"] } }
}

Please note that I have indexes on { Foo: -1, Baz: -1 }and { Bar: -1, Baz: -1 }, so this is optimized for the operator $or. And in fact, in the version for Query 2, in the output of the explanation, I see two clauses, both with the corresponding indexes, one for (Foo, Baz)and one for (Bar, Baz). MongoDB does exactly what it should have.

But in the first version (Query 1) is no more clauses. He gives me BasicCursorwithout the indicated pointers.

What is the difference between these two queries? Why does Mongo seem to be able to optimize # 2 but not # 1?

MongoVue, JSON, #, , # 1 # 2, , ...

+3
1

, , mongodb. ?

2.5.3.

( 2.4.6), $and.

2.6.

UPDATE:

, 2.6.3 , .

> db.test.find()
{ "_id" : 1, "Fields" : { "K1" : 123, "K2" : 456 } }
{ "_id" : 2, "Fields" : { "K1" : 456, "K2" : 123 } }
> db.test.getIndexes()
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "test.test"
    },
    {
        "v" : 1,
        "key" : {
            "Fields.K1" : 1
        },
        "name" : "Fields.K1_1",
        "ns" : "test.test"
    },
    {
        "v" : 1,
        "key" : {
            "Fields.K2" : 1
        },
        "name" : "Fields.K2_1",
        "ns" : "test.test"
    }
]

> db.test.find({"$and" : [{ "Fields.K1" : 123, "Fields.K2" : 456}]}).explain()
{
    "cursor" : "BtreeCursor Fields.K1_1",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 2,
    "nscannedAllPlans" : 4,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "Fields.K1" : [
            [
                123,
                123
            ]
        ]
    },
    "server" : "benihime:27017",
    "filterSet" : false
}
> db.test.find({ "Fields.K1" : 123, "Fields.K2" : 456}).explain()
{
    "cursor" : "BtreeCursor Fields.K1_1",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 2,
    "nscannedAllPlans" : 4,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "Fields.K1" : [
            [
                123,
                123
            ]
        ]
    },
    "server" : "benihime:27017",
    "filterSet" : false
}
+4

More articles:


All Articles