Optimization: PostgreSQL query and index tuning with pg_stat_statements - top queries, unused and duplicate indexes

#Enable pg_stat_statements

# postgresql.conf (managed services: parameter group/server parameters)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

#Top queries by total time

SELECT queryid, calls,
       round(total_exec_time::numeric / 1000, 1) AS total_s,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows, shared_blks_read, temp_blks_written,
       left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Reset after a tuning round: SELECT pg_stat_statements_reset();

#Analyse with EXPLAIN

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;

See the EXPLAIN ANALYZE post for reading plans. Create indexes concurrently in production:

CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at DESC);

Consider partial indexes for common filters (WHERE status = 'open') and covering indexes with INCLUDE.

#Unused indexes

SELECT s.schemaname, s.relname, s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

Statistics are per server. Check replicas too, and check pg_stat_database.stats_reset to know the period covered.

#Duplicate indexes

SELECT indrelid::regclass AS tbl, array_agg(indexrelid::regclass) AS dup_indexes
FROM pg_index
GROUP BY indrelid, indkey::text, indclass::text, indcollation::text,
         coalesce(indexprs::text, ''), coalesce(indpred::text, '')
HAVING count(*) > 1;

Drop with DROP INDEX CONCURRENTLY.

#Track over time

Snapshot pg_stat_statements into a table daily (or use your monitoring tool) to see trends and regressions after releases.

Tip

Sort by total time, not mean time. A 2 ms query called 50 million times a day usually matters more than one 30-second report.

#Checklist

• pg_stat_statements enabled
• Top 20 by total time reviewed
• Fixes validated with EXPLAIN (ANALYZE, BUFFERS)
• Indexes created/dropped concurrently
• Unused and duplicate indexes removed (replicas checked)
• Trends tracked