Fix PostgreSQL Slow Query

What's Actually Happening

PostgreSQL queries are taking too long to execute. Application response times are slow, timeouts are occurring, or queries are consuming excessive resources.

The Error You'll See

Query timeout:

ERROR: canceling statement due to statement timeout

Slow query log:

LOG: duration: 5000.123 ms  statement: SELECT * FROM large_table WHERE ...

Why This Happens

1. 1.Missing index - Query scanning full table
2. 2.Inefficient query - Poorly written SQL
3. 3.Outdated statistics - Planner using wrong plan
4. 4.Lock contention - Query blocked by locks
5. 5.Resource limits - Memory or I/O constraints
6. 6.Large result set - Query returning too many rows
7. 7.Network latency - Slow network to database

Step 1: Identify Slow Queries

```sql ALTER SYSTEM SET log_min_duration_statement = 1000; SELECT pg_reload_conf();

SELECT query, calls, total_time, mean_time, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'; ```

Step 2: Analyze Query Plan

```sql EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM users WHERE email = 'user@example.com'; ```

Step 3: Check Index Usage

```sql SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

SELECT indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE relname = 'users';

SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE seq_scan > idx_scan;

CREATE INDEX idx_users_email ON users(email); ```

Step 4: Update Statistics

```sql ANALYZE users;

VACUUM ANALYZE users;

SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'users';

SHOW autovacuum; ```

Step 5: Check Lock Contention

```sql SELECT locktype, relation::regclass, mode, pid, granted FROM pg_locks WHERE NOT granted;

SELECT blocked.pid, blocked.query, blocking.pid, blocking.query FROM pg_stat_activity blocked JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype WHERE NOT blocked_locks.granted;

SELECT pg_cancel_backend(pid);

SET lock_timeout = '10s'; ```

Step 6: Optimize Query

```sql SELECT id, email, name FROM users WHERE email = 'user@example.com';

SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.active = true; ```

Step 7: Check Database Configuration

```sql SHOW shared_buffers; SHOW work_mem; SHOW effective_cache_size;

SELECT name, setting, unit FROM pg_settings WHERE name IN ('shared_buffers', 'work_mem', 'effective_cache_size');

ALTER SYSTEM SET shared_buffers = '4GB'; ALTER SYSTEM SET work_mem = '256MB'; ```

Step 8: Check Resource Usage

```bash ps aux | grep postgres

iostat -x 1

df -h /var/lib/postgresql

psql -c "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;"

psql -c "SELECT count(*) FROM pg_stat_activity;"

psql -c "SHOW max_connections;" ```

Step 9: Use Query Hints

```sql SET enable_seqscan = off; EXPLAIN SELECT * FROM users WHERE email = 'user@example.com'; SET enable_seqscan = on;

SET statement_timeout = '30s'; ```

Step 10: Monitor Query Performance

```sql CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, total_time/calls as avg_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

SELECT pg_stat_statements_reset(); ```

PostgreSQL Slow Query Checklist

Verify the Fix

```sql EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

SELECT idx_scan FROM pg_stat_user_indexes WHERE indexrelname = 'idx_users_email';

SELECT count(*) FROM pg_locks WHERE NOT granted;

SELECT mean_time FROM pg_stat_statements WHERE query LIKE '%users%';

SELECT now() - query_start, query FROM pg_stat_activity WHERE state = 'active'; ```

Related Issues

• [Fix PostgreSQL Connection Timeout](/articles/fix-postgresql-connection-timeout)
• [Fix MySQL Slow Query](/articles/fix-mysql-slow-query)