Performance Optimization

Code

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

Explanation

EXPLAIN ANALYZE inspects query plans; VACUUM reclaims space; statistics help the optimizer choose plans.

More SQL Snippets