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	https://github.com/datasentinel/pg_store_plans/archive/refs/tags/2.0.tar.gz

Unpack the source code

tar xvzf 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.verbose=false
pg_store_plans.save=false

# Exclude simple INSERT statements (INSERT ... VALUES); INSERT ... SELECT remains collected
pg_store_plans.exclude_simple_inserts=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 row)

or

\dx pg_store_plans
                                          List of installed extensions
      Name      | Version | Schema |                                 Description                                 
----------------+---------+--------+-----------------------------------------------------------------------------
 pg_store_plans | 2.0     | 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.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
pg_store_plans.exclude_simple_inserts | on

Check Execution Plans

Connected as datasentinel user and in postgres database

SELECT dbid, count(*) from pg_store_plans group by dbid;