How do you tell whether a Couchbase GSI fully covers a SQL++ query?

## What covering means A Couchbase SQL++ query normally executes in two steps. The Index service scans a global secondary index and returns the qualifying document keys; the Query service then **fetches** those documents from the Data service to evaluate any remaining predicates and build the projection. A *covering* index eliminates the second step: if the index keys already contain every field the statement mentions, the answer can be assembled from index entries alone. The fields that must be present are all of them — not just the ones in `WHERE`. Projected fields, `ORDER BY` fields and `GROUP BY` fields all count. `META().id` is available from the index entry itself, so projecting the document key does not break covering. ## Reading the plan `EXPLAIN` in front of the statement returns the plan without running it. Two markers matter: - an `IndexScan` operator that lists a `covers` array — the expressions the scan can supply directly; - the **absence** of a `Fetch` operator. A plan that shows `IndexScan` followed by `Fetch` is using the index for the predicate but still going to the Data service for the projection. A plan showing `PrimaryScan` means no secondary index was selected at all, which is a bigger problem than not covering. ## The before/after shape ```sql -- not covered: country is not an index key, so every key is fetched CREATE INDEX idx_name ON `app`.inv.airline(name); SELECT name, country FROM `app`.inv.airline WHERE name LIKE "A%"; -- covered: both referenced fields are index keys CREATE INDEX idx_name_country ON `app`.inv.airline(name, country); ``` After the second index exists, the same statement plans without a `Fetch`. Note the field *order* still matters for eligibility even though covering does not care about order: `name` must lead because that is what the predicate constrains. ## Eligibility comes first Covering is irrelevant if the index is not chosen. A GSI is only considered when the query constrains its **leading** key. A classic symptom: an index exists on `orders(created_at)` and a query does `SELECT ... ORDER BY created_at LIMIT 10` with no `WHERE` clause — the planner will not use the index, because nothing constrains the leading key, and the statement falls back to a primary scan or fails outright. The idiomatic fix is to add `WHERE created_at IS NOT MISSING`, which constrains the leading key without changing the intended result set, making the index eligible and the ordered scan cheap. ## The MISSING semantics you must not gloss over A secondary GSI contains an entry only for documents where the leading index key is present. Documents that lack the field are simply absent from the index. That is not a bug; it is what makes partial-shaped document collections indexable cheaply. But it means a query served by that index can return a smaller set than the logically equivalent query served by a primary index. In a schema-flexible collection where some documents genuinely lack the field, this is a correctness question, not a performance one — decide deliberately whether those documents should be in the answer, and if they must be, index an expression that is always present or add the field on write. ## Costs of chasing covering Every extra field you add to an index key widens the index entry and increases index-service memory and disk, and every index must be maintained on every mutation of a covered field. Adding four projection fields to a hot index to make it covering is often the right call; adding them to five indexes to cover five variant queries is usually the wrong one. The disciplined approach is: identify the few statements that dominate the workload, cover those, and let the rest fetch. `ADVISE` will suggest covering indexes per statement, so the consolidation judgment stays with you. ## What interviewers listen for They want to hear that you verify with `EXPLAIN` rather than assert; that you know the distinction between *eligible* (leading key constrained) and *covering* (all referenced fields present); that you can name the `Fetch` operator as the thing whose absence proves covering; and that you can articulate the write-side cost so covering does not become a reflex applied to every index in the cluster.