Couchbase Sqlpp Tuning

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

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:

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.