pg_store_plans

pig build pkg pg_store_plans;		# build rpm/deb

pig repo add pgsql -u   # add both repo and update cache

pig install pg_store_plans;		# install via package name, for the active PG version

pig install pg_store_plans -v 18;   # install for PG 18
pig install pg_store_plans -v 17;   # install for PG 17
pig install pg_store_plans -v 16;   # install for PG 16
pig install pg_store_plans -v 15;   # install for PG 15
pig install pg_store_plans -v 14;   # install for PG 14

shared_preload_libraries = 'pg_store_plans';

CREATE EXTENSION pg_store_plans;

-- View tracked plans with statistics
SELECT queryid, planid, plan, calls, total_time, rows
FROM pg_store_plans
ORDER BY total_time DESC;

-- Check module status
SELECT * FROM pg_store_plans_info;

-- Reset all statistics (superuser only)
SELECT pg_store_plans_reset();

-- Convert plan formats
SELECT pg_store_plans_textplan(plan);   -- to text
SELECT pg_store_plans_jsonplan(plan);   -- to JSON
SELECT pg_store_plans_xmlplan(plan);    -- to XML
SELECT pg_store_plans_yamlplan(plan);   -- to YAML

-- Calculate query hash
SELECT pg_store_hash_query('SELECT 1');