Most Couchbase performance tickets I have seen end the same way: someone adds more nodes, the dashboard looks a little better for a week, and the same query shows up in the slow log a month later. The node count was never the problem. The index was.

This is a working reference, not an essay. I already wrote the reflective version of what indexing in Couchbase teaches you about systems in general. This post skips the reflection and gets straight to the decisions: which index type to reach for, how to order composite keys, how to tell if a query is actually using what you built, and which four or five mistakes account for most of the slow queries you will ever debug. Couchbase’s own tuning tutorial is the primary source for the recommendations below. Every example runs against travel-sample, the sample bucket Couchbase ships with every install, so you can paste these into your own cluster and see the plan yourself.

The Three Services, Just Enough to Matter Here

Couchbase splits into three services, and indexing only makes sense once you know which one does what.

The Query Service parses SQL++ (the language Couchbase used to call N1QL) and decides which index, if any, can answer the query. The Index Service maintains Global Secondary Indexes (GSIs), which are separate structures built from document fields, not the documents themselves. The Data Service holds the actual documents and answers key-value fetches.

Every index decision in this post is really a decision about how much work the Query Service can finish inside the Index Service before it has to go ask the Data Service for the rest. The less it has to ask, the faster the query.

Primary Index: The One You Should Almost Never Use in Production

1CREATE PRIMARY INDEX ON `travel-sample`;

A primary index maps every document key in a keyspace to itself. It exists so SELECT * FROM travel-sample works with zero setup, which makes it perfect for a five-minute demo and a liability everywhere else. A primary index scan is a full keyspace scan, every document, every time, the same category of operation as SELECT * FROM a_million_row_table with no WHERE clause on any relational database.

If EXPLAIN on a production query shows "index": "#primary", that is not the database being slow. That is a missing secondary index, and the fix is always the same: build one that actually matches the query’s predicates, then drop the primary index, or at least make sure the query planner never reaches for it by mistake.

Composite Indexes: Key Order Is Not Arbitrary

A composite (compound) index combines multiple fields into one index, and the order of those fields determines which queries it can actually serve efficiently. Get the order wrong and the index still exists, it just does far less work than it looks like it should.

The ordering rule, in priority order:

1. Equality predicates (WHERE city = "Paris")
2. IN predicates (WHERE city IN ["Paris", "London"])
3. LESS THAN / LESS THAN OR EQUAL
4. BETWEEN
5. GREATER THAN / GREATER THAN OR EQUAL
6. Array predicates
7. Any remaining fields needed only for covering (fields the query selects or filters on with something other than the above)

Within the same predicate type, order by cardinality, the field with more distinct values first. A country field with 5 values does far less work eliminating rows than a city field with 500.

One relief: you don’t have to write your query’s WHERE clause in this exact order. This rule governs how you declare the CREATE INDEX statement, not how you phrase the query, the planner reorders predicates internally to match whatever index order is available.

 1-- Query: find vacant hotels in a given city, cheapest first
 2SELECT name, price
 3FROM `travel-sample`
 4WHERE type = 'hotel' AND city = $city AND vacancy = true
 5ORDER BY price
 6LIMIT 20;
 7
 8-- Bad: vacancy has lower cardinality than city, and it's listed first
 9CREATE INDEX idx_hotels_bad ON `travel-sample` (vacancy, city, price)
10WHERE type = 'hotel';
11
12-- Better: equality fields ordered by cardinality, then the ORDER BY field
13CREATE INDEX idx_hotels_good ON `travel-sample` (city, vacancy, price)
14WHERE type = 'hotel';

Both indexes technically “work.” The second one eliminates far more rows per key comparison before it ever touches price, because city (hundreds of distinct values) is far more selective than vacancy (only true or false). On a small sample bucket the difference is invisible. On a keyspace with a few million documents, it is the difference between milliseconds and seconds.

Indexing to Avoid Sorting

A GSI doesn’t just store field values, it stores them pre-sorted by the index’s key order. When a query’s ORDER BY lines up with that same order, positioned right after the predicate keys, the Query Service reads rows straight off the index in the order it already needs and skips a separate sort step entirely.

1-- ORDER BY price matches the index's key order (after the equality predicate on city)
2SELECT name, price FROM `travel-sample`
3WHERE type = 'hotel' AND city = 'Paris'
4ORDER BY price;
5
6CREATE INDEX idx_hotel_city_price ON `travel-sample` (city, price)
7WHERE type = 'hotel';

If the query needs descending order, declare that direction in the index itself rather than leaving the Query Service to reverse a whole result set afterward:

1SELECT name, price FROM `travel-sample`
2WHERE type = 'hotel' AND city = 'Paris'
3ORDER BY price DESC;
4
5CREATE INDEX idx_hotel_city_price_desc ON `travel-sample` (city, price DESC)
6WHERE type = 'hotel';

EXPLAIN shows whether this actually worked. An explicit Order operator in the plan means the Query Service sorted in memory after the scan. No Order operator means the index handled it for free. This is worth checking on any query with an ORDER BY: sorting a small result set is invisible, sorting a large one right before a LIMIT is a common source of latency that looks, from the outside, like the index isn’t helping at all.

Favor Equality Over Ranges

Given a choice, design the query and the index around equality predicates instead of ranges. An equality match is a direct lookup inside the index’s sorted structure. A range (<, >, BETWEEN) has to walk every entry between the two bounds, and the cost scales with how wide that range is, not with how many rows eventually match.

Timestamps are the field type this bites most often, and travel-sample’s own documents don’t carry one worth ranging over, so here’s the pattern as you’d hit it against any timestamped collection instead, orders, events, sessions. “Give me everything from a particular day” usually gets written as a range over an ISO-8601 timestamp:

1-- Range scan: walks every index entry between the two timestamps
2SELECT * FROM `travel-sample`
3WHERE type = 'order'
4  AND orderDate >= '2026-07-28T00:00:00' AND orderDate < '2026-07-29T00:00:00';

A functional index on just the date portion turns the same query into an equality lookup:

1CREATE INDEX idx_order_date ON `travel-sample` (SPLIT(orderDate, "T")[0])
2WHERE type = 'order';
3
4SELECT * FROM `travel-sample`
5WHERE type = 'order' AND SPLIT(orderDate, "T")[0] = '2026-07-28';

This isn’t only about scan cost. Equality predicates are also what enables partition elimination on a partitioned index (more on that below), a range predicate generally can’t tell which partitions to skip, so it ends up scanning all of them.

Covering Indexes: Skip the Fetch Entirely

An index covers a query when every field the query needs, filters, selects, and sorts on, already lives in the index. When that’s true, the Query Service never has to ask the Data Service for the full document. It answers straight from the Index Service.

The idx_hotel_city_price index from the previous section is already doing double duty here, the same index that avoids a sort also covers any query that only touches city, price, and the implicit type filter:

1-- Covered: city, price, and type all come from idx_hotel_city_price
2SELECT city, price FROM `travel-sample`
3WHERE type = 'hotel' AND city = 'Paris';
4
5-- Not covered: name isn't in the index, so this triggers a Data Service fetch per row
6SELECT name, city, price FROM `travel-sample`
7WHERE type = 'hotel' AND city = 'Paris';

EXPLAIN tells you directly. Look for a covers array in the IndexScan3 operator, if the fields you selected aren’t listed there, you’re paying for a fetch.

1EXPLAIN SELECT city, price FROM `travel-sample` WHERE type = 'hotel' AND city = 'Paris';

1{
2  "#operator": "IndexScan3",
3  "index": "idx_hotel_city_price",
4  "covers": [
5    "cover ((`travel-sample`.`city`))",
6    "cover ((`travel-sample`.`price`))",
7    "cover ((`travel-sample`.`type`))"
8  ]
9}

SELECT * breaks this every single time, by definition. * means “give me the whole document,” and no index (short of duplicating every field in the collection) can cover that. If a query plan needs to stay covering, name the fields it needs. This alone is one of the highest-leverage changes you can make to an existing codebase full of SELECT *.

Covering isn’t limited to document fields, either. Couchbase’s META() function, META().id for the document key, META().cas for its CAS value, can be indexed and selected the same way:

1CREATE INDEX idx_hotel_cas ON `travel-sample` (city, META().cas)
2WHERE type = 'hotel';
3
4SELECT META().id, META().cas FROM `travel-sample`
5WHERE type = 'hotel' AND city = 'Paris';

That’s useful for an optimistic-locking check, confirm a document’s CAS hasn’t changed since it was last read, without a full document fetch, or for any query that only ever needed the key back, META().id in the SELECT list instead of a real field.

Partial (Filtered) Indexes: Index a Subset, Not Everything

Every CREATE INDEX above already has a WHERE type = 'hotel' clause. That’s deliberate, and it should be the default, not the exception. A partial index only indexes documents matching its filter, which means a smaller index, a faster build, and a faster scan, because the Index Service never has to store or skip over documents the query would never match anyway.

1-- Index only hotel documents, not the whole travel-sample bucket
2CREATE INDEX idx_cx ON `travel-sample` (state, city)
3WHERE type = 'hotel';

One easy mistake to avoid here: don’t repeat the partial filter’s equality field as an index key too. If every document the index covers already has type = 'hotel' by definition of the WHERE clause, indexing type again wastes space and adds nothing:

1-- Wasteful: type is already guaranteed by the WHERE clause
2CREATE INDEX idx_bad ON `travel-sample` (type, city)
3WHERE type = 'hotel';
4
5-- Correct: the WHERE clause already filters on type, don't index it again
6CREATE INDEX idx_good ON `travel-sample` (city)
7WHERE type = 'hotel';

A consistent type (or docType) field on every document is what makes this pattern possible in the first place. If your data model doesn’t tag documents with a stable type field, partial indexes have nothing to filter on, and you’re back to indexing the whole keyspace.

That type field is one of three habits worth building into the document model itself, before a collection has millions of documents in it and these become much harder to retrofit.

Give every document a consistent type field. Covered just above, and worth repeating: without it, there’s no cheap way to filter an index down to “just hotels” or “just routes”, every index falls back to scanning the entire keyspace.

Store computed values, don’t recompute them at query time. If a hotel document has a reviews array, and queries frequently need the average rating or review count, store average_rating and review_count as fields on the document itself, updated when a review is added, rather than making every query aggregate the array on the fly. A stored field can be indexed and read straight from a covering index. An aggregate computed inside the query cannot.

Avoid dynamic or unpredictable key names. A document shaped like {"amenityDetails": {"wifi": true, "pool": true}} can’t be indexed by key, because CREATE INDEX needs to name a field, and wifi and pool are values here, not a fixed field name, some other document in the same collection might have {"spa": true, "gym": true} instead. Restructure it as an array, {"amenities": ["wifi", "pool"]}, and it becomes exactly the kind of array index the next section covers.

Array Indexes: Indexing Inside a Document

Couchbase documents routinely nest arrays, travel-sample’s route documents each carry a stops array of intermediate airport codes. Querying inside those arrays needs its own index shape.

1-- Document shape: { "type": "route", "sourceairport": "SFO", "destinationairport": "JFK", "stops": ["ORD"] }
2
3CREATE INDEX idx_route_stops ON `travel-sample` (DISTINCT ARRAY s FOR s IN stops END)
4WHERE type = 'route';
5
6SELECT sourceairport, destinationairport FROM `travel-sample`
7WHERE type = 'route' AND ANY s IN stops SATISFIES s = 'ORD' END;

For partial text matching inside an array (autocomplete-style search on names, tags, or amenities), reach for SUFFIXES() or TOKENS() instead of a leading-wildcard LIKE:

 1-- Bad: a leading wildcard forces a full index scan, wildcard or not
 2SELECT name FROM `travel-sample`
 3WHERE type = 'hotel' AND name LIKE '%luteti%';
 4
 5-- Good: index the suffixes, then the same search becomes a targeted lookup
 6CREATE INDEX idx_name_suffixes ON `travel-sample` (ALL ARRAY s FOR s IN SUFFIXES(LOWER(name)) END)
 7WHERE type = 'hotel';
 8
 9SELECT name FROM `travel-sample`
10WHERE type = 'hotel'
11  AND ANY s IN SUFFIXES(LOWER(name)) SATISFIES s LIKE 'luteti%' END;

One more distinction that’s easy to get backwards: IN checks the current array level, WITHIN recurses into every nested array and object underneath it. WITHIN is more powerful and noticeably more expensive. Reach for IN unless you specifically need to search nested structures you can’t flatten.

Replacing OR With an Array Index

An OR across two different fields forces Couchbase into a UnionScan: one index scan per side of the OR, then a merge. It’s the same problem IntersectScan causes for AND across narrow indexes, just for the opposite operator, and it has the same fix: give the planner one index instead of two scans to reconcile. Searching a hotel by name, where the guest might type either its formal name or its shorter alias, is a real example of this shape in travel-sample itself:

 1-- OR across two fields: one scan per branch, then a union
 2SELECT name FROM `travel-sample`
 3WHERE type = 'hotel' AND (name = $query OR alias = $query);
 4
 5-- A functional array index turns the same lookup into a single DistinctScan
 6CREATE INDEX idx_hotel_name_alias ON `travel-sample` (DISTINCT ARRAY v FOR v IN [name, alias] END)
 7WHERE type = 'hotel';
 8
 9SELECT name FROM `travel-sample`
10WHERE type = 'hotel' AND ANY v IN [name, alias] SATISFIES v = $query END;

Add more fields to this index and it stays covering the same way any other array index does. The DistinctScan replaces the UnionScan, nothing else about how the index behaves changes.

Stop the IntersectScan: One Wide Index Beats Three Narrow Ones

If a query filters on three fields and three separate single-field indexes each match one of them, Couchbase can technically use all three at once and intersect the results. It works. It is also close to the worst way to answer that query.

Each narrow index scan returns its own candidate set, and the Query Service has to merge all three before it can return anything, an IntersectScan operator in the plan is the tell. A single composite index ordered correctly for the query (equality fields, then ranges, per the ordering rule above) answers the same query in one ordered scan with no merge step. If you find yourself adding index after index chasing individual query clauses, stop and check whether one wider composite index, matching the field order rule, replaces all of them.

The same logic applies to indexes built purely on a low-cardinality type or docType field with nothing else. An index like that matches almost every document in the keyspace, which means the query planner treats it as barely better than the primary index, an expensive fallback rather than a real optimization.

The same consolidation instinct applies across separate queries, not just within one. If three different endpoints each query travel-sample filtered by city, plus one other field apiece (state for one, price for another, country for a third), a single index on (city, state, price, country) can often serve all three, instead of maintaining three separate indexes that each duplicate the city prefix. Whether that consolidation is worth it depends on whether the combined index still meets each query’s own latency budget, check EXPLAIN against each of the three queries before committing to one wide index over three narrow ones.

Avoid USE INDEX Hints

N1QL lets you force a specific index by name:

1SELECT name FROM `travel-sample` USE INDEX (idx_hotel_city_price)
2WHERE type = 'hotel' AND city = 'Paris';

Resist reaching for it as a default habit. The query planner is rule-based and, given the indexes covered in this post, usually makes a reasonable choice on its own. Naming an index in application code creates a dependency the planner can’t see past: that index can’t be dropped, renamed, or replaced without also changing and redeploying whatever code hints at it, and adding a better index later does nothing for a query that’s still hardcoded to the old one.

The narrow, legitimate use is diagnostic: temporarily forcing a specific index while tracking down why the planner picked a worse plan, or manually working around an IntersectScan you haven’t consolidated yet. Treat it as a debugging tool you remove once the underlying index shape is fixed, not a permanent fixture in production code.

Index Replication and Partitioning for Scale

Two settings on CREATE INDEX matter once a single index node stops being enough.

Replicas protect against losing an index node, and unlike data replicas they’re active: Couchbase load-balances queries across all of them, so num_replica buys query throughput, not just failover. It defaults to 0 unless you set a cluster-wide default via the indexer.settings.num_replica setting, and the value always has to be less than the number of index nodes you actually have. These are separate CREATE INDEX statements from idx_hotel_city_price above, not settings bolted onto it after the fact, Couchbase can’t add replicas or partitioning to an existing index without recreating it:

1CREATE INDEX idx_hotel_city_price_r2 ON `travel-sample` (city, price)
2WHERE type = 'hotel'
3WITH { "num_replica": 2 };
4
5-- Pin the original and its replicas to specific nodes instead of letting
6-- Couchbase place them: the "nodes" array length should equal num_replica plus one.
7CREATE INDEX idx_hotel_city_price_r2_pinned ON `travel-sample` (city, price)
8WHERE type = 'hotel'
9WITH { "num_replica": 2, "nodes": ["index1.internal", "index2.internal", "index3.internal"] };

Partitioning splits one large index across multiple nodes by a hash of an expression, useful once a single index no longer fits comfortably in one node’s memory. Before Couchbase Server 5.5, this meant manually building separate range-partitioned indexes yourself, one index per key range (LOWER(username) >= 'a' AND < 'n', another for 'n' to 'z', and so on) and gluing the results together in the application. PARTITION BY HASH replaces all of that: Couchbase decides which of num_partitions partitions (16 by default) each document lands in, and spreads those partitions across the available index nodes automatically.

1CREATE INDEX idx_hotel_city_price_partitioned ON `travel-sample` (city, price)
2WHERE type = 'hotel'
3PARTITION BY HASH(city)
4WITH { "num_replica": 1 };

The payoff shows up as partition elimination: a query with an equality predicate on the partition key only has to scan the one partition that document could possibly be in, not all sixteen. This is one more reason equality predicates (the previous section) matter more than they look, a range predicate on a partitioned field generally can’t tell which partitions to skip.

When you’re creating several indexes on a bucket that already has data (a new feature launch, a migration, a new query pattern), build them deferred and trigger the build once instead of once per index:

1CREATE INDEX idx_a ON `travel-sample` (city) WHERE type = 'hotel' WITH { "defer_build": true };
2CREATE INDEX idx_b ON `travel-sample` (country) WHERE type = 'hotel' WITH { "defer_build": true };
3CREATE INDEX idx_c ON `travel-sample` (price) WHERE type = 'hotel' WITH { "defer_build": true };
4
5BUILD INDEX ON `travel-sample` (idx_a, idx_b, idx_c);

Deferred indexes share a single DCP stream (Couchbase’s internal change stream) when built together, so the Index Service reads every document once and feeds all three indexes from that one pass, instead of re-reading the entire keyspace for each index in turn.

Projection Selectivity: What Every Mutation Costs

Every index has an upkeep cost that’s easy to forget, because it doesn’t show up when you run a query, it shows up on every write. The Data Service’s Projector inspects each document mutation and checks it against every index definition on that keyspace; anything that matches gets routed to the Index Service to update. Projection selectivity is the fraction of mutations that actually qualify: qualifying documents divided by total documents mutated.

An index filtered down to a narrow subset, WHERE type = 'hotel' AND country = 'US' against a keyspace that’s mostly something else, might see a selectivity of a fraction of a percent: almost every mutation gets checked and immediately skipped, cheaply. An index with no filter, or one whose filter matches nearly everything in the keyspace, sits at or near 100% selectivity: every single mutation in the keyspace triggers a real update to that index.

100% selectivity isn’t automatically wrong, an index genuinely meant to cover most of a keyspace pays that cost on purpose. What’s worth catching is the accidental version: a partial index whose WHERE clause is broader than it needs to be, or a composite index that quietly lost its filter during a rewrite. This is the concrete cost partial indexes (covered earlier) are actually buying you: keeping this number low so most mutations never touch the Index Service at all.

Query-Side Tuning

Indexes only get you half the win. The query itself needs to actually use them well.

USE KEYS when you already have the document key. If you know the key, skip the Index Service entirely and go straight to the Data Service, the same cost as a key-value get. It accepts more than one key:

1SELECT * FROM `travel-sample` USE KEYS ['hotel_123', 'hotel_456'];

It works the same way inside a JOIN’s ON KEYS clause, whenever one document embeds another’s key directly, instead of needing its own index lookup to find the match. travel-sample’s hotels embed their reviews as an array rather than storing them as separate documents, so this doesn’t come up inside the sample data itself, but it’s a common shape elsewhere: an order document that stores its customer’s key directly, for example.

1-- General pattern, not travel-sample: orders store the customer's key directly
2SELECT o.total, c.name AS customer_name
3FROM orders o
4JOIN customers c ON KEYS o.customerId;

And if the query doesn’t need SQL++ at all, just the full document by a key you already have, skip the Query Service entirely too. A plain key-value GET() through the SDK is cheaper than any N1QL statement, USE KEYS included, because it never touches the Query Service’s parsing and planning path in the first place.

Prepared statements, not ad hoc SQL++ on every call. A prepared statement parses and plans once, then reuses that plan on every execution. From the SDK, this usually means setting adhoc: false on the query options. From the shell or a raw HTTP call:

1PREPARE hotel_by_city AS
2  SELECT name, price FROM `travel-sample`
3  WHERE type = 'hotel' AND city = $city;
4
5EXECUTE hotel_by_city USING { "city": "Paris" };

Parameters, not string concatenation. $city above, not ' + city + ' spliced into the query text. This is a SQL-injection defense first, but it also means the Query Service sees the same query shape every time regardless of the value, which is what makes the prepared plan reusable in the first place.

One query with CASE, not several queries for several aggregates. If a dashboard needs several different counts filtered several different ways over the same keyspace, it’s faster to compute all of them in one pass than to scan the same data repeatedly:

1SELECT
2  COUNT(CASE WHEN free_internet THEN 1 END) AS free_wifi_count,
3  COUNT(CASE WHEN free_breakfast THEN 1 END) AS free_breakfast_count,
4  COUNT(CASE WHEN free_parking THEN 1 END) AS free_parking_count
5FROM `travel-sample`
6WHERE type = 'hotel';

Pagination: Pushdown vs Keyset

LIMIT and OFFSET only get pushed down into the Index Service, meaning the index itself stops early instead of the Query Service fetching everything and truncating afterward, when four conditions all hold: the query’s predicates fit a single index (no IntersectScan), there’s no join, and ORDER BY matches the index’s own key order. Break any one of those and LIMIT 20 still means “scan everything, then keep 20.”

Confirm this actually happened rather than assuming it did: EXPLAIN the query and check the IndexScan3 operator for explicit limit and offset properties. If they’re missing, the Query Service is applying LIMIT/OFFSET itself after assembling the full result set, meaning one of the four conditions above wasn’t met.

Even with pushdown working, OFFSET on a deep page is the same problem the API design post covers for any database: the index still has to walk past every skipped row before it can return anything. For a paginated feed backed by Couchbase, a keyset-style cursor, “give me rows after this specific (price, id) pair,” not “skip this many rows”, avoids the walk entirely and stays fast at any depth:

1-- Offset pagination: gets slower every page, deep pages walk thousands of skipped rows
2SELECT name, price FROM `travel-sample`
3WHERE type = 'hotel' AND city = 'Paris'
4ORDER BY price LIMIT 20 OFFSET 200;
5
6-- Keyset pagination: constant cost regardless of depth
7SELECT name, price FROM `travel-sample`
8WHERE type = 'hotel' AND city = 'Paris' AND price > $last_seen_price
9ORDER BY price LIMIT 20;

Scan Consistency: The Tradeoff Nobody Reads the Docs For

Couchbase’s indexes update asynchronously from writes. A document written a moment ago might not be reflected in an index yet. Every query picks a consistency level, and the default is not always the right default for the query in front of you.

These aren’t three tiers of the same thing, AT_PLUS and REQUEST_PLUS solve the consistency problem in genuinely different ways. AT_PLUS is cheaper precisely because it only waits for the mutations you tell it about, the tradeoff is that your application has to capture and pass along the mutation token from the write it cares about. REQUEST_PLUS needs nothing from the caller, it just waits for everything, which is simpler to use and more expensive to run.

NOT_BOUNDED is correct for the overwhelming majority of reads, a dashboard, a search page, a list view. The other two earn their cost in a narrow case: you just wrote a document and the very next request in the same flow needs to see it, a “create and immediately redirect to the detail page” pattern. If your SDK already hands you the mutation token from the write, AT_PLUS is the cheaper way to guarantee that. Reaching for REQUEST_PLUS everywhere “to be safe” quietly adds latency to every query in the system for a consistency guarantee most of them never needed.

Let the Tools Do the First Pass

ADVISE: A Concrete Recommendation, Not a Guess

Couchbase ships an actual index advisor, not a linter guessing from syntax, it reads the query and the keyspace’s existing indexes and recommends what to build.

1ADVISE SELECT name, price FROM `travel-sample`
2WHERE type = 'hotel' AND city = 'Paris'
3ORDER BY price;

ADVISE returns a concrete CREATE INDEX recommendation, correctly ordered keys included, for that specific query. It’s a starting point, not a replacement for understanding why an index is shaped the way it is, but it’s a fast way to catch an obviously missing index before a query ships. It also only ever looks at one query at a time, so it won’t tell you when three of its recommendations across three different queries should really be one composite index, that consolidation judgment (covered above) is still yours to make.

Underneath, Couchbase’s cost-based optimizer relies on statistics gathered by UPDATE STATISTICS. As of Couchbase Server 7.6, the Query Service gathers these statistics automatically whenever an index is created or built, so the optimizer generally has real cardinality data to plan against rather than guessing. On older clusters, or when you’ve made a large data shape change, running it manually keeps the optimizer’s assumptions from going stale:

1UPDATE STATISTICS FOR `travel-sample` (type, city, price);

INFER: See What’s Actually in the Bucket

Before deciding what to index at all, INFER profiles a keyspace by sampling its documents and returning a JSON Schema-shaped summary of what it actually found, field names, types, and how common each field is.

1INFER `travel-sample` WITH { "sample_size": 1000, "num_sample_values": 3 };

 1{
 2  "properties": {
 3    "city": {
 4      "%docs": 87.5,
 5      "#docs": 875,
 6      "samples": ["Paris", "London", "Tokyo"],
 7      "type": "string"
 8    },
 9    "public_likes": {
10      "%docs": 62.1,
11      "#docs": 621,
12      "minitems": 1,
13      "maxitems": 12,
14      "type": "array"
15    }
16  }
17}

%docs and #docs tell you how common a field actually is, useful for catching the assumption that every document has a field when only two-thirds of them do. minitems/maxitems on an array field gives a rough sense of how big an array index on it will be. sample_size and num_sample_values control how much of the keyspace gets sampled and how many example values come back per field; similarity_metric controls how aggressively INFER merges documents with slightly different shapes into one schema entry instead of reporting them as separate variants. On a keyspace you didn’t design yourself, or one that’s evolved for a few years, this is a faster way to find out what’s really there than reading old code or asking whoever wrote it originally.

One more habit worth adopting from the start: design and iterate on a query and its index against an empty or near-empty keyspace first. EXPLAIN output, key ordering, and covering behavior are all things you can verify structurally before a single document is loaded, and iterating there is fast. Load real data once the shape is right, then re-check the same EXPLAIN output against real cardinality, not before.

Finding the Query That’s Actually Slow

Two system keyspaces cover almost every “why is this slow right now” investigation.

 1-- Currently running queries, useful for catching a runaway query live
 2SELECT * FROM system:active_requests;
 3
 4-- Cancel one by request ID
 5DELETE FROM system:active_requests WHERE requestId = 'abc-123';
 6
 7-- Recently completed queries, filter to anything that took more than a second
 8SELECT statement, elapsedTime, resultCount
 9FROM system:completed_requests
10WHERE elapsedTime > "1s"
11ORDER BY elapsedTime DESC
12LIMIT 20;

That elapsedTime > "1s" comparison is a string comparison against a formatted duration ("250ms", "1.2s", and so on), not a numeric one. It works for the common case of picking a single threshold like “over a second,” but don’t trust it to sort or compare cleanly across mixed units, "500ms" sorts after "2s" as a string even though it’s the faster query. Fine for a threshold filter, worth converting to a numeric duration first if you need to compare or sort a wider range of values.

system:completed_requests is the single best place to start any performance investigation. It’s the actual, historical record of what ran slow, not a guess about what might be slow. Once you’ve pulled the offending statement and either fixed the index or the query, delete the entry so the next investigation isn’t wading through queries you already resolved.

One field makes both of these keyspaces far more useful once more than one service is calling Couchbase: clientContextID. Couchbase never looks at this value itself, it exists purely for your application to set, from the SDK, so a query shows up in system:active_requests and system:completed_requests tagged with whatever request or trace ID your own code already uses.

1SELECT statement, elapsedTime, clientContextID
2FROM system:completed_requests
3WHERE clientContextID LIKE 'checkout-svc:%'
4ORDER BY elapsedTime DESC
5LIMIT 20;

Set it to the same request ID your logs and traces already carry, and a slow-query investigation stops being “reverse-engineer which request this was from the raw statement text” and starts being a direct join against your own application logs.

The Anti-Pattern Checklist

FAQ

Do I need a primary index at all?

Only in development, for ad hoc exploration, or briefly while building out real secondary indexes for a new keyspace. Drop it once the keyspace has proper indexes, or the query planner will occasionally fall back to it for queries that don’t match any secondary index, and you won’t notice until that fallback is scanning millions of documents in production.

How many indexes is too many?

Every index costs write throughput (each mutation updates every index on that document’s fields) and memory. The practical ceiling isn’t a fixed number, it’s whichever comes first: write latency degrading under your actual mutation rate, or index memory pressure. Consolidating narrow, overlapping indexes into fewer wide composite ones (the IntersectScan fix above) is usually how you buy back headroom, not simply deleting indexes until something breaks.

Does ADVISE replace understanding key ordering myself?

No, and treating it as a replacement is where advisor-generated indexes go wrong. ADVISE is excellent at catching “you’re missing an index for this query” fast. It is analyzing one query at a time, so it won’t tell you that three of its recommendations across three queries should really be one composite index. That consolidation judgment is still yours to make.

What’s the difference between INFER and ADVISE?

INFER answers “what does my data actually look like”, field names, types, how common each one is, before you’ve written a query. ADVISE answers “what index does this specific query need”, after you’ve written a query. Reach for INFER when exploring an unfamiliar or evolving keyspace, ADVISE once you already have a slow query in hand.

Is a 100% projection selectivity index ever acceptable?

Yes, when it’s deliberate. An index meant to cover most of a keyspace (a primary-key-style lookup, or a field genuinely present on every document) will legitimately see every mutation. What’s worth catching is the accidental version: a partial index whose WHERE clause is wider than it needs to be, or a composite index that quietly lost its filter during a rewrite. Check the intent behind the number, not just the number itself.

Is WITHIN ever the right choice over IN?

Yes, specifically when you genuinely need to search nested arrays or objects at unknown depth and can’t restructure the data to flatten them. That’s a real but narrow case. Reach for IN by default and only step up to WITHIN when a document’s nesting is truly variable depth.

Why does the same query get a different plan on two different clusters?

Cardinality. The cost-based optimizer’s choices depend on UPDATE STATISTICS output, which reflects the actual data distribution in that specific keyspace. A staging cluster with a thousand documents and a production cluster with fifty million can reasonably produce different plans for the same query and the same indexes, because the actual selectivity of each field is different in each place. Don’t assume a plan verified in staging holds in production without checking EXPLAIN against real production statistics.

Things Worth Their Own Post

• Full-Text Search (FTS) indexes vs GSI: when relevance ranking, fuzzy matching, and language-aware tokenization outgrow what SUFFIXES() and array indexes can reasonably do.
• The cost-based optimizer in depth: how UPDATE STATISTICS sampling actually works, what it stores, and how to read a plan’s cost estimates against real cardinality.
• Vector search indexes: Couchbase’s vector index support for embedding similarity search, and how it composes with GSI on the same bucket.
• Capella-specific tuning: autoscaling behavior, multi-region XDCR replication lag, and what changes about index design when the ops layer is managed for you.