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/datasentinel/pg_store_plans

PG_STORE_PLANS store execution plans like PG_STAT_STATEMENTS does for queries.

This project is a fork of https://github.com/ossc-db/pg_store_plans

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.

Installation

To be installed in the internal database named postgres

Set the PATH variable to your PostgreSQL installation directory

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

Ensure PostgreSQL development package is installed

On RHEL based systems:

dnf install postgresql<<VERSION>>-devel

PostgreSQL provides the pg_config utility to allow to verify that build settings are available and correctly resolved.

pg_config

Download Source Files

PostgreSQL version	pg_store_plans version	Download file url
14, 15, 16, 17, 18	2.0.1	https://github.com/datasentinel/pg_store_plans/archive/refs/tags/2.0.1.tar.gz

Unpack the source code

tar xvzf pg_store_plans-2.0.tar.gz
cd pg_store_plans-2.0

Build and install the extension

make
sudo make install

For Versions less than 14

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

Unpack the source code

tar xvzf <<VERSION>>.tar.gz
cd pg_store_plans

Build and install the extension

make USE_PGXS=1
sudo make install USE_PGXS=1

Modify postgresql.conf

shared_preload_libraries

Add pg_store_plans to shared_preload_libraries parameter (requires a server restart)

Add the following lines

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 if you don't want to collect all plans
pg_store_plans.min_duration=0

pg_store_plans.log_analyze = false
pg_store_plans.log_timing = false
pg_store_plans.log_buffers=false
pg_store_plans.log_triggers=false
pg_store_plans.log_verbose=false
pg_store_plans.save=false

# Exclude simple INSERT statements (INSERT ... VALUES); INSERT ... SELECT remains collected
pg_store_plans.exclude_simple_inserts=on

# Anonymize
 pg_store_plans.anonymize=on

Restart PostgreSQL

Create The Extension

• connect to the internal database named postgres with superuser privileges

CREATE EXTENSION pg_store_plans;

Upgrading from Versions < 2.0

When upgrading from a version earlier than 2.0, the extension must be dropped and recreated.

Version 2.0 introduces a change in how planid values are computed, meaning the same execution plan will produce a different identifier than in previous versions.

Check The Extension

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

or

\dx pg_store_plans
                                          List of installed extensions
      Name      | Version | Schema |                                 Description                                 
----------------+---------+--------+-----------------------------------------------------------------------------
 pg_store_plans | 2.0.1   | public | Datasentinel SQL execution plan tracking extension (fork of pg_store_plans)
(1 row)

Check Parameters

SELECT name, setting from pg_settings where name like 'pg_store_plans%';
                 name                  | setting 
---------------------------------------+---------
 pg_store_plans.anonymize              | on
 pg_store_plans.exclude_simple_inserts | off
 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.max_plan_length        | 5000
 pg_store_plans.min_duration           | 0
 pg_store_plans.plan_format            | text
 pg_store_plans.plan_storage           | file
 pg_store_plans.save                   | on
 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;