pg_track_optimizer

pig build pkg pg_track_optimizer;		# build rpm/deb

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

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

pig install pg_track_optimizer -v 18;   # install for PG 18
pig install pg_track_optimizer -v 17;   # install for PG 17

shared_preload_libraries = 'pg_track_optimizer';

CREATE EXTENSION pg_track_optimizer;

-- Track only problematic queries in production
SET pg_track_optimizer.mode = 'normal';

-- Track all queries during debugging
SET pg_track_optimizer.mode = 'forced';

-- Log EXPLAIN for queries exceeding error threshold
SET pg_track_optimizer.log_min_error = 2.0;

SELECT queryid, query,
       avg_avg, avg_min, avg_max,
       rms_avg, rms_min, rms_max,
       time_avg, blks_avg, nexecs
FROM pg_track_optimizer
ORDER BY avg_avg DESC
LIMIT 10;

-- Using the RStats type directly
SELECT queryid, query,
       wca_error -> 'mean' AS avg_wca_error,
       blks_accessed -> 'mean' AS avg_blocks
FROM pg_track_optimizer()
WHERE blks_accessed -> 'mean' > 1000
ORDER BY wca_error -> 'mean' DESC;

-- Save statistics to disk
SELECT pg_track_optimizer_flush();

-- Clear all tracked statistics
SELECT pg_track_optimizer_reset();

-- Check extension status
SELECT * FROM pg_track_optimizer_status;