Diagnosing & Fixing High N1QL Latency Caused by Missing or Inefficient Indexes in Couchbase

Recognizable Condition

Clients report that a typically fast N1QL query now returns after 3–5 seconds, even though the bucket contains only a few million documents. The cluster shows high CPU and disk‑I/O spikes during the request, and the latency spike aligns with heavy query traffic.

Root Cause Quick‑Look

Missing or improperly defined indexes force Couchbase to perform a full bucket scan, which is the primary driver of the latency. Inefficient index definitions (e.g., missing covering columns or incorrect sort order) can also lead to scans or sub‑optimal index usage.

Cause / Detection / Fix Table

Cause	Detection	Fix
Query predicate has no matching index	EXPLAIN shows "Full Scan" and no index reference	Create a primary or secondary index that covers the predicate fields
Index exists but missing covering fields	Plan shows "Index Scan" but still high elapsed time	Alter index to include the SELECTed fields (covering index)
Index has wrong sort order for ORDER BY	Plan shows index scan but query still sorts in RAM	Recreate index with correct ASC/DESC order or add an index for the ORDER BY clause
Index status is pending or rebuilding	Index status shows "Pending" or "Rebuilding" in the UI	Wait for completion or restart the build; ensure enough disk space

Ordered Diagnostic Checklist

1. Confirm the symptom
   • Check the 95th‑percentile latency in the cluster dashboard.
   • Verify that the spike coincides with a query burst.
2. Inspect the query plan

   # Run as a user with SELECT privilege
   EXPLAIN SELECT name, age FROM `travel-sample` WHERE type = "hotel" AND city = "Paris";
   

   Look for the Full Scan keyword or a reference to an index. A plan that shows Index Scan but still lists high elapsed time indicates a covering or sort issue.

3. Check index status

   # Via CLI (requires admin privileges)
   cbq -u Administrator -p password -e "SELECT name, state FROM system:indexes WHERE name LIKE '%hotel%'";
   

   Indexes should be Ready. If not, note the rebuild time and monitor disk usage.

4. Review cluster metrics
   • Disk I/O spikes in the bucket statistics.
   • CPU usage above 80% during query bursts.
   • Memory pressure if too many indexes are loaded.
5. Validate with the query profiler

   # Enable profiling for the query
   PROFILE ON;
   SELECT name, age FROM `travel-sample` WHERE type = "hotel" AND city = "Paris";
   PROFILE OFF;
   

   Check the elapsed time, I/O, and CPU columns in the profiler output.

Fixes Tied to Findings

1. Create a Missing Index

# Example index covering the predicate and SELECT fields
CREATE INDEX ix_hotel_city ON `travel-sample`(city) USING GSI WHERE type = "hotel";

After creation, wait for the index to reach Ready status. Re‑run the EXPLAIN to confirm the plan now references ix_hotel_city.

2. Add Covering Columns

# Add name and age to the index
ALTER INDEX ix_hotel_city ADD COVERING FIELD name, age;

Covering indexes eliminate the need for a secondary lookup, reducing latency and I/O.

3. Correct Sort Order

# Recreate index with descending order for age
CREATE INDEX ix_hotel_city_age_desc ON `travel-sample`(city, age DESC) USING GSI WHERE type = "hotel";

When the query contains ORDER BY age DESC, this index allows the engine to return sorted rows directly.

4. Rebuild a Stalled Index

# Rebuild only the affected index
ALTER INDEX ix_hotel_city REBUILD;

Rebuilding during off‑peak hours avoids a temporary spike in cluster load.

Verification Steps

• Run EXPLAIN again to ensure the new index is used.
• Check the profiler for reduced elapsed time and lower I/O.
• Observe the cluster dashboard for a drop in CPU and disk usage during query bursts.
• Confirm that the 95th‑percentile latency returns to the expected 100–200 ms range.

Escalation Criteria

If, after applying the above fixes, latency remains above 1 second, consider:

• Reviewing the overall index strategy—too many indexes can degrade write performance.
• Checking for hardware bottlenecks (disk I/O, network latency).
• Examining the query itself for complex joins or sub‑queries that may benefit from a different data model.
• Engaging Couchbase support with detailed query profiler logs and cluster metrics.

Limitations & Practical Tips

• Index builds consume disk space; monitor disk usage in the UI to avoid out‑of‑space errors.
• Large indexes can take hours to build on a 10 GB dataset; schedule during maintenance windows.
• Index updates trigger cache invalidation; ensure sufficient RAM to keep hot data in memory.
• Avoid over‑indexing: each index increases write latency and memory usage.

By following this ordered diagnostic flow, you can quickly pinpoint whether missing or inefficient indexes are the root cause of high N1QL latency and apply the precise fix needed to restore performance.