🔌pg_store_plans

The PostgreSQL extension pg_store_plans is a contrib module that provides a way to store execution plans generated by the query planner.

The open-source project is accessible on GitHub: https://github.com/ossc-db/pg_store_plans

PG_STORE_PLANS store execution plans like PG_STAT_STATEMENTS does for queries.

About

This extension is OPTIONAL. Datasentinel will automatically consider it if it is installed

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.

See https://ossc-db.github.io/pg_store_plans/ for more details.

Use this extension with caution in your high-load production clusters as it could lead to an increased burden.

To mitigate this, consider setting the min_duration parameter to a value greater than 0.

Installation

To be installed in the internal database named postgres

Download Source Files

PostgreSQL version	pg_store_plans version	Download file url
< 10	1.3.1	https://github.com/ossc-db/pg_store_plans/archive/1.3.1.tar.gz
>= 10 and < 13	1.4	https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.4.tar.gz
13	1.5	https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.5.tar.gz
14	1.6.1	https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.6.1.tar.gz
15	1.7	https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.7.tar.gz
16	1.8	https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.8.tar.gz
17	1.9	https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.9.tar.gz

Compilation

Ensure PostgreSQL development package is installed

On RHEL/CentOS-based systems:

dnf install postgresql<<VERSION>>-devel

Set the PATH variable to your PostgreSQL installation directory

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

The pg_config command should be OK

// Compile and deploy
tar xvzf <<VERSION>>.tar.gz
cd pg_store_plans-<<VERSION>>
make USE_PGXS=1
sudo make install USE_PGXS=1

Modify postgresql.conf

Add pg_store_plans to shared_preload_libraries parameter

Add the following lines

pg_store_plans.log_analyze = false
pg_store_plans.log_timing = false
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 in your high-load production clusters
pg_store_plans.min_duration=0

pg_store_plans.log_buffers=false
pg_store_plans.log_triggers=false
pg_store_plans.verbose=false
pg_store_plans.save=false

Restart PostgreSQL

Create The Extension

• connect to the internal database named postgres with superuser privileges

CREATE EXTENSION pg_store_plans;

Check The Extension

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

Check Parameters

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

Check Execution Plans

Connected as datasentinel user and in postgres database

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