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.
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
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
sudo make install USE_PGXS=1
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
- connect to the internal database named postgres with superuser privileges
CREATE EXTENSION pg_store_plans;
SELECT current_database(), extname, extversion from pg_extension where extname ='pg_store_plans';
current_database | extname | extversion
postgres | pg_store_plans | 1.6
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
Connected as datasentinel user and in postgres database
SELECT dbid, count(*) from pg_store_plans group by dbid;