pg_stat_statements: Identifying true YSQL query bottlenecks before tuning

Short answer

Yes, you can use pg_stat_statements to find your true YSQL bottlenecks, but only if you account for the fact that the view is strictly per-node. The practical workflow is: enable the extension, scrape and aggregate it from every tserver grouped by queryid, then validate the top offenders with EXPLAIN (ANALYZE, DIST) to separate local CPU cost from remote storage wait time. Only then should you touch indexes or flags.

Aggregating across nodes

Each tserver maintains its own copy of pg_stat_statements, and the data vanishes on restart. There is no built-in cluster-wide view, so you aggregate externally:

1. Connect to each YSQL endpoint (or have your monitoring stack scrape each one) and run the same query, for example: SELECT queryid, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements;
2. Combine the results with a UNION ALL (or in your metrics pipeline) and group by queryid, summing calls and total_exec_time. The query text may differ slightly per node if literals are not normalized, so group by queryid, not text.
3. Weight by node load. If your load balancer routes unevenly, a query that looks cheap in aggregate may be hot on one node. Keep per-node rows in your snapshot so you can spot skew.

Because restarts wipe the stats, export a snapshot before any rolling restart, and take before/after snapshots around a diagnostic window so you can compute deltas rather than relying on lifetime totals.

Continuous vs. diagnostic-window use

The overhead is real but bounded. Cost scales with pg_stat_statements.max (the number of tracked statements) and is typically a low single-digit percentage of CPU; the extension also consumes shared memory per node, and evicts the oldest entries when the cap is hit. For most production clusters, leaving it on continuously is acceptable and is the only way to catch intermittent regressions. The case for enabling it only during diagnostic windows is a latency-sensitive cluster where even small CPU jitter matters, or where you cannot afford the shared memory footprint. Note that changing shared_preload_libraries or pg_stat_statements.max requires a rolling tserver restart, so "toggling" is not free — plan for that operational cost when choosing a strategy.

Correlating with EXPLAIN (ANALYZE, DIST)

The cumulative time in pg_stat_statements is wall-clock execution time: it includes local CPU work and time spent waiting on remote DocDB reads, RPCs, and network round trips. EXPLAIN (ANALYZE, DIST) breaks out the distributed side — remote read requests, rows read from storage, and associated wait time. The decision logic is:

• Take a top offender by total_exec_time, run it with EXPLAIN (ANALYZE, DIST), and compare the storage-read wait portion against the mean time from the stats view.
• If remote-read latency dominates, the query is storage-bound: look at index coverage, selectivity, tablet distribution, and whether the scan reads far more rows than it returns.
• If remote reads are small but total time is high, the cost is local — sort, aggregation, join strategy, or row volume — and indexes/flags aimed at storage access will not help.

One caveat: a single EXPLAIN ANALYZE run is one sample under current cache and load conditions. If the pg_stat_statements mean and the explained run disagree wildly, run it a few times before concluding anything.

Verification steps

After the rolling restart, confirm the view populates on every node with SELECT count(*) FROM pg_stat_statements;. Run a representative workload, snapshot before and after, and check that the CPU delta is within your expected bound. Finally, pick one known query and confirm its queryid total time roughly matches the wall time you observe in EXPLAIN (ANALYZE, DIST) — that sanity check validates the whole pipeline before you act on it.

Version note: column names (e.g., total_exec_time vs. total_time) vary by the PostgreSQL version your YugabyteDB release tracks; verify against your version's view definition.