Execution Plans

Execution plans provide detailed insight into how PostgreSQL executes SQL queries. They describe the steps chosen by the query planner, the access methods used, join strategies, ...

Analyzing execution plans is essential for understanding query behavior and identifying performance bottlenecks.

Execution Plan Collection

Datasentinel collects execution plans together with their associated performance metrics on a continuous basis.

To support execution plan monitoring, we have forked and extended an internal module derived from the PostgreSQL extension pg_store_plans.

This custom module (https://github.com/datasentinel/pg_store_plans) enables the collection of execution plans together with their runtime metrics, in a manner comparable to how pg_stat_statements aggregates statistics for SQL queries.

By associating plans with execution metrics, it makes it possible to analyze both how queries are executed and how efficiently they run.

Execution plans and their metrics are collected every minute using one of the following collection methods:

• Local agents, deployed alongside the PostgreSQL instance

• Agentless collection, where the platform connects directly to the database

Configuration

See the installation guide for instructions on installing and configuring the extension at the cluster level.

pg_store_plans

Collected Metrics

For each execution plan, the extension aggregates a comprehensive set of runtime metrics, similar in scope to those provided by pg_stat_statements, but associated directly with execution plans.

These metrics include:

• Execution statistics such as execution count and first/last execution timestamps

• Execution time statistics, including total, minimum, maximum, average, and variability of execution time

• Row processing metrics, capturing the volume of data processed by the plan

• Buffer usage statistics for shared and local buffers, including hits, reads, dirtied, and written blocks

• Temporary file usage, tracking read and written temporary blocks

• I/O timing information for shared, local, and temporary block reads and writes

By summarizing execution frequency, timing behavior, and I/O characteristics, these metrics allow you to:

• Monitor how execution plans evolve over time

• Identify inefficient or unstable execution plans

• Detect performance regressions caused by data growth, configuration changes, or application updates

Visualization and Analysis

All collected execution plans are automatically available in the Datasentinel user interface. The UI makes it easy to:

• Visualize execution plans by query over time

• Correlate execution plans with query performance metrics

• Inspect execution plans in multiple formats, depending on the level of detail required:

  • Text format, similar to the output of PostgreSQL EXPLAIN, for a raw and familiar representation

  • Structured tree view, a custom Datasentinel visualization that displays the plan as a hierarchical table for easier navigation and analysis

This integrated view helps database administrators and developers quickly understand query behavior, identify plan changes, and make informed optimization decisions.