Ahmed Gouda: Monitoring Query Plans with Pgwatch and Pg_stat_plans

Why It Matters

Plan‑level visibility lets teams pinpoint inefficient execution paths and verify the impact of schema changes, directly improving PostgreSQL performance and reliability.

Key Takeaways

• pg_stat_plans tracks plan-level statistics, not just statements
• pgwatch can query custom metrics via YAML config
• Grafana dashboards visualize plan execution time and calls
• Detect plan changes after index creation
• No need to expose raw SQL text to developers

Pulse Analysis

PostgreSQL administrators have long relied on pg_stat_statements to surface the most costly queries, but that view stops at the statement level. The newly released pg_stat_plans extension fills the gap by aggregating execution metrics for each distinct query plan, exposing plan text, call counts, and total execution time. This granularity is crucial for environments where the planner may switch between nested‑loop, hash, or merge joins, and where subtle plan changes can dramatically affect latency.

Integrating pg_stat_plans with pgwatch is straightforward. By adding a custom metric definition in a YAML file—specifying the extension’s initialization SQL and the complex CTE that joins pg_stat_plans to pg_stat_statements—pgwatch can poll the database at configurable intervals (e.g., every three minutes). The collected rows are written to a sink database, where they become available for downstream analysis. This extensible approach means any SQL‑accessible metric can be incorporated, turning pgwatch into a universal telemetry hub for PostgreSQL clusters.

Once the data resides in the sink, Grafana can render it through a dedicated dashboard panel. Users can filter by database, query ID, or time range to compare plan execution times, call frequencies, and resource consumption across plan versions. The visual feedback makes it easy to verify that new indexes or configuration tweaks trigger the intended plan, and to spot regressions before they impact production workloads. In short, the pg_stat_plans + pgwatch combo equips teams with actionable, plan‑level insights that drive performance tuning and operational confidence.