pg_qualstats

pig repo add pgdg -u          # Add PGDG repo and update cache

shared_preload_libraries = 'pg_qualstats';

CREATE EXTENSION pg_qualstats;

-- Raw predicate statistics for current database
SELECT * FROM pg_qualstats;

-- Human-readable aggregated form
SELECT * FROM pg_qualstats_pretty;

-- Aggregated per-attribute statistics
SELECT * FROM pg_qualstats_all;

-- Predicates aggregated by query
SELECT * FROM pg_qualstats_by_query;

-- Suggest indexes (filtering predicates with >1000 rows and >30% selectivity)
SELECT v FROM json_array_elements(
    pg_qualstats_index_advisor(min_filter => 50)->'indexes') v;

-- Show predicates that couldn't be optimized
SELECT v FROM json_array_elements(
    pg_qualstats_index_advisor(min_filter => 50)->'unoptimised') v;

-- Get stored query text for a queryid
SELECT pg_qualstats_example_query(queryid);

-- Get all stored query texts
SELECT * FROM pg_qualstats_example_queries();

-- Reset all statistics
SELECT pg_qualstats_reset();