Comment on page


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:
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.
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 | 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;