Slow Query Analysis — pg_stat_statements · MySQL Slow Log

-- Enable the extension (postgresql.conf)
-- shared_preload_libraries = 'pg_stat_statements'

-- Install the extension in the database
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Verify the configuration
SHOW shared_preload_libraries;

# Edit postgresql.conf
sudo nano /etc/postgresql/16/main/postgresql.conf

# Add:
# shared_preload_libraries = 'pg_stat_statements'
# pg_stat_statements.track = all
# pg_stat_statements.max = 10000

sudo systemctl restart postgresql

-- Top 10 by average execution time
SELECT
  round(mean_exec_time::numeric, 2) AS avg_ms,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  rows,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- By total execution time (highest-impact queries)
SELECT
  round(total_exec_time::numeric / 1000, 2) AS total_sec,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Queries with high I/O
SELECT
  left(query, 80),
  shared_blks_read,
  shared_blks_hit,
  round(shared_blks_hit * 100.0 / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_rate
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

-- Reset statistics
SELECT pg_stat_statements_reset();

-- Basic execution plan
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- Actual execution + statistics
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.name
ORDER BY COUNT(o.id) DESC;

Seq Scan     → sequential scan (slow; needs an index)
Index Scan   → uses an index (fast)
Bitmap Scan  → moderate
Hash Join    → large joins
Nested Loop  → small joins

Actual Rows >> Estimated Rows → stale statistics
cost=0..1234 → estimated cost
actual time=0.1..500 ms → actual time

-- Update statistics
ANALYZE users;
ANALYZE VERBOSE users;

# /etc/mysql/mysql.conf.d/mysqld.cnf
slow_query_log       = 1
slow_query_log_file  = /var/log/mysql/slow.log
long_query_time      = 1          # 1 second or more
log_queries_not_using_indexes = 1 # also log queries that don't use indexes

sudo systemctl restart mysql

# Live monitoring
sudo tail -f /var/log/mysql/slow.log

# Analyze with mysqldumpslow
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log   # Top 10 by total time
sudo mysqldumpslow -s c -t 10 /var/log/mysql/slow.log   # by call count

EXPLAIN SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.name;

-- Key columns
-- type: ALL (full scan) < index < range < ref < eq_ref < const (better)
-- key: index used
-- rows: estimated rows examined
-- Extra: Using filesort, Using temporary → performance issues

-- 1. WHERE clause without an index → add an index
CREATE INDEX idx_users_status_email ON users(status, email);

-- 2. SELECT * → only the columns you need
SELECT id, name FROM users WHERE status = 'active';

-- 3. N+1 problem → solve with a JOIN
-- Bad: a query per user inside a loop
-- Good:
SELECT u.id, u.name, o.id, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

-- 4. Cursor pagination instead of OFFSET
-- Bad: LIMIT 10 OFFSET 10000
-- Good:
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;