Code
sql
-- View execution plan
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
-- View slow queries
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
-- Table statistics
SELECT relname, n_live_tup, last_vacuum, last_analyze
FROM pg_stat_user_tables;
-- Update statistics
ANALYZE users;
-- Clean dead tuples
VACUUM ANALYZE users;
-- View index usage
SELECT relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- View locks
SELECT pid, mode, granted, query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid;