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

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Querying
    • Labels:
      None
    • Operating System:
      ALL
    • Steps To Reproduce:
      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

      Description

      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();
      

        Attachments

        1. bug.js
          0.5 kB

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                12 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: