Couchbase Sqlpp Tuning

---
name: couchbase-sqlpp-tuning
description: "Diagnose and tune slow Couchbase SQL++ / N1QL queries. Use whenever the user asks about query performance, slow queries, EXPLAIN plans, why an index isn't being used, IntersectScan, PrimaryScan, covering indexes, partial indexes, array indexes (ANY / EVERY / UNNEST), index selection, query hints, the cost-based optimizer, the Index Advisor (ADVISE), system:completed_requests, query profiling (kernTime / servTime / execTime), pagination performance, prepared statements, or 'this query is slow / how do I make it faster.' Distinct from couchbase-data-modeling (document shape) and couchbase-mcp (operating the cluster) — this skill is about reading plans, designing the right indexes, and reshaping queries that already exist. Use proactively when the user shares an EXPLAIN output or a slow query."
license: MIT
---

# Couchbase SQL++ tuning

A skill for diagnosing and fixing slow SQL++ / N1QL queries on Couchbase Server (7.x and 8.x). The mechanics of reading execution plans, choosing the right index type, fixing common anti-patterns, and wiring up the diagnostic tools.

Distinct from the sibling skills:
- `couchbase-data-modeling` — how to MODEL the data (document shape, boundaries, access patterns)
- `couchbase-sizing` — how to size the cluster
- `couchbase-app-integration` — how to write app code that uses Couchbase
- `couchbase-migration-execution` — how to move data INTO Couchbase
- `couchbase-mcp` — operating the cluster (the MCP server's `cb_explain_query`, `cb_index_advisor`, `cb_perf_*` tools)
- **`couchbase-sqlpp-tuning` (this skill)** — making queries that already exist run faster

If the conversation is "this query is slow, what do I do," this is the right skill.

## When this skill applies

- "Why is this query slow?"
- "How do I read this EXPLAIN plan?"
- "Why isn't my index being used?"
- "I'm seeing PrimaryScan / IntersectScan — is that bad?"
- "How do I make this a covering index?"
- "Should I use a partial index here?"
- "How do I index an array field with ANY / EVERY / UNNEST?"
- "ADVISE recommended this index — should I create it?"
- "How do I tune pagination — LIMIT / OFFSET is slow at high offsets"
- "What does kernTime / servTime / execTime mean in the profile?"
- "Can I force the optimizer to use a specific index?"
- "What do the cost-based optimizer hints do in 7.6+?"

## Core principles (read first)

These are the headline rules. Read them before diving into references.

1. **Pareto applies to query tuning.** 80% of perf problems come from 20% of queries. Use `system:completed_requests` (or the MCP `cb_perf_longest_running` / `cb_perf_most_frequent` tools) to find that 20% first. Don't tune the wrong queries.

2. **CBO needs stats to help; without them it falls back to rule-based logic.** Couchbase has a cost-based optimizer (GA in 7.0, EE only), but it needs statistics on indexes and collections to do its job. Without stats, single-keyspace access is rule-based — index cardinality doesn't influence the choice, the optimizer picks based on which leading keys are in the WHERE clause. In 7.6+, statistics are gathered automatically when an index is created or built; in earlier versions, you run `UPDATE STATISTICS` manually. Either way: design indexes so the rules pick them, and run `UPDATE STATISTICS` on a schedule.

3. **The leading key of the index must appear in the WHERE clause** for an index to be picked. If a field can be missing, you need `INCLUDE MISSING` on the leading key, or you need `IS NOT MISSING` / `IS NOT NULL` in the WHERE clause to force selection.

4. **Cover the query when it's hot.** A covering index includes every field the query SELECTs and filters on, so the query never touches the Data service. Look for `"covers": [...]` in the EXPLAIN plan and the absence of a `Fetch` operator — that's the signal.

5. **Don't index low-cardinality fields like `docType` alone.** It causes IntersectScans and wrong plans. Use a partial index (`WHERE type = 'X'`) instead — the field gates the index, but isn't the leading key.

6. **Match the query shape to the index shape for arrays.** `ANY ... SATISFIES` and `ANY AND EVERY` can use array indexes; bare `EVERY` cannot. `UNNEST` must use the **exact same binding variable name** as the `CREATE INDEX ... FOR <var> IN ...`.

7. **Avoid PrimaryScan in production.** A PrimaryScan is the equivalent of a full table scan. Drop primary indexes in prod, or at least confirm no production query relies on one.

## Pick the right reference

| Question | Read |
|---|---|
| "How do I read this EXPLAIN plan? What's PrimaryScan / IntersectScan / Fetch?" | `references/explain-plan.md` |
| "What kind of index should I create? Covering / partial / array / composite / vector?" | `references/index-design.md` |
| "Why isn't my index being used? Common query anti-patterns and how to fix them" | `references/query-patterns.md` |
| "What does the cost-based optimizer do? What are the hints?" | `references/cost-based-optimizer.md` |
| "How do I wire this up with the MCP server tools (`cb_explain_query`, `cb_index_advisor`, `cb_perf_*`)" | `references/diagnostic-workflow.md` |
| "How do I do efficient pagination on a large result set?" | `references/pagination.md` |
| "How do I tune queries that join across keyspaces?" | `references/joins-and-cbo.md` |

## Workflow

The general approach to tuning a slow query:

```
1. Identify  →  Find the slow query (Pareto: top-20% by frequency × duration)
                Tools: cb_perf_longest_running, cb_perf_most_frequent,
                       system:completed_requests

2. Understand →  Run EXPLAIN. Read the plan.
                 Tools: cb_explain_query (returns plan + parsed findings)
                 What to look for: PrimaryScan? IntersectScan? Fetch present?
                                   Is the leading key of an index in WHERE?

3. Hypothesize → Pick one of:
                 - Add a covering index (everything in the index, no Fetch)
                 - Add a partial index (smaller, indexed on a subset)
                 - Add an array index (DISTINCT ARRAY ... FOR ... IN ... END)
                 - Reshape the query (drop OR predicates, add IS NOT MISSING)
                 - Add a USE INDEX hint
                 Tools: cb_index_advisor (ADVISE) for index recommendations

4. Verify     → Re-run EXPLAIN. Check the new index is picked.
                Run the query. Check kernTime / servTime / execTime in the profile.
                Tools: cb_explain_query, cb_query with profile=on

5. Iterate    → Repeat until the query meets SLA, or further tuning has
                diminishing returns. Most queries reach acceptable performance
                in 1-3 iterations.
```

## Anti-pattern checklist

Quick scan list — if you see any of these, jump to `references/query-patterns.md`:

- `SELECT *` from a large keyspace (forces Fetch, can't cover)
- `WHERE docType = 'X'` as the leading filter (low-cardinality leading key)
- `WHERE NOT (...)`, `!=`, `NOT IN` predicates (often not sargable)
- `OR` across different fields (often forces IntersectScan)
- `EVERY x IN arr SATISFIES ... END` without `ANY AND EVERY` (no array index)
- `UNNEST` binding variable not matching the index definition
- `LIMIT 10 OFFSET 1000000` (deep pagination — use KeySet pagination)
- Raw user input concatenated into the statement (injection + can't prepare)
- A query that runs thousands of times per second with no `PREPARE`

## Tooling

The MCP server in this project (`celticht32/MCP-Couchbase`) exposes the tuning tools you need:

| Tool | Purpose |
|---|---|
| `cb_explain_query` | EXPLAIN + parsed plan findings |
| `cb_index_advisor` | ADVISE statement; recommends indexes |
| `cb_perf_longest_running` | Top N queries by duration |
| `cb_perf_most_frequent` | Top N queries by frequency |
| `cb_perf_largest_responses` | Queries returning the most bytes |
| `cb_perf_large_result_count` | Queries returning the most rows |
| `cb_perf_using_primary_index` | Queries hitting the primary index (BAD in prod) |
| `cb_perf_not_using_covering_index` | Queries that could be covered but aren't |
| `cb_perf_not_selective` | Queries where the WHERE filter doesn't narrow much |
| `cb_perf_by_user` | (8.x) attribute slow queries to a specific user |
| `cb_get_schema_for_collection` | Sample document schema (for designing the right index) |

See `references/diagnostic-workflow.md` for the full step-by-step using these tools.

## Version notes

- **Pre-7.0:** No scope/collection — indexes are bucket-level
- **7.0:** Scopes and collections; covering, partial, array indexes; CBO went GA (preview was in 6.5) — requires `UPDATE STATISTICS` to be useful
- **7.1+:** `INCLUDE MISSING` for leading index keys (so docs without the leading-key field still get indexed)
- **7.6+:** CBO auto-gathers stats on index create/build; join-enumeration improvements; `/*+ ... */` optimizer hints (`productivity`, `ORDERED`, `USE HASH`); `UPDATE STATISTICS` still available for manual refresh
- **8.0+:** Vector indexes (HYPERSCALE / COMPOSITE VECTOR INDEX); Auto Update Statistics (AUS) keeps stats fresh automatically; FTS synonym sets; user lock/unlock; XDCR conflict logging

Always verify against the cluster version before recommending a feature — the MCP `cb_mcp_status` tool reports the version it has detected.