5 PostgreSQL Queries Running Slow? Check These Before Anything Else

Most database performance issues come down to the same handful of problems. Before you start tweaking configs or throwing hardware at it, run these checks.

1. Missing Indexes on WHERE/JOIN Columns

-- Find the biggest sequential scans (tables being read row-by-row)
SELECT relname, seq_scan, seq_tup_read, 
       idx_scan, idx_tup_fetch
FROM pg_stat_user_tables 
ORDER BY seq_tup_read DESC 
LIMIT 10;

If seq_scan is high and idx_scan is low on a large table, you're missing an index. Period.

2. Bloated Tables Killing I/O

-- Check dead tuple ratio (anything above 10% = problem)
SELECT relname, 
       n_dead_tup, 
       n_live_tup,
       ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables 
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC;

If dead_pct is high and last_autovacuum is null or old, your autovacuum isn't keeping up. Fix its settings before anything else.

3. Lock Contention

-- See what's blocking what right now
SELECT blocked.pid AS blocked_pid,
       blocked_activity.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked
JOIN pg_catalog.pg_locks blocking 
  ON blocking.locktype = blocked.locktype
  AND blocking.database IS NOT DISTINCT FROM blocked.database
  AND blocking.relation IS NOT DISTINCT FROM blocked.relation
  AND blocking.pid != blocked.pid
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked.pid = blocked_activity.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking.pid = blocking_activity.pid
WHERE NOT blocked.granted;

Long-running transactions holding locks will cascade into everything else. Kill the blocker or fix the transaction.

4. Connection Saturation

# Quick check from the terminal
psql -c "SELECT count(*), state FROM pg_stat_activity GROUP BY state;"

If you're near max_connections with most in idle state, you need a connection pooler (PgBouncer), not more connections.

5. The Query Itself

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT ... -- your slow query here

Look for: Seq Scan on large tables, Nested Loop with high row counts, Sort operations spilling to disk. These are your targets.

---

These five checks solve ~80% of PostgreSQL performance issues I see in production. The other 20% usually comes down to hardware, replication lag, or application-level query patterns — but always start here first.

ShellForge Academy