# Execution Plans

{% hint style="info" %}
Analyzing execution plans is essential for understanding query behavior and identifying performance bottlenecks.
{% endhint %}

## 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**](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**](https://docs.datasentinel.io/manual/getting-started/installation/agent), deployed alongside the PostgreSQL instance
* [**Agentless collection**](https://docs.datasentinel.io/manual/features/other-features/agentless-monitoring), 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.

{% content-ref url="../../getting-started/postgresql-clusters/extensions/pg\_store\_plans" %}
[pg\_store\_plans](https://docs.datasentinel.io/manual/getting-started/postgresql-clusters/extensions/pg_store_plans)
{% endcontent-ref %}

## 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.

{% @arcade/embed flowId="UKahLWSZwrYLpOYUvkDG" url="<https://app.arcade.software/share/UKahLWSZwrYLpOYUvkDG>" %}