Couchbase Query Analysis Hub

The Every Query tab lets you see stats per individual query to spot bottlenecks and get timings of operations in detail.

• elapsedTime — Total wall-clock from request acknowledgment to last byte sent.
• serviceTime — Active execution time while waiting on services (Index, Data, FTS).
• executionTime — Internal query processing time (engine logic).
• cpuTime — Cumulative CPU consumed across threads (can exceed wall time).
• kernTime — Time spent waiting for CPU scheduling by the OS.
• resultCount — Number of documents returned to the client.
• resultSize — Total bytes sent; affects network transfer time.
• phaseCounts.fetch — Number of full documents retrieved from data service
• phaseCounts.indexScan — Number of matching records from the index(es) service

Watch-outs: large resultSize stretches elapsedTime; high usedMemory suggests heavy sorts/aggregations or big payloads.

Performance is influenced by node CPU, memory, and service placement. Large results also increase network time.

• kernTime — Time spent waiting for CPU scheduling by the OS.
• cpuTime — Cumulative CPU consumed across threads (can exceed wall time).
• memoryUsed — Cumulative Memory used to execute a query

The image above shows the Query service handling multiple concurrent requests on a single node. The OS kernel schedules query threads onto a limited number of CPU cores, so parts of a query must “wait” when resources are contended. This scheduling delay appears as kernTime at the operator level and stretches execution/elapsed time without doing useful work.

• Limited CPU cores → thread contention and context switching under load
• Memory pressure → GC/paging can increase pauses and reduce throughput
• Service co-location (Query with Index/KV) → competition for CPU and memory on the same node
• Large resultSize → longer post-execution network transfer time

Reduce contention by scaling query nodes, tuning concurrency, creating covering indexes to reduce fetches, and minimizing payload size.

The Query service relies on other services in the cluster to operate (Data, Index, FTS, and Auth Service). This statistic is a sum of time spent waiting on all these services for this particular query.

• High kernTime → CPU contention; check system load and concurrency.
• High phaseCounts.fetch → add covering indexes; reduce fetches.
• Large resultSize vs resultCount → optimize projection; narrow scans.
• elapsedTime ≫ serviceTime → queuing/network; scale query nodes/services.
• cpuTime high ~ serviceTime → CPU-bound; simplify expressions or add cores.

Quick patterns: elapsedTime ≫ serviceTime → queuing or network; cpuTime ~ serviceTime → CPU-bound; kernTime high → CPU contention.

See also: Performance Data and ServiceTime Context.

• CRAWL: High phaseCounts.fetch, large resultSize, higher serviceTime
• WALK: Reduced fetch, more selective indexScan, improved serviceTime
• RUN: fetch=0 (covering index), minimal resultSize, optimal serviceTime

A Couchbase index is a data structure that creates shortcuts to your documents, allowing queries to find data efficiently without scanning entire buckets. When you create indexes with CREATE INDEX statements, they are stored and managed by the Index Service.

How Index Creation Works

As shown in the diagram above, when you execute CREATE INDEX statements:

• custId_status_v1: Creates an index on (status, custId) fields, organizing data like "done,1234", "done,5678", "draft,1234"
• email_v1: Creates an index on (email) field, organizing data like "@yahoo.com", "@gmail.com", "@aol.com"
• Index Node Storage: All indexes are stored on dedicated Index Service nodes
• Key-Value Organization: Each index maintains sorted key-value pairs for fast lookups
• Document References: Index values point to document keys (like 1234, 5678) for quick document retrieval

The percentage of your index stored in memory directly impacts query performance. Higher memory residency means faster query execution, while lower memory residency can lead to disk I/O and slower responses.

High Memory Residency (90%+)

Optimal Performance: Most index data is in RAM, resulting in fast lookups and minimal disk I/O. Ideal for frequently accessed indexes.

⚠️ Low Memory Residency (20-40%)

Performance Impact: Frequent disk reads required, leading to higher latency and reduced throughput. Consider increasing memory allocation.

Memory Optimization Tips

• Monitor Memory Residency: Use our tool to track what percentage of your indexes are in memory
• Increase Index RAM: Allocate more memory to the Index Service for frequently used indexes
• Index Selectivity: Create more selective indexes to reduce overall memory footprint
• Partition Indexes: Use partitioned indexes to distribute memory load across nodes
• Archive Old Data: Remove or separate historical data that doesn't need high-performance access

Index Performance Impact

Memory residency directly affects:

• Query Latency: In-memory indexes respond 10-100x faster than disk-based lookups
• Throughput: Higher memory residency allows more concurrent queries without performance degradation
• CPU Usage: Less CPU spent waiting for disk I/O when indexes are in memory
• Consistency: Predictable performance when indexes don't compete for disk resources

While primary indexes in Couchbase provide a basic way to scan all documents in a bucket by their keys, they come with significant drawbacks that make them unsuitable for most production scenarios. Primary indexes lead to very slow performance because they fetch all documents across all types in the bucket before applying filters, resulting in unnecessary I/O, memory, and CPU waste.

• Performance Impact: Excessive document retrievals and post-scan filtering make operations "VERY EXPENSIVE"
• Resource Waste: Unnecessary I/O, memory, and CPU consumption
• Not Recommended: The Couchbase index advisor never recommends primary indexes
• Better Alternatives: Secondary or composite indexes are almost always more efficient

Recommendation: Avoid primary indexes in production—use them only for initial data exploration or when no other index applies, and opt for targeted secondary indexes to minimize latency and resource usage.

Sequential scans in Couchbase involve scanning documents directly without an index, which can be simple but comes with performance limitations. "Sequential scans are intended for simple, ready access to data, and are not intended as a high performance solution."

• Limited Use Cases: Best suited to small collections where key order is unimportant
• Index Overhead: Only when the overhead of maintaining an index can't be justified
• Performance Issues: Lead to full bucket traversals, high latency, and increased resource consumption
• Primary Index Alternative: For ordered document key operations, a primary index provides the same functionality and will outperform a sequential scan

Recommendation: Avoid sequential scans for large datasets or frequent queries—always prioritize indexing for scalability.

Parse (SQL++ text → internal structure) and plan (optimizer choosing execution strategy) phases normally complete in microseconds. When either exceeds 1ms, it indicates CPU contention or kernel scheduling delays—queries waiting for CPU time instead of executing.

Example - Normal times (reference query):

Query: SELECT * , meta() FROM `travel-sample` ORDER BY `type` DESC

phaseTimes: {
  "parse": "271.666µs",    ✅ Normal (< 1ms)
  "plan": "441.75µs"       ✅ Normal (< 1ms)
}

Why times > 1ms are problematic:

• CPU Saturation: Query service node lacks available CPU cores
• Kernel Waits: Threads queued in OS scheduler (see kernTime)
• Co-located Services: Query competing with Index/Data services on same node
• High Concurrency: Too many simultaneous queries

Diagnose via:

• system:vitals → cpu.user.percent (>80%), request.active.count
• Operator stats → high kernTime throughout execution plan
• Node config → check if services are co-located

Fixes: Reduce query concurrency, use prepared statements (skip parse), add dedicated query nodes, separate co-located services, or optimize complex queries.

JOIN operations in Couchbase N1QL/SQL++ can introduce significant performance overhead when not properly optimized. Complex JOINs are flagged based on multiple indicators (A-H) that signal resource contention, inefficient execution patterns, or unexpected data explosion.

Query: SELECT o.*, p.* FROM orders o 
       UNNEST o.items AS item 
       JOIN products p ON KEYS item.productId

Input:  10,000 orders
UNNEST: 50,000 items (avg 5 items per order)
JOIN:   50,000 products matched
Result: 50,000 rows (5x explosion) ⚠️

Flags Triggered:
- D: 5.0x explosion (10,000 → 50,000)
- E: JOIN took 2.80s
- H: JOIN is 32.7% of query time

💡 Recommendation: Monitor JOIN queries with multiple complexity flags (especially A, B, D) as they often indicate systemic design issues. Consider denormalizing frequently-joined data paths or restructuring queries to reduce JOIN overhead.

Concurrent query conflicts occur when Couchbase services (Query, Data, Index) become overloaded, causing resource contention and performance degradation. Unlike peak vs. off-peak analysis, this insight detects service-level pressure through phase timing anomalies, regardless of when queries run.

Query: SELECT o.*, c.* FROM orders o JOIN customers c ON o.customerId = c.id

Metrics:
- Parse+Plan:  5.0ms (Flag A - should be <1ms)
- Fetch:       15ms per doc for 100 docs (Flag D - >10ms/doc)
- Kernel Time: 6,000ms / 15,000ms = 40% (Flag G - >30%)
- Services:    Query, Data, CPU/OS affected (Flag H)

Flags Triggered: A, D, G, H
Severity: 🔴 Critical - System-Wide Pressure

This indicates the entire Couchbase cluster is under heavy load with 
Query Service CPU contention, KV service slow fetches, and OS-level 
CPU scheduling overhead.

💡 Recommendation: Queries with Flag H (System-Wide Pressure) indicate cluster-level resource exhaustion. Immediate action required: scale horizontally, optimize workload, or implement query throttling. Monitor individual service flags (A-G) to identify specific bottlenecks for targeted optimization.