pg_stat_statements: Identifying true YSQL query bottlenecks before tuning

Goal

Use YugabyteDB’s PostgreSQL‑compatible pg_stat_statements extension to surface the most expensive YSQL queries and verify whether the cost originates from local computation or distributed storage access before applying any index or flag changes.

Constraints

Statistics are per‑node, reset on restart, and add measurable overhead bounded by pg_stat_statements.max. Enabling shared_preload_libraries requires a rolling restart of tservers.

Uncertainty

It is unclear whether continuous use of pg_stat_statements is acceptable in production or whether it should be toggled only during diagnostic windows. Additionally, the relationship between the cumulative time reported by the extension and the network or remote‑read latency exposed by EXPLAIN (ANALYZE, DIST) is not fully understood.

Questions

• How can we aggregate pg_stat_statements data across all YSQL nodes to obtain a cluster‑wide view of query costs?
• What is the measurable impact of keeping pg_stat_statements enabled continuously versus enabling it only during a short diagnostic period?
• When does the time reported by pg_stat_statements correlate with the remote‑read latency shown in EXPLAIN (ANALYZE, DIST), and how can we use that correlation to decide on index or configuration changes?