Indexes Scores and Explain - simagix/keyhole GitHub Wiki

Indexes Scores and Explain

Use keyhole to evaluate a query shape and display:

  • Explain results of indexes selected by mongo engine
  • Indexes scores of all possible applicable indexes
  • Cardinality of all fields of the query shape
  • Index recommendation

Usage

keyhole --explain <input_file> <connection_string>

where an input file can contain a mongo log line or a JSON document.

Log Line Example

2019-06-26T10:51:53.448-0400 I COMMAND [conn23075] command keyhole.cars appName: "MongoDB Shell" command: find { find: "cars", filter: { color: "Red", style: "Truck" }, sort: { year: 1.0 }, lsid: { id: UUID("36f308ad-ab58-4053-bd0f-d2d815269f4e") }, $clusterTime: { clusterTime: Timestamp(1558882305, 1), signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 } }, $db: "keyhole" } planSummary: IXSCAN { color: 1, style: 1, year: 1 } cursorid:66325545984 keysExamined:101 docsExamined:101 fromMultiPlanner:1 numYields:4 nreturned:101 reslen:34418 locks:{ Global: { acquireCount: { r: 5 } }, Database: { acquireCount: { r: 5 } }, Collection: { acquireCount: { r: 5 } } } storage:{ data: { bytesRead: 14745, timeReadingMicros: 5342 } } protocol:op_msg 7ms

JSON Input Format

{
    "ns": "<db>.<collection>"
    "filter": <JSON>,
    "sort": <JSON>
}

where ns and filter are required. An example:

{
    "ns": "keyhole.cars",
    "filter": { "color": "Red", "style": "Truck" },
    "sort": { "year": 1 }
}

Output examples

Below is an example with results. The discussions are divided into different sections. First of all, execute the commands below to generate outputs.

$ export MONGO_URI="mongodb+srv://user:[email protected]/keyhole"
$ keyhole --seed --drop --total 101000 $MONGO_URI
$ mongo $MONGO_URI --eval 'db.cars.createIndex({color: 1})'
$ mongo $MONGO_URI --eval 'db.cars.createIndex({style: 1})'
$ mongo $MONGO_URI --eval 'db.cars.createIndex({color: 1, style: 1, year: 1})'
$ keyhole --explain mongod.log $MONGO_URI

Cluster and Query Shape

Cluster: Replica Set
Query Shape: {"filter":{"color":"Red","style":"Truck"},"sort":{"year":1}}

AllPlansExecution Results

Results of explain('allPlansExecution') with scores.

=> Execution Stats
=========================================
Winning Plan:
  ├─score: 1.9994220368744513
  ├─totalKeysExamined: 1138
  ├─totalDocsExamined: 1138
  ├─executionStages: FETCH
  ├─  advanced: 1138
  ├─  works: 1139
  └─  executionTimeMillisEstimate: 0
    ├─inputStage: IXSCAN
    ├─  key pattern: {"color":1,"style":1,"year":1}
    ├─  advanced: 1138
    ├─  works: 1139
    └─  executionTimeMillisEstimate: 0

=> All Plans Execution
=========================================
Query Plan 1:
├─score: 2.0003
├─totalKeysExamined: 101
├─totalDocsExamined: 101
├─executionStages: FETCH
├─  advanced: 101
├─  works: 101
└─  executionTimeMillisEstimate: 0
  ├─inputStage: IXSCAN
  ├─  key pattern: {"color":1,"style":1,"year":1}
  ├─  advanced: 101
  ├─  works: 101
  └─  executionTimeMillisEstimate: 0

Query Plan 2:
├─score: 1.0003
├─totalKeysExamined: 100
├─totalDocsExamined: 100
├─executionStages: SORT
├─  advanced: 0
├─  works: 101
└─  executionTimeMillisEstimate: 0
  ├─inputStage: SORT_KEY_GENERATOR
  ├─  advanced: 7
  ├─  works: 101
  └─  executionTimeMillisEstimate: 0
    ├─inputStage: FETCH
    ├─  filter: {"color":{"$eq":"Red"}}
    ├─  advanced: 7
    ├─  works: 100
    └─  executionTimeMillisEstimate: 0
      ├─inputStage: IXSCAN
      ├─  key pattern: {"style":1}
      ├─  advanced: 100
      ├─  works: 100
      └─  executionTimeMillisEstimate: 0

Query Plan 3:
├─score: 1.0003
├─totalKeysExamined: 100
├─totalDocsExamined: 100
├─executionStages: SORT
├─  advanced: 0
├─  works: 101
└─  executionTimeMillisEstimate: 0
  ├─inputStage: SORT_KEY_GENERATOR
  ├─  advanced: 18
  ├─  works: 101
  └─  executionTimeMillisEstimate: 0
    ├─inputStage: FETCH
    ├─  filter: {"style":{"$eq":"Truck"}}
    ├─  advanced: 18
    ├─  works: 100
    └─  executionTimeMillisEstimate: 0
      ├─inputStage: IXSCAN
      ├─  key pattern: {"color":1}
      ├─  advanced: 100
      ├─  works: 100
      └─  executionTimeMillisEstimate: 0

All Applicable Indexes Scores

Index scores of all possible indexes. Indexes are picked from all indexes of the collection, where the prefix of an index is one of the fields of the query shape.

=> All Applicable Indexes Scores
=========================================
[
  {
    "index": {
      "color": 1,
      "style": 1,
      "year": 1
    },
    "score": 1.9994220368744513
  },
  {
    "index": {
      "color": 1
    },
    "score": 1.138507432596551
  },
  {
    "index": {
      "style": 1
    },
    "score": 1.0642057284339654
  }
]

Cardinality by Query Shape

This part displays the data count of each key from a sampled data set.

=> Cardinality (sampled data: 4,999):
=========================================
     14: color
      8: year
      6: style

Index Recommendation

This is work in progress, but the principle of sort, equality, sort, and range, are all taken into consideration.

Index Suggestion: {"color":1,"style":1,"year":1}

Output File

The result is saved as a JSON document.

Explain output written to cars.log-explain.json.gz

To read the outputs, execute the command without mongo uri:

keyhole --explain cars.log-explain.json.gz
⚠️ **GitHub.com Fallback** ⚠️