Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-31064

query isn't index only unless projection references all indexed columns

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Querying
    • Labels:
      None
    • ALL
    • Hide

      Run the script above. Output is:

      WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 2,
        "numIndexesAfter" : 2,
        "note" : "all indexes already exist",
        "ok" : 1
}
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.foo",
                "indexFilterSet" : false,
                "parsedQuery" : {

                },
                "winningPlan" : {
                        "stage" : "PROJECTION",
                        "transformBy" : {
                                "_id" : 0,
                                "price" : 1,
                                "customerid" : 1
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "price" : 1,
                                        "customerid" : 1
                                },
                                "indexName" : "price_1_customerid_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "price" : [ ],
                                        "customerid" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "price" : [
                                                "[MinKey, MaxKey]"
                                        ],
                                        "customerid" : [
                                                "[MinKey, MaxKey]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "nuc1",
                "port" : 27017,
                "version" : "3.4.6-1.7",
                "gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2"
        },
        "ok" : 1
}
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.foo",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "customerid" : {
                                "$lt" : 0
                        }
                },
                "winningPlan" : {
                        "stage" : "PROJECTION",
                        "transformBy" : {
                                "_id" : 0,
                                "price" : 1,
                                "customerid" : 1
                        },
                        "inputStage" : {
                                "stage" : "FETCH",
                                "filter" : {
                                        "customerid" : {
                                                "$lt" : 0
                                        }
                                },
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "price" : 1,
                                                "customerid" : 1
                                        },
                                        "indexName" : "price_1_customerid_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                                "price" : [ ],
                                                "customerid" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "price" : [
                                                        "[MinKey, MaxKey]"
                                                ],
                                                "customerid" : [
                                                        "[MinKey, MaxKey]"
                                                ]
                                        }
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "nuc1",
                "port" : 27017,
                "version" : "3.4.6-1.7",
                "gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2"
        },
        "ok" : 1
}
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.foo",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "customerid" : {
                                                "$lt" : 0
                                        }
                                },
                                {
                                        "price" : {
                                                "$gte" : 0
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "PROJECTION",
                        "transformBy" : {
                                "_id" : 0,
                                "price" : 1,
                                "customerid" : 1
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "price" : 1,
                                        "customerid" : 1
                                },
                                "indexName" : "price_1_customerid_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "price" : [ ],
                                        "customerid" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "price" : [
                                                "[0.0, inf.0]"
                                        ],
                                        "customerid" : [
                                                "[-inf.0, 0.0)"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "nuc1",
                "port" : 27017,
                "version" : "3.4.6-1.7",
                "gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2"
        },
        "ok" : 1
}
bye
      Show
      Run the script above. Output is: WriteResult({ "nInserted" : 1 }) WriteResult({ "nInserted" : 1 }) WriteResult({ "nInserted" : 1 }) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 2, "note" : "all indexes already exist", "ok" : 1 } { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.foo", "indexFilterSet" : false, "parsedQuery" : { }, "winningPlan" : { "stage" : "PROJECTION", "transformBy" : { "_id" : 0, "price" : 1, "customerid" : 1 }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "price" : 1, "customerid" : 1 }, "indexName" : "price_1_customerid_1", "isMultiKey" : false, "multiKeyPaths" : { "price" : [ ], "customerid" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "price" : [ "[MinKey, MaxKey]" ], "customerid" : [ "[MinKey, MaxKey]" ] } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "nuc1", "port" : 27017, "version" : "3.4.6-1.7", "gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2" }, "ok" : 1 } { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.foo", "indexFilterSet" : false, "parsedQuery" : { "customerid" : { "$lt" : 0 } }, "winningPlan" : { "stage" : "PROJECTION", "transformBy" : { "_id" : 0, "price" : 1, "customerid" : 1 }, "inputStage" : { "stage" : "FETCH", "filter" : { "customerid" : { "$lt" : 0 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "price" : 1, "customerid" : 1 }, "indexName" : "price_1_customerid_1", "isMultiKey" : false, "multiKeyPaths" : { "price" : [ ], "customerid" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "price" : [ "[MinKey, MaxKey]" ], "customerid" : [ "[MinKey, MaxKey]" ] } } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "nuc1", "port" : 27017, "version" : "3.4.6-1.7", "gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2" }, "ok" : 1 } { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.foo", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "customerid" : { "$lt" : 0 } }, { "price" : { "$gte" : 0 } } ] }, "winningPlan" : { "stage" : "PROJECTION", "transformBy" : { "_id" : 0, "price" : 1, "customerid" : 1 }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "price" : 1, "customerid" : 1 }, "indexName" : "price_1_customerid_1", "isMultiKey" : false, "multiKeyPaths" : { "price" : [ ], "customerid" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "price" : [ "[0.0, inf.0]" ], "customerid" : [ "[-inf.0, 0.0)" ] } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "nuc1", "port" : 27017, "version" : "3.4.6-1.7", "gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2" }, "ok" : 1 } bye

      In my test case below I expect the 3 queries at the end to be index only, but the second one isn't. The third query is like the second but it also references the first column in the index (price) and that query is index only.

      db.foo.insert({ price:0, customerid:0});
db.foo.insert({ price:1, customerid:1});
db.foo.insert({ price:2, customerid:2});

db.foo.createIndex({ price:1, customerid:1});

db.foo.find({}, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
db.foo.find({ customerid : { $lt : 0 } }, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
db.foo.find({ price : { $gte : 0 }, customerid : { $lt : 0 } }, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();

mdcallag@nuc1:~/b/pmon346$
mdcallag@nuc1:~/b/pmon346$
mdcallag@nuc1:~/b/pmon346$ cat bug.js

db.foo.insert({ price:0, customerid:0});
db.foo.insert({ price:1, customerid:1});
db.foo.insert({ price:2, customerid:2});

db.foo.createIndex({ price:1, customerid:1});

db.foo.find({}, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
db.foo.find({ customerid : { $lt : 0 } }, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
db.foo.find({ price : { $gte : 0 }, customerid : { $lt : 0 } }, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();

        1. bug.js
          0.5 kB

            Assignee:
            kelsey.schubert@mongodb.com Kelsey Schubert
            Reporter:
            mdcallag Mark Callaghan
            Votes:
            0 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved: