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` (MCP) or `db.<coll>.getIndexes()` (mongosh) to get existing indexes on the collection
2. Use `explain_operation` (MCP) or `.explain("executionStats")` (mongosh)
   to get explain output with execution stats
3. Use `find_documents` (MCP) or `db.<coll>.findOne()` (mongosh) 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` (MCP) or `show dbs` (mongosh) to understand the database structure
2. Use `get_statistics` with scope "collection" (MCP) or `db.collection.stats()` (mongosh) to identify large collections
3. Use `get_statistics` with scope "index" (MCP) or `db.collection.aggregate([{$indexStats:{}}])` (mongosh) to check existing index usage
4. Use `current_ops` (MCP) or `db.currentOp()` (mongosh) to see currently running operations
5. Suggest reviewing the most-used collections for missing indexes

## MCP Tools Available

When DocumentDB MCP server is connected, these tools are available:

| Tool name (exact) | Description |
| :--- | :--- |
| `list_indexes` | List all indexes on a collection — check if the query can use an existing index |
| `explain_operation` | Run explain with executionStats for any operation (find, aggregate, count) |
| `find_documents` | Fetch sample documents to understand schema — use with limit=1 |
| `get_statistics` | Get collection or index statistics (use scope: "collection" or "index") |
| `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) |
| `sample_documents` | Sample random documents from a collection |

## Local mongosh Commands (No MCP Required)

All diagnostic operations can be performed directly via mongosh. Use these when
MCP is not available or for quick ad-hoc diagnostics:

```javascript
// Explain a find query
db.collection.find({filter}).explain("executionStats")

// Explain an aggregation pipeline
db.collection.aggregate([{$match:...}, {$group:...}]).explain("executionStats")

// Explain a count
db.runCommand({explain: {count: "collection", query: {filter}}, verbosity: "executionStats"})

// Collection statistics
db.collection.stats()

// List indexes
db.collection.getIndexes()

// Index usage statistics
db.collection.aggregate([{$indexStats: {}}])

// Sample documents
db.collection.aggregate([{$sample: {size: 3}}])

// Currently running operations
db.currentOp()
```

## 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:

**Via MCP (when connected):**
```
list_indexes({ db_name: "<db>", collection_name: "<coll>" })
```

```
explain_operation({
  db_name: "<db>",
  collection_name: "<coll>",
  operation: {
    find: "<coll>",
    filter: <filter>,
    sort: <sort>,
    projection: <projection>,
    limit: <n>
  }
})
```

From Azure/documentdb-agent-kit, MIT.

More from Azure/documentdb-agent-kit