Start With EXPLAIN ANALYZE
Guessing at performance problems wastes time. Always start by looking at the actual query plan:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 4821 AND status = 'shipped';Look for Seq Scan on large tables — that’s usually your first sign a missing index is causing a full table scan.
Adding the Right Index
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);Column order matters: put the most selective, most frequently filtered column first when queries commonly filter on both.
Composite vs Single-Column Indexes
A composite index on (customer_id, status) serves queries filtering on customer_id alone or on both columns together, but won’t help a query filtering on status alone. Match your indexes to your actual query patterns, not every possible combination.
Avoiding N+1 Queries
-- N+1: one query per order to fetch items (bad)
SELECT * FROM order_items WHERE order_id = 1;
SELECT * FROM order_items WHERE order_id = 2;
-- ... repeated for every order
-- Fixed: single query with IN
SELECT * FROM order_items WHERE order_id IN (1, 2, 3, 4, 5);Using Partial Indexes
If you frequently query only active or recent rows, a partial index keeps the index smaller and faster:
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';Watch for Implicit Type Casts
-- Prevents index usage if customer_id is an integer column
SELECT * FROM orders WHERE customer_id = '4821';
-- Correct type, index-friendly
SELECT * FROM orders WHERE customer_id = 4821;Connection Pooling
Beyond query-level tuning, use a connection pooler like PgBouncer for high-concurrency applications — PostgreSQL’s per-connection memory overhead makes thousands of direct connections expensive.
Monitoring Ongoing Performance
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;The pg_stat_statements extension surfaces your slowest queries in aggregate, which is far more useful than debugging one-off slow requests reactively.
Conclusion
Most PostgreSQL performance issues come down to missing or mismatched indexes and N+1 query patterns. Make EXPLAIN ANALYZE a habit before assuming you need a bigger database instance.