Comment on page
🔌
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.
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
PostgreSQL version | pg_store_plans version | Download file url |
< 10 | 1.3.1 | |
>= 10 and < 14 | 1.4 | |
14 | 1.6.1 | |
15 | 1.7 |
Ensure you have 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
Add
pg_store_plans to shared_preload_libraries parameterAdd 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
- 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
(1 row)
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;
Last modified 2mo ago