Datasentinel Docs
Visit our websiteBlogRelease Notes
  • 👋Welcome
  • 🆓Free Trial
  • 📣Release Notes
  • Getting started
    • Architecture
    • Installation
      • 🌐Platform
      • 🕶️Agent
    • PostgreSQL clusters
      • 🔌Extensions
        • 🔌pg_stat_statements
        • 🔌pg_store_plans
        • 🔌system_stats
        • 🔌pg_buffercache
      • 🕶️Monitoring User
      • ➕Adding Connection
    • FAQs
      • ❓Platform FAQ
      • ❓Agent FAQ
  • Features
    • Key Features
      • 📊Session History
      • 📊Top Queries
      • 📂Top Tables
      • 🔓Lock Explorer
      • 📈Cluster & System Metrics
      • ⌚Live360
      • 📰Reporting
      • 🔔Alerting
        • Settings
        • Templates
        • Silences
        • Manager
    • Other Features
      • 📡Agentless Monitoring
      • 🛡️Role Based Access
      • 🔂Changed Parameters
    • Tips & Hints
      • 🖥️User Interface
      • 🏷️Tags
      • 🔀Metric Correlation
      • 👁️‍🗨️Consolidated View
      • ❗Graphical Annotations
      • ☁️Predefined Providers
      • ❓Wait Event Description
      • ®️Read Replicas
      • 👁️‍🗨️Agentless & System Metrics
      • ☑️Simplified pg_instance Display
  • implementation
    • Platform Usage
      • ⚙️Configuration
        • 🔑License
        • 📓LDAP
        • ✉️SMTP
        • 📋Audit
        • 👨‍🏭Users & Roles
      • 🧩API
        • 🔗Access Token
        • 🧩Connection API
        • 🧩Role API
        • 🧩User API
        • 🧩Reporting API
        • 🧩Workload API
        • 🧩Alerting API
      • 🛠️Tooling
    • Agent Usage
      • 📣Release Notes
      • ⌨️CLI
      • 🧩API
      • 🗃️Collection Level
      • 🔬Internals
    • Upgrade
      • 🔄Platform
      • 🔄Agent
    • Troubleshooting
      • 🩺Error message: “502 Bad Gateway”
      • 🩺The UI is not displaying any metrics for my new instance.
      • 🩺UI dashboard is encountering loading errors
      • 🩺InfluxDB
  • Support
    • How to Contact Us
  • GitHub Toolkit
Powered by GitBook
On this page
  • About
  • Installation
  • Download Source Files
  • Compilation
  • Modify postgresql.conf
  • Restart PostgreSQL
  • Create The Extension
  • Check The Extension
  • Check Parameters
  • Check Execution Plans
  1. Getting started
  2. PostgreSQL clusters
  3. Extensions

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.

Previouspg_stat_statementsNextsystem_stats

Last updated 2 days ago

The open-source project is accessible on GitHub:

PG_STORE_PLANS store execution plans like PG_STAT_STATEMENTS does for queries.

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.

See for more details.

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.

Installation

To be installed in the internal database named postgres

Download Source Files

PostgreSQL version
pg_store_plans version
Download file url

< 10

1.3.1

>= 10 and < 13

1.4

13

1.5

14

1.6.1

15

1.7

16

1.8

17

1.9

Compilation

Ensure PostgreSQL development package is installed

On RHEL/CentOS-based systems:

dnf install postgresql<<VERSION>>-devel

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

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
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

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
pg_store_plans.save         | 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;

🔌
🔌
https://github.com/ossc-db/pg_store_plans
https://ossc-db.github.io/pg_store_plans/
https://github.com/ossc-db/pg_store_plans/archive/1.3.1.tar.gz
https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.4.tar.gz
https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.5.tar.gz
https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.6.1.tar.gz
https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.7.tar.gz
https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.8.tar.gz
https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.9.tar.gz