PostgreSQL Query Optimization: From Slow to Sub-10ms

Most slow PostgreSQL queries share the same handful of problems. This guide shows you how to diagnose and fix them systematically.

Start with EXPLAIN ANALYZE

Never guess. Always profile first:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, count(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.email;

Key things to look for in the output:

• Seq Scan on large tables = missing index
• Hash Join on huge datasets = may need better join conditions
• actual rows >> estimated rows = stale statistics, run ANALYZE
• Buffers: hit=X read=Y = high read means disk I/O, needs caching or indexes

The Most Common Issues

1. Missing Indexes

-- Find slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Check existing indexes
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

-- Add the right index
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders(user_id, created_at DESC);

Use CONCURRENTLY in production -- it builds the index without locking the table.

2. N+1 Queries

-- Bad: one query per user to get their orders
SELECT * FROM users WHERE active = true;
-- Then for each user: SELECT * FROM orders WHERE user_id = ?

-- Good: single JOIN
SELECT u.*, json_agg(o.*) as orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.active = true
GROUP BY u.id;

In ORMs, use eager loading. In raw SQL, use JOINs or batch fetching.

3. SELECT * in Production

-- Bad
SELECT * FROM users WHERE id = $1;

-- Good
SELECT id, email, name, created_at FROM users WHERE id = $1;

SELECT * fetches large text/JSONB columns you don't need, bypasses index-only scans, and breaks when schema changes.

4. Missing Partial Indexes

Index only the rows you actually query:

-- Only index active orders (80% of queries, 20% of rows)
CREATE INDEX idx_orders_active
ON orders(user_id, created_at)
WHERE status = 'active';

-- Index is tiny, queries are fast

5. Inefficient LIKE Queries

-- No index possible
WHERE name LIKE '%smith%'

-- Can use index (prefix search only)
WHERE name LIKE 'smith%'

-- For full-text: use GIN index
CREATE INDEX idx_users_name_gin ON users USING gin(to_tsvector('english', name));
SELECT * FROM users WHERE to_tsvector('english', name) @@ plainto_tsquery('smith');

Table Partitioning for Large Tables

For tables with 100M+ rows, partitioning can turn full scans into partition scans:

-- Partition orders by created_at (range partitioning)
CREATE TABLE orders (
    id BIGSERIAL,
    user_id BIGINT,
    created_at TIMESTAMPTZ NOT NULL,
    total_cents INT
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE orders_2026 PARTITION OF orders
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- Query only hits the relevant partition
SELECT * FROM orders WHERE created_at >= '2026-01-01';

Connection Pooling

Never connect directly to PostgreSQL in a web app -- use PgBouncer:

# pgbouncer.ini
[databases]
mydb = host=localhost dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

This lets 1000 app connections share 25 actual PostgreSQL connections. Without it, each connection uses ~10MB RAM and context switching kills performance.

Quick Wins Checklist

• Run VACUUM ANALYZE on tables that see heavy inserts/deletes
• Set work_mem = 64MB for sort-heavy queries (check shared_buffers too)
• Use RETURNING instead of a separate SELECT after INSERT/UPDATE
• Replace COUNT(*) > 0 with EXISTS for existence checks
• Add indexes on all foreign keys (Postgres doesn't do this automatically)