pg_qualstats

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

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

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

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();