documentdb-query-optimizer

Files (3)

• SKILL.md
• core-indexing-principles.md · references/
• index-support-queries.md · references/

SKILL.md (first lines)

# DocumentDB Query Optimizer

## When This Skill Is Invoked

Invoke **only** when the user wants:

- Query/index **optimization** or **performance** help
- **Why** a query is slow or **how to speed it up**
- **Slow queries** on their cluster and/or **how to optimize them**
- Index recommendations or index review

Do **not** invoke for routine query authoring unless the user has requested help
with optimization, slow queries, or indexing.

## High Level Workflow

### Help with a Specific Query

If the user is asking about a particular query:

1. Use `list_indexes` to get existing indexes on the collection
2. Use `optimize_find_query` (for find queries) or `explain_aggregate_query`
   (for aggregation pipelines) to get explain output with execution stats
3. Use `find_documents` with limit=1 to fetch a sample document to understand the
   schema

Then make an optimization suggestion based on collected information and best
practices from the reference files. Prefer creating an index that fully covers
the query if possible.

### General Performance Help

If the user wants to examine slow queries or is looking for general performance
suggestions (not regarding any particular query):

1. Use `list_databases` and `get_db_info` to understand the database structure
2. Use `collection_stats` to identify large collections
3. Use `index_stats` to check existing index usage
4. Use `current_ops` to see currently running operations
5. Suggest reviewing the most-used collections for missing indexes

## MCP Tools Available

**Database tools** (for query optimization):

| Tool name (exact) | Description |
| :--- | :--- |
| `list_indexes` | List all indexes on a collection — check if the query can use an existing index |
| `optimize_find_query` | Run explain with executionStats for a find query, returning metrics, plan shape, index stats, and collection stats in one call |
| `explain_aggregate_query` | Run explain with executionStats for an aggregation pipeline |
| `explain_find_query` | Run explain for a find query (lower-level than optimize_find_query) |
| `explain_count_query` | Run explain for a count query |
| `find_documents` | Fetch sample documents to understand schema — use with limit=1 |
| `collection_stats` | Get collection statistics (size, document count, storage) |
| `index_stats` | Get index usage statistics ($indexStats) |
| `current_ops` | Get currently running database operations |
| `create_index` | Create a new index (only after user approval) |
| `drop_index` | Drop an existing index (only after user approval) |

## Load References

Before beginning diagnosis and recommendation, load reference files.

Always load:

- `references/core-indexing-principles.md`

## Diagnostic Workflow

### Step 1: Gather Information

For a specific query, run these tools (when MCP is connected):

```
list_indexes({ db_name: "<db>", collection_name: "<coll>" })
```

```
optimize_find_query({
  db_name: "<db>",
  collection_name: "<coll>",
  query: <filter>,
  options: { sort: <sort>, projection: <projection>, limit: <n> }
})
```

For aggregation pipelines:
```
explain_aggregate_query({
  db_name: "<db>",
  collection_name: "<coll>",
  pipeline: <pipeline_array>
})
```

### Step 2: Analyze Explain Output

From the `optimize_find_query` / `explain_aggregate_query` response, extract:

- **metrics**: `totalKeysExamined`, `totalDocsExamined`, `nReturned`,
  `executionTimeMillis`
- **plan_shape**: winning plan stage (IXSCAN vs COLLSCAN), index used
- **indexes_stats**: which indexes exist and their usage frequency
- **collection_stats**: total document count, average document size

**Key ratios to evaluate:**

| Metric | Good | Bad |
| :--- | :--- | :--- |
| keysExamined / nReturned | Close to 1 | >> 1 (poor selectivity) |
| docsExamined / nReturned | Close to 1 | >> 1 (scanning too many docs) |
| Plan stage | IXSCAN | COLLSCAN (no index) |
| Sort stage | In-memory: false | In-memory: true (blocking sort) |

### Step 3: Diagnose

Common issues and their root causes:

- **COLLSCAN** → No index supports the query filter. Create an index on

From khelanmodi/documentdb-agent-kit, MIT.

More from khelanmodi/documentdb-agent-kit