Lab 3.1

In this lab you're going to determine which index was used to satisfy a query given its explain output.
The following query was ran:
> var exp = db.restaurants.explain("executionStats")
> exp.find({ "address.state": "NY", stars: { $gt: 3, $lt: 4 } }).sort({ name: 1 }).hint(REDACTED)
Which resulted in the following output:
{
  "queryPlanner": {
    "plannerVersion": 1,
    "namespace": "m201.restaurants",
    "indexFilterSet": false,
    "parsedQuery": "REDACTED",
    "winningPlan": {
      "stage": "SORT",
      "sortPattern": {
        "name": 1
      },
      "inputStage": {
        "stage": "SORT_KEY_GENERATOR",
        "inputStage": {
          "stage": "FETCH",
          "inputStage": {
            "stage": "IXSCAN",
            "keyPattern": "REDACTED",
            "indexName": "REDACTED",
            "isMultiKey": false,
            "isUnique": false,
            "isSparse": false,
            "isPartial": false,
            "indexVersion": 1,
            "direction": "forward",
            "indexBounds": "REDACTED"
          }
        }
      }
    },
    "rejectedPlans": [ ]
  },
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 3335,
    "executionTimeMillis": 20,
    "totalKeysExamined": 3335,
    "totalDocsExamined": 3335,
    "executionStages": "REDACTED"
  },
  "serverInfo": "REDACTED",
  "ok": 1
}
Given the redacted explain output above, select the index that was passed to hint.

 Note: The hint() method is used to force the query planner to select a particular index for a given query. You can learn more about hint by visiting its documentation.

Choose the best answer.

Answer:
  • { "address.state": 1, "name": 1, "stars": 1 } - No - if this index was used, then there would be no SORT stage.
  • { "address.state": 1, "stars": 1, "name": 1 } - Yes - It wouldn't need to examine any extra index keys, so since nReturned and totalKeysExamined are both 3,335 we know this index was used.
  • { "address.state": 1, "name": 1 } - No - if this index was used, then there would be no SORT stage.
  • { "address.state": 1 } - No - if this index was used, then we would expect that we'd have to examine some unnecessary documents and index keys. Since there are 50 states in the US, and we have 1,000,000 documents we'd expect to examine about 20,000 documents, not the 3,335 we see in the output.


Comments

  1. Unknown24 May 2018 at 18:43

    Could you please explain be so kind to explain a few details to me? I would really appreciate it!

    1.- Why "... { "address.state": 1, "stars": 1, "name": 1 } - Yes ..." is not using the index to sort?

    2.- Why "... { "address.state": 1, "name": 1, "stars": 1 } - No ..." there wouldn't be a SORT stage when using this index?

    Thank you!

    ReplyDelete

Post a Comment

Popular posts from this blog

M201 MongoDB Performance - Labs & Final Exam Q&A

M201 MongoDB Performance Chapter-1 Introduction to MongoDB Performance Lab 1.1: Install Course Dependencies Lab 1.2: Install MongoDB Compass Chapter – 2 MongoDB Indexes Lab 2.1: Using Indexes to Sort Lab 2.2: Optimizing Compound Indexes Chapter – 3 Index Operations Lab 3.1: Explain Output Chapter 4: CURD Optimization Lab 4.1 – Equality, Sort, Range Chapter 5: Performance on Clusters Final Exam Question 1 Question 2 Question 3 Question 4 Question 5 Question 6 Question 7
Read more

Lab 2.1

Image
In this lab you're going to determine which queries are able to successfully use a given index for both filtering and sorting. Given the following index: {"first_name": 1, "address.state": -1, "address.city": -1, "ssn": 1} Which of the following queries are able to use it for both filtering and sorting? Check all that apply: Answer: db.people.find({ "first_name": { $gt: "J" } }).sort({ "address.city": -1 }) - No - This query doesn't use equality on the index prefix. When using an index for filtering and sorting the query must include equality conditions on all the prefix keys that precede the sort keys. Moreover, on the sort predicate it skipped the next key in the prefix "address.state". db.people.find({ "first_name": "Jessica" }).sort({ "address.state": 1, "address.city": 1 }) - Yes . This query matches with equality on the query predicate wit
Read more

Question 7

Image
Given the following indexes: { categories: 1, price: 1 } { in_stock: 1, price: 1, name: 1 } The following documents: { price: 2.99, name: "Soap", in_stock: true, categories: ['Beauty', 'Personal Care'] } { price: 7.99, name: "Knife", in_stock: false, categories: ['Outdoors'] } And the following queries: db.products.find({ in_stock: true, price: { $gt: 1, $lt: 5 }  }).sort({  name: 1 }) db.products.find({ in_stock: true }) db.products.find({ categories: 'Beauty'  }).sort({  price: 1 }) Whic h of  following is/are true? Answer: Index #1 would provide a sort to query #3. - True Index #2 properly uses the equality, sort, range rule for query #1. - False - If we were to build an index for query #1 using the equality, sort, range rule, then the index would be: { in_stock: 1, name: 1, price: 1 }. There would be a total of 4 index keys created across all of these documents and indexes. - False - As there would be 5 to
Read more