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.


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 http://pgstoreplans.osdn.jp/pg_store_plans.html 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.


To be installed in the internal database named postgres

Download Source Files


Ensure you have set the PATH variable to your PostgreSQL installation directory


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
# Consider setting this parameter to a value greater than 0 in your high-load production clusters


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;

Last updated