PostgreSQL Query Optimization: Practical Tips

Slow queries are inevitable. At some point, the table that was fine at 10k rows starts causing timeouts at 10M rows. Here's how I diagnose and fix them.

Start with EXPLAIN ANALYZE

Never guess. Always start with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT u.*, p.title
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE u.created_at > '2024-01-01'
ORDER BY p.created_at DESC
LIMIT 20;

The output tells you:

• Seq Scan — reading every row, no index used
• Index Scan — using an index (good)
• Hash Join / Nested Loop — join strategy
• actual time — real execution time per node
• rows — estimated vs actual (big differences = stale stats)

The Most Common Problem: Missing Indexes

If you see Seq Scan on a large table, you probably need an index:

-- Before: Seq Scan on users
SELECT * FROM users WHERE email = '[email protected]';
 
-- Add the index
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
 
-- After: Index Scan

Use CONCURRENTLY to avoid locking the table during index creation in production.

Composite Indexes

Order matters. Put the most selective column first, and match your query's WHERE clause order:

-- Query filters by status AND created_at
SELECT * FROM orders 
WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';
 
-- Good composite index
CREATE INDEX idx_orders_status_created 
ON orders(status, created_at DESC);

N+1 Queries

The silent killer of ORM-based apps. Instead of fetching related data in a loop:

-- Bad: 1 query to get users + N queries for their posts
SELECT * FROM users WHERE active = true;
-- then for each user:
SELECT * FROM posts WHERE user_id = $1;

Use a JOIN or a single WHERE IN:

-- Good: one query
SELECT u.id, u.name, json_agg(p.*) as posts
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.active = true
GROUP BY u.id, u.name;

Partial Indexes

Index only the rows you actually query:

-- Only index active users (not the millions of inactive ones)
CREATE INDEX idx_users_email_active 
ON users(email) 
WHERE active = true;
 
-- Queries with WHERE active = true will use this smaller, faster index
SELECT * FROM users WHERE email = $1 AND active = true;

Vacuum and Statistics

PostgreSQL's query planner uses statistics to estimate row counts. If stats are stale, the planner makes bad decisions:

-- Update statistics manually
ANALYZE users;
 
-- Check when stats were last updated
SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE tablename = 'users';

In production, make sure autovacuum is enabled and tuned. For high-write tables, you may need more aggressive settings:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

Connection Pooling

Database connections are expensive. In production, always use a connection pooler:

• PgBouncer — battle-tested, transaction pooling
• pg_pool — built into some managed providers
• Supabase/Neon — connection pooling built in

Most "slow query" problems at scale are actually connection exhaustion — the app is waiting for a connection, not for the query itself.

Quick Wins Checklist

When a query is slow, run through this list:

1. EXPLAIN ANALYZE — identify the bottleneck
2. Missing index on WHERE / JOIN columns?
3. Composite index needed for multi-column filters?
4. N+1 pattern in your ORM layer?
5. Can you add a partial index?
6. Are statistics stale? (ANALYZE tablename)
7. Are you selecting * when you only need 3 columns?
8. Is the connection pool exhausted?

Most slow queries are fixed by one of the first three.