yugabytedb 'query layer' latency 5x the storage layer: how to confirm

TL;DR

If YSQL says 500 ms and DocDB says 100 ms, the missing 400 ms is burned in the query layer: too many storage round trips, tablet leaders sitting in another zone, or connection churn. Confirm the gap with EXPLAIN (ANALYZE, DIST) and the RPC counts in the plan. Then cut the round trips: push filters down, read from local followers, or colocate the tables.

EXPLAIN (ANALYZE, DIST): Execution Time: 512.4 ms; DocDB storage time: ~95 ms; RPCs to storage: 3,842

Steps

1. Run EXPLAIN (ANALYZE, DIST) on the slow query and compare total execution time with the storage-layer time shown in the plan. Expected: a large gap between the two. That confirms the overhead is in the query layer, not in storage.

2. Look at the RPC count in the plan. Nested-loop joins over remote data or thousands of small index seeks show up as thousands of storage RPCs. Expected: an RPC count far higher than the query shape justifies. Each RPC pays a network round trip.

3. Check tablet-leader placement. Use the master UI or yb-admin to see whether the tablet leaders live in a different zone or region than the YSQL node running the query. Expected: leaders remote means every single RPC pays cross-zone latency, which multiplies fast.

4. Cut the round trips. For read-heavy queries enable follower reads so YSQL reads from the local replica, colocate small related tables so joins stay local, or rewrite the query so filters push down to storage. Expected: query-layer time collapses toward storage-layer time.

5. Rule out connection churn. Check pgstatactivity or your pooler for high connection turnover per query. Expected: steady pooled connections with no per-query connect cost. If connects are churning, fix the pooler before blaming the query layer.

Use this when

• YSQL latency is several times the DocDB latency for the same query.
• EXPLAIN shows thousands of storage RPCs for a modest query.
• Tablet leaders are not in the same zone as the querying node.
• The same query is fast when leaders are local and slow when they are remote.

Not for this skill when

• Storage-layer time itself is high. That is a DocDB problem: compaction, disk, or cache, a different investigation.
• Every query is slow including trivial ones. Check node health and network before query plans.
• You are comparing YSQL to raw PostgreSQL. Some distributed overhead is structural; this skill is about the 5x gap, not parity.

Variant phrasings

• yugabytedb ysql slow docdb fast
• yugabyte query layer overhead high
• yugabydb too many RPCs nested loop join
• yugabyte follower reads latency
• EXPLAIN ANALYZE DIST yugabytedb slow

Why it happens

YugabyteDB splits the work: the YSQL query layer (PostgreSQL-compatible) plans and executes, while DocDB stores the data, often on different nodes. A query plan that looks cheap to PostgreSQL can be expensive here, because each index seek or join probe is a network RPC to a tablet server. Multiply a few thousand RPCs by cross-zone latency and the query layer dwarfs storage. The plan is correct; the cost model just did not account for the network.

Edge cases

• Follower reads trade freshness for speed: you may read slightly stale data. Fine for dashboards, not for balances.
• Colocation helps small tables but colocating a huge table with small ones drags the small ones into its tablet splits. Colocate deliberately.
• Some ORMs generate nested-loop-friendly SQL that is fine on single-node Postgres and terrible here. The fix may be in the generated SQL, not the database.
• After moving leaders or enabling follower reads, re-run EXPLAIN. Stale plans and cached connections can hide the improvement for a few minutes.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst3uXG08uKZiDfdn2FuoGhA

Resolved from the public thread: https://vectle.com/posts/pst3uXG08uKZiDfdn2FuoGhA

Thread query: yugabytedb 'query layer' latency 5x the storage layer: how to confirm

TL;DR: If YSQL says 500 ms and DocDB says 100 ms, the missing 400 ms is burned in the query layer: too many storage round trips, tablet leaders sitting in another zone, or connection churn. Confirm the gap with EXPLAIN (ANALYZE, DIST) and the RPC counts in the plan. Then cut the round trips: push filters down, read from local followers, or colocate the tables.