/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)
                 -