PostgreSQL Query Optimization: From Slow Queries to Sub-Millisecond Responses

PostgreSQL is extraordinarily capable. Almost every "PostgreSQL is slow" complaint traces back to either missing indexes, inefficient query patterns, or default configuration values designed for minimal resource usage rather than performance. Here's a systematic approach to diagnosing and fixing each category.

EXPLAIN ANALYZE: Learning to Read Execution Plans

`EXPLAIN ANALYZE` is the most important command in your optimization toolkit. It shows the query planner's chosen execution strategy, the estimated vs. actual row counts, and the time spent in each node. Key things to look for: Seq Scan on large tables (usually means missing index), Hash Join instead of Index Nested Loop for small result sets (estimates are wrong — analyze your table), and row estimate accuracy (wild divergence between actual and estimated rows means stale statistics).

Index Strategy: Beyond Basic B-Trees

Every PostgreSQL table should have a primary key (clustered B-tree index). Beyond that, index creation should be query-driven. Partial indexes (`CREATE INDEX ON orders(customer_id) WHERE status = 'pending'`) are dramatically smaller and faster than full indexes for filtered queries. GIN indexes power full-text search and JSONB queries. BRIN indexes are efficient for time-series data with natural ordering. Cover frequently-used query columns with composite indexes, ordering by selectivity.

Connection Pooling with PgBouncer

PostgreSQL spawns a process per connection. At 1000 concurrent connections, you have 1000 processes competing for CPU and maintaining shared memory structures. PgBouncer's transaction-mode pooling multiplexes hundreds of application connections through a handful of actual PostgreSQL connections, dramatically reducing overhead. This single change has resolved performance crises for more production databases than any other optimization.

Configuration: max_connections, work_mem, shared_buffers

Default PostgreSQL configuration is deliberately conservative. `shared_buffers` should be 25% of system RAM. `work_mem` controls memory per-operation (sorting, hashing) — setting it too low forces disk spills; too high and many simultaneous queries cause OOM. `effective_cache_size` informs the planner about available OS cache — set it to 75% of system RAM. Enable `pg_stat_statements` to surface your slowest queries automatically.