# pg\_store\_plans

The open-source project is accessible on GitHub:  <https://github.com/datasentinel/pg_store_plans>

**PG\_STORE\_PLANS** store execution plans like **PG\_STAT\_STATEMENTS** does for queries.

{% hint style="info" %}
This project is a fork of <https://github.com/ossc-db/pg_store_plans>
{% endhint %}

## **About**

{% hint style="info" %}

## This extension is **OPTIONAL.**

Datasentinel will automatically consider it if it is installed
{% endhint %}

Once the settings are configured, there is no further action required. \
Resetting **pg\_store\_plans** is unnecessary.

Datasentinel collects execution plan activity metrics for all queries at regular intervals to enable detailed analyzes on the GUI.

Upon installation, you gain access to:

* Historical statistics for each query ID's plan.
* Execution plans.

## **Installation**

{% hint style="warning" %}
To be installed in the internal database named **postgres**
{% endhint %}

Set the PATH variable to your PostgreSQL installation directory

```bash
export PATH=<<POSTGRESQL_DIRECTORY>>/bin:$PATH
```

{% hint style="info" %}
**Ensure PostgreSQL development package is installed**

On RHEL based systems:

```bash
dnf install postgresql<<VERSION>>-devel
```

{% endhint %}

PostgreSQL provides the `pg_config` utility to allow to verify that build settings are available and correctly resolved.

```bash
pg_config
```

### Download Source Files

<table data-full-width="true"><thead><tr><th width="190.1483154296875">PostgreSQL version</th><th width="240.33333333333331">pg_store_plans version</th><th>Download file url</th></tr></thead><tbody><tr><td>14, 15, 16, 17, 18</td><td>2.0</td><td><a href="https://github.com/datasentinel/pg_store_plans/archive/refs/tags/2.0.tar.gz">https://github.com/datasentinel/pg_store_plans/archive/refs/tags/2.0.tar.gz</a></td></tr></tbody></table>

Unpack the source code

```bash
tar xvzf pg_store_plans-2.0.tar.gz
cd pg_store_plans-2.0
```

Build and install the extension

```bash
make
sudo make install
```

<details>

<summary>For Versions less than 14</summary>

<table data-full-width="true"><thead><tr><th width="156">PostgreSQL version</th><th width="240.33333333333331">pg_store_plans version</th><th>Download file url</th></tr></thead><tbody><tr><td>&#x3C; 10</td><td>1.3.1</td><td><a href="https://github.com/ossc-db/pg_store_plans/archive/1.3.1.tar.gz">https://github.com/ossc-db/pg_store_plans/archive/1.3.1.tar.gz</a></td></tr><tr><td>>= 10 and &#x3C; 13</td><td>1.4</td><td><a href="https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.4.tar.gz">https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.4.tar.gz</a></td></tr><tr><td>13</td><td>1.5</td><td><a href="https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.4.tar.gz">https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.5.tar.gz</a></td></tr></tbody></table>

Unpack the source code

```bash
tar xvzf <<VERSION>>.tar.gz
cd pg_store_plans
```

Build and install the extension

```bash
make USE_PGXS=1
sudo make install USE_PGXS=1
```

</details>

### Modify `postgresql.conf`

{% hint style="info" %}

## shared\_preload\_libraries

Add **pg\_store\_plans** to **shared\_preload\_libraries** parameter (requires a server restart)
{% endhint %}

Add the following lines

```apache

pg_store_plans.max=1000
pg_store_plans.track=all
pg_store_plans.plan_format=text
#
# Consider setting this parameter to a value greater 
# than 0 if you don't want to collect all plans
pg_store_plans.min_duration=0

pg_store_plans.log_analyze = false
pg_store_plans.log_timing = false
pg_store_plans.log_buffers=false
pg_store_plans.log_triggers=false
pg_store_plans.log_verbose=false
pg_store_plans.save=false

# Exclude simple INSERT statements (INSERT ... VALUES); INSERT ... SELECT remains collected
pg_store_plans.exclude_simple_inserts=on
```

### Restart PostgreSQL

### Create The Extension

* connect to the internal database named **postgres** with superuser privileges

```sql
CREATE EXTENSION pg_store_plans;
```

{% hint style="info" %}

#### Upgrading from Versions < 2.0

When upgrading from a version **earlier than 2.0**, the extension must be **dropped and recreated**.

Version 2.0 introduces a change in how `planid` values are computed, meaning the same execution plan will produce a different identifier than in previous versions.
{% endhint %}

### Check The Extension

```sql
 SELECT current_database(), extname, extversion from pg_extension where extname ='pg_store_plans';
 current_database |    extname     | extversion
 ------------------+----------------+------------
 postgres         | pg_store_plans | 2.0
(1 row)

```

**or**

```sql
\dx pg_store_plans
                                          List of installed extensions
      Name      | Version | Schema |                                 Description                                 
----------------+---------+--------+-----------------------------------------------------------------------------
 pg_store_plans | 2.0     | public | Datasentinel SQL execution plan tracking extension (fork of pg_store_plans)
(1 row)
```

### Check Parameters

```sql
SELECT name, setting from pg_settings where name like 'pg_store_plans%';
            name             | setting
-----------------------------+---------
pg_store_plans.log_analyze  | off
pg_store_plans.log_buffers  | off
pg_store_plans.log_timing   | off
pg_store_plans.log_triggers | off
pg_store_plans.log_verbose  | off
pg_store_plans.max          | 1000
pg_store_plans.min_duration | 0
pg_store_plans.plan_format  | text
pg_store_plans.save         | off
pg_store_plans.track        | all
pg_store_plans.exclude_simple_inserts | on
```

### Check Execution Plans

> Connected as **datasentinel** user and in **postgres** database

```sql
SELECT dbid, count(*) from pg_store_plans group by dbid;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.datasentinel.io/manual/getting-started/postgresql-clusters/extensions/pg_store_plans.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.