PostgreSQL Query Optimization: A Practical Guide

Slow queries are one of the most common performance issues in production systems. In high-traffic environments processing millions of monthly requests, query performance directly impacts user experience. Here's a practical approach to finding and fixing slow queries.

Start with EXPLAIN ANALYZE

Before optimizing anything, understand what the query is actually doing:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.status, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2025-01-01'
  AND o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 100;

Key things to look for:

Seq Scan vs Index Scan: Sequential scans on large tables are often the problem
Actual rows vs planned rows: Big differences indicate stale statistics
Buffers: How much data is being read from disk vs cache
Loops: Nested loops with high iteration counts

Query Optimization at Scale

In one project involving a microservices-based bidding platform, we had bulk API calls that needed optimization. One query looked innocent:

SELECT v.*, 
       (SELECT COUNT(*) FROM bids WHERE vehicle_id = v.id) as bid_count,
       (SELECT MAX(amount) FROM bids WHERE vehicle_id = v.id) as highest_bid
FROM vehicles v
WHERE v.auction_id = $1;

The problem: correlated subqueries executing for each row. With 500 vehicles per auction, that's 1000 additional queries.

Through query optimization, we achieved up to 13x performance improvements, bringing most queries under 50ms.

The fix: lateral join or aggregation in a single pass:

SELECT v.*, 
       COALESCE(b.bid_count, 0) as bid_count,
       b.highest_bid
FROM vehicles v
LEFT JOIN (
    SELECT vehicle_id, 
           COUNT(*) as bid_count, 
           MAX(amount) as highest_bid
    FROM bids
    GROUP BY vehicle_id
) b ON b.vehicle_id = v.id
WHERE v.auction_id = $1;

Result: Under 1 second.

Index Strategies

Indexes aren't free. They speed up reads but slow down writes. Here's how I think about them:

Composite indexes: Column order matters. Put high-selectivity columns first.

-- Good for: WHERE status = 'pending' AND created_at > '2025-01-01'
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- Less useful if you often query by created_at alone
-- Consider a separate index or reversing the order

Partial indexes: Index only what you need.

-- Only index pending orders (10% of data, 90% of queries)
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

Covering indexes: Include columns to avoid table lookups.

-- Includes customer_name to satisfy the query from index alone
CREATE INDEX idx_orders_covering ON orders(status, created_at)
INCLUDE (customer_name, total);

Common Anti-Patterns

Functions on indexed columns:

-- Bad: Can't use index on created_at
WHERE DATE(created_at) = '2025-01-15'

-- Good: Index-friendly range
WHERE created_at >= '2025-01-15' AND created_at < '2025-01-16'

OR conditions:

-- Often causes full table scan
WHERE status = 'pending' OR status = 'processing'

-- Better: Use IN
WHERE status IN ('pending', 'processing')

LIKE with leading wildcard:

-- Can't use regular B-tree index
WHERE email LIKE '%@gmail.com'

-- Consider: trigram index or reversed column
CREATE INDEX idx_email_trgm ON users USING gin(email gin_trgm_ops);

Analyzing Production Queries

PostgreSQL's pg_stat_statements extension is invaluable:

SELECT query,
       calls,
       mean_exec_time,
       total_exec_time,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This shows you where time is actually spent, not where you think it's spent.

Statistics and Planner

Sometimes the query plan is bad because PostgreSQL's statistics are stale:

-- Update statistics for a table
ANALYZE orders;

-- Check when stats were last updated
SELECT relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

For columns with non-uniform distribution, consider extended statistics:

CREATE STATISTICS orders_stats (dependencies)
ON status, priority FROM orders;

Connection Pooling

Not strictly query optimization, but connection overhead adds up. Each PostgreSQL connection costs memory (roughly 10MB). With high concurrency:

Use PgBouncer or similar pooler
Transaction pooling mode for most workloads
Session pooling only when you need session state

Lessons Learned

Measure first: Don't guess where the problem is
Test with production-like data: Query plans change with data volume
Monitor continuously: Performance degrades as data grows
Index maintenance: Bloated indexes need periodic reindexing

Query optimization is iterative. Make one change, measure, repeat. The goal isn't the perfect query. It's a query that meets your performance requirements with acceptable maintenance overhead.

Sources and further reading

The reference material behind everything above, in the order I would read it.

PostgreSQL: Using EXPLAIN. What every node in a plan means, and the difference between estimated and actual rows, which is where most mysteries resolve.
PostgreSQL: Statistics used by the planner. Why the planner chose what it chose, and what to do when its row estimates are badly wrong.
PostgreSQL: Index types. B-tree, hash, GiST, GIN and BRIN, and the access patterns each one is actually for.
PostgreSQL: pg_stat_statements. The extension that tells you which queries cost the most in aggregate, which is rarely the one somebody complained about.
Use The Index, Luke by Markus Winand. The clearest explanation anywhere of how a B-tree index is actually used, and why column order in a composite index decides whether it helps at all.
explain.depesz.com. Paste a plan, see where the time really went. Faster than reading nested plan output by eye.
PgBouncer features, for the pooling section, including the table of what transaction pooling breaks.

Continue learning

If plans now make sense, the next thing worth understanding is how the planner estimates rows, because almost every bad plan traces back to a bad estimate rather than a bad algorithm. After that, index-only scans and visibility maps, which explain why an index that looks perfect still hits the heap. Then vacuum and bloat, since a table that has grown three times its useful size will be slow no matter how good the query is.

FAQ

Why is my index being ignored? Usually because the planner expects to touch a large fraction of the table, where a sequential scan genuinely wins. Sometimes because a function or type cast on the column makes the index unusable. Check the estimated rows first.

Should I add an index for every slow query? No. Every index costs write amplification on every insert and update, and space. Look for one index that serves several queries, and drop the ones nothing uses.

How do I find the queries that actually matter? pg_stat_statements, ordered by total time rather than mean time. A query taking 20ms ten thousand times an hour costs far more than a 2 second report nobody runs twice.