PostgreSQL Query Optimization: From Slow to Sub-Millisecond

Plain EXPLAIN shows you what PostgreSQL plans to do, without actually running the query:

This tells you the plan — an index scan — and the estimated cost. But it's a prediction. PostgreSQL might be completely wrong about the row count. I've seen estimates of "1 row" when the actual result was 50,000 rows. That kind of misestimate cascades into terrible plan choices.

Use plain EXPLAIN when you want a quick look at the plan without actually running the query. This matters when you're analyzing a DELETE that would modify data, or a query that takes 30 minutes to run.

EXPLAIN ANALYZE — The Truth

Now you see the actual execution time and the actual row count. This is where the real debugging happens. Compare rows=1 (estimated) with rows=1 (actual) — they match, so the planner made a good decision. When they don't match, that's your first clue.

Warning: EXPLAIN ANALYZE actually runs the query. If you're analyzing an UPDATE or DELETE, wrap it in a transaction:

EXPLAIN (ANALYZE, BUFFERS) — The Full Picture

This is what I actually use 90% of the time:

The BUFFERS output is critical. shared hit=312 means 312 pages came from the buffer cache (RAM). read=45 means 45 pages had to be read from disk. If you see a lot of read relative to hit, your shared_buffers might be too small, or the table is too large to stay cached.

FORMAT JSON is also handy when you need to paste the plan into explain.dalibo.com for visualization. The tree view makes complex plans much easier to read:

EXPLAIN Options I Actually Use

Here's the full diagnostic query I run first for any slow query investigation:

• TIMING — per-node timing (on by default with ANALYZE, but I'm explicit)
• VERBOSE — shows output column lists and schema-qualified table names

Reading Query Plans Like a Detective

Every query plan is a tree. PostgreSQL reads it bottom-up: the deepest indented nodes execute first, feeding results upward. Understanding the scan types is the foundation of everything else.

Seq Scan — The Full Table Scan

Sequential scan reads every single row in the table. For a million-row table, that's every page on disk.

But here's the nuance: Seq Scan is not always bad. If you're selecting 30% or more of the table, a sequential scan is actually faster than an index scan because sequential I/O is much faster than random I/O. PostgreSQL knows this. If it's choosing a Seq Scan when you have an index, check how selective your WHERE clause actually is.

When it's a problem: when you're selecting a tiny fraction of rows from a large table and there's no index.

Index Scan — The Targeted Lookup

Goes to the B-tree index, finds the matching entries, then fetches the actual rows from the table heap. Each row fetch is a random I/O operation to the heap. This is great for highly selective queries but each heap fetch has a cost.

Index Only Scan — The Holy Grail

This is the fastest scan type. PostgreSQL gets everything it needs from the index alone, never touching the table heap. You see this when all the columns you SELECT and WHERE on are in the index.

The catch: Heap Fetches: 0 means the visibility map is up to date. If your table has a lot of dead tuples (not vacuumed recently), PostgreSQL still has to check the heap to verify row visibility. This is one reason autovacuum matters for performance, not just disk space.

Bitmap Scan — The Middle Ground

Bitmap scans are PostgreSQL's answer to the "too many rows for an index scan, too few for a seq scan" problem. It builds a bitmap of which pages contain matching rows, sorts them by physical location, then fetches them in order. This converts random I/O into sequential I/O.

You'll often see bitmap scans when two or more indexes are combined:

This BitmapAnd combines two separate indexes. It's PostgreSQL saying "I don't have a composite index, but I can combine these two single-column indexes." It works, but a proper composite index would be faster.

Spotting Bad Estimates

The number one thing I look for in a query plan is mismatched estimates:

The planner estimated 1 row. The actual result was 48,753 rows. That's a five-order-of-magnitude misestimate. The planner chose a Nested Loop because it thought it was joining against 1 row. With 48,753 rows, a Hash Join would have been orders of magnitude faster.

Common causes of bad estimates:

• Stale statistics: Run ANALYZE on the table
• Correlated columns: The planner assumes column values are independent. If status = 'shipped' and created_at > '2026-01-01' are correlated (most recent orders are shipped), the planner underestimates the combined selectivity
• Custom functions in WHERE: The planner uses a default selectivity estimate (usually 0.5% for equality, 33% for range) when it can't analyze a function
• Parameterized queries with generic plans: After 5 executions, PostgreSQL may switch to a generic plan that doesn't consider the actual parameter value

When you see bad estimates, the fix is usually one of: run ANALYZE, create extended statistics, rewrite the query, or use a CTE as an optimization fence.

Index Strategy: The Right Index for the Right Job

Indexes aren't free. Each index slows down writes, consumes disk space, and needs to be maintained. The goal isn't "index everything" — it's "index exactly what you need."

B-tree — The Default Workhorse

B-tree is the default and handles the vast majority of cases. It supports equality and range operators (=, <, >, <=, >=, BETWEEN, IN, IS NULL).

B-tree index column order matters enormously for composite indexes. The leftmost column is the primary sort, then the next, and so on. An index on (a, b, c) can efficiently answer:

• WHERE a = 1
• WHERE a = 1 AND b = 2
• WHERE a = 1 AND b = 2 AND c = 3
• WHERE a = 1 AND b > 5
• WHERE a = 1 ORDER BY b

It cannot efficiently answer:

• WHERE b = 2 (skips the first column)
• WHERE c = 3 (skips the first two columns)
• WHERE a = 1 AND c = 3 (gap in the middle — a condition used, c condition requires filter)

Think of it like a phone book sorted by last name, then first name. You can quickly find all "Smith" entries, or "Smith, John" specifically. But you can't quickly find all "John" entries without scanning the entire book.

The rule: put equality conditions first, then range conditions, then sort columns.

Covering Indexes with INCLUDE

PostgreSQL 11+ lets you add non-key columns to an index with INCLUDE. These columns are stored in the leaf pages but aren't part of the B-tree structure. They enable index-only scans without bloating the tree:

Now PostgreSQL can satisfy the entire query from the index alone. Without INCLUDE, it would need to fetch total and status from the heap. The difference on a cold cache can be dramatic — I've seen queries go from 50ms to 0.2ms just by adding INCLUDE columns.

Partial Indexes — Index Only What Matters

This is my single favorite PostgreSQL feature and the one most developers don't know about.

This index is tiny compared to a full index on created_at. It only contains rows where status = 'pending'. Queries that match the WHERE clause use this small, fast index. Queries that don't match ignore it.

Real example from production: I had a sessions table with 50 million rows. Only about 200,000 were active (not expired). A full index on user_id was 1.2 GB. A partial index WHERE expires_at > now() was 8 MB. The query went from 12ms to 0.1ms because the entire index fit in cache.

That last one is incredibly useful. It lets you have a unique email constraint on active users while allowing the same email to appear in deleted records.

GIN Indexes — Full-Text Search and JSONB

GIN (Generalized Inverted Index) is the answer when you need to search inside values — arrays, JSONB documents, or full-text search vectors.

GIN indexes are large and slow to build, but blazing fast to query. They're worth it for read-heavy workloads with complex containment queries.

For JSONB, there's also jsonb_path_ops which is smaller and faster for @> containment queries, but doesn't support existence operators (?, ?|, ?&):

GiST Indexes — Geometric and Range Types

GiST (Generalized Search Tree) handles overlapping data types: geometric shapes, ranges, full-text search (alternative to GIN, smaller but slower).

Hash Indexes — Niche but Useful

Hash indexes are only useful for equality comparisons. Since PostgreSQL 10 they're WAL-logged and crash-safe. They're smaller than B-tree for wide columns and slightly faster for pure equality:

In practice, I rarely use hash indexes. B-tree handles equality just fine, and the inability to do range queries or ordering makes hash indexes too inflexible for most real use cases.

When Indexes Hurt

Every index has a cost:

• Write amplification: Each INSERT updates every index on the table. A table with 8 indexes means 8 additional writes per INSERT
• HOT updates blocked: Heap-Only Tuple (HOT) updates are a major optimization where PostgreSQL can update a row without updating indexes, but only if no indexed column changed. More indexes = more chances of blocking HOT updates
• Vacuum overhead: More indexes means vacuum takes longer
• Planner overhead: More indexes means the planner has more options to evaluate

It's worth regularly auditing indexes on production tables:

It's common to find multi-gigabyte indexes created during a one-time migration and never used again. Dropping them speeds up writes noticeably.

The N+1 Query Problem

Every ORM developer hits this eventually. It's the most common performance problem I debug.

What N+1 Looks Like

This generates:

501 queries instead of 1 or 2. Each query is fast individually, maybe 0.5ms. But 501 of them adds up to 250ms of just database time, plus network round-trip latency for each one.

Detecting N+1 in Logs

The fastest way to catch N+1 queries is to enable statement logging temporarily:

Then look at the logs. N+1 is unmistakable — you'll see hundreds of identical queries with different parameter values in rapid succession.

A more targeted approach for development:

This logs the full EXPLAIN ANALYZE output for any query over 100ms, including queries inside functions.

The Fix: Eager Loading or JOINs

ORM approach — tell the ORM to load related data upfront:

Raw SQL approach — just use a JOIN:

One query. Done.

JOINs vs Multiple Queries

There's a valid debate here. Sometimes two queries are better than a JOIN:

The JOIN approach duplicates order data for every item. If each order has 20 items and the order row is wide, that's 20x data transfer. The two-query approach sends each order exactly once.

My rule: use JOINs for one-to-one relationships, consider separate queries for one-to-many when the "one" side is wide. But always benchmark — the network round-trip of a second query often costs more than the duplicate data.

Common Query Rewrites

Some queries are slow not because of missing indexes but because of how they're written. PostgreSQL's planner is good, but it's not magic.

Subquery vs JOIN vs CTE

These three approaches can produce very different plans:

In PostgreSQL 12+, CTEs are usually inlined (the planner treats them like subqueries), so performance is identical. But in PostgreSQL 11 and below, CTEs are optimization fences — the planner materializes them and can't push predicates through them. If you're still on PG 11, avoid CTEs for performance-critical queries.

EXISTS vs IN vs JOIN

This comes up constantly:

For large tables, EXISTS often wins because it short-circuits. The IN version has to build the entire list of return order_ids before filtering. The JOIN version can produce duplicates, requiring DISTINCT which adds a sort or hash step.

My default: use EXISTS when checking for the existence of related rows. It's the most semantically clear and usually the fastest.

But there's a counterexample. If the subquery result set is small and you need it for multiple conditions:

Avoiding SELECT *

This isn't just a code style preference. It has real performance implications:

With SELECT *:

• Can't use index-only scans (all columns would need to be in the index)
• Transfers more data over the network
• Uses more memory for sorting and hashing
• If someone adds a 10MB BYTEA column later, your existing queries silently get slower

Window Functions vs Subqueries

Window functions are one of PostgreSQL's most powerful features and almost always outperform correlated subqueries:

Another common pattern — getting the latest row per group:

DISTINCT ON is my go-to for this pattern. It's concise, readable, and PostgreSQL optimizes it well with the right index:

Pagination Done Right

OFFSET is a trap for large datasets:

The fix is keyset pagination (also called cursor-based pagination):

This is always fast because it uses the index to jump directly to the right position, regardless of which "page" you're on. The trade-off is you can't jump to an arbitrary page number, but for infinite scroll or "next page" UIs, keyset pagination is strictly superior.

For complex sort orders:

This uses row-value comparison, which PostgreSQL handles efficiently with a composite index on (price, id).

Table Statistics and Vacuuming

PostgreSQL's query planner makes decisions based on statistics about your data. Bad statistics lead to bad plans. It's that simple.

ANALYZE: Update the Statistics

The correlation value is particularly interesting. It ranges from -1 to 1 and measures how well the physical order of rows matches the logical order of the column. A correlation close to 1 or -1 means the data is physically sorted on that column, making range scans very efficient (sequential I/O). A correlation near 0 means random I/O for range queries.

The default statistics target is 100, meaning PostgreSQL samples 300 * 100 = 30,000 rows. For columns with many distinct values or skewed distributions, increasing this to 500 or 1000 gives the planner better data at the cost of slightly longer ANALYZE times.

Autovacuum Tuning

Autovacuum does two things: reclaims space from dead tuples (deleted or updated rows) and updates statistics. On busy tables, the default autovacuum settings are often too conservative.

If you see tables with millions of dead tuples and the last vacuum was hours ago, your autovacuum is falling behind.

For high-churn tables (like sessions, job queues, or metrics), I set per-table autovacuum settings:

Table Bloat

When PostgreSQL updates a row, it doesn't modify the row in place — it creates a new version and marks the old one as dead. Vacuum reclaims the dead rows, but the space is only reused by that table. The table file on disk doesn't shrink.

Over time, a heavily updated table can have significant bloat — the table is much larger on disk than the live data requires. This means more pages to scan, more I/O, more cache pressure.

For severely bloated tables, the nuclear option is VACUUM FULL — it rewrites the entire table. But it takes an exclusive lock, so you can't do it on a live production table without downtime. The better approach is pg_repack, which does the same thing without locking:

Connection Pooling

Here's something that surprises a lot of developers: PostgreSQL connections are expensive. Each connection spawns a new process (not a thread), consumes about 5-10 MB of memory, and has a nontrivial fork overhead.

The default max_connections is 100. If you have an application server with 20 workers, each opening 5 connections, you're already at your limit. Add a background job processor, a monitoring tool, and a migration running somewhere, and you're in trouble.

Why You Need a Connection Pooler

Without a pooler, if your application needs to handle 500 concurrent requests, you need 500 PostgreSQL connections. That's 5 GB of memory just for connection overhead, and PostgreSQL's performance degrades significantly beyond a few hundred connections due to process management overhead.

With PgBouncer in front of PostgreSQL, those 500 application connections map to maybe 20 actual PostgreSQL connections. The pooler queues requests when all database connections are busy.

PgBouncer Configuration

Transaction vs Session Mode

• Transaction mode (pool_mode = transaction): Connections are returned to the pool after each transaction. This is what you want 95% of the time. Maximum connection reuse.
• Session mode (pool_mode = session): Connections are held for the entire client session. Use this if you need prepared statements, SET commands, LISTEN/NOTIFY, or other session-level features.
• Statement mode (pool_mode = statement): Connections are returned after each statement. Too restrictive for most applications — you can't even use explicit transactions.

The gotcha with transaction mode: you can't use prepared statements (they're session-level state), you can't use SET for session variables, and LISTEN/NOTIFY won't work as expected. Most ORMs have a way to disable prepared statements.

For Node.js with pg driver:

Pool Sizing Formula

There's a common formula for optimal PostgreSQL connection pool size:

For a modern server with 4 cores and an SSD (1 effective spindle):

This seems counterintuitively low. But PostgreSQL is CPU-bound on most modern hardware (SSDs are fast enough that I/O rarely bottlenecks). More connections than this leads to context switching overhead that actually slows things down.

In practice, I usually set default_pool_size to 2-3x this number to handle burst traffic, with the understanding that at peak load, some queries will wait in the PgBouncer queue rather than all hitting PostgreSQL simultaneously.

Practical Checklist: The Exact Steps for Every Slow Query

Here's my actual process when I get a "this query is slow" report. I follow these steps in this order, every time.

Step 1: Get the Actual Query

Not "the endpoint is slow" — the actual SQL. If you're using an ORM, enable query logging:

Or check pg_stat_statements for the top queries by total time:

pg_stat_statements is the single most valuable extension for PostgreSQL performance. If you're not running it, install it now:

Step 2: Run EXPLAIN (ANALYZE, BUFFERS)

Look for:

1. Bad row estimates — actual rows very different from estimated rows
2. Seq Scans on large tables — potential missing index
3. Nested Loops with many rows — should be a Hash Join or Merge Join
4. High buffer reads — cold cache or table too large
5. Sort operations spilling to disk — increase work_mem or add index for sort

Step 3: Check Table Statistics

If last_analyze is old or n_dead_tup is high relative to n_live_tup, run:

Then re-run EXPLAIN ANALYZE. If the plan changes, stale statistics were the problem.

Step 4: Check Existing Indexes

Maybe the index exists but isn't being used. Maybe the column order is wrong for your query.

Step 5: Create or Modify Indexes

Based on the query plan, create the appropriate index. Test with EXPLAIN ANALYZE before and after.

Always use CONCURRENTLY on production tables. A regular CREATE INDEX takes a full table lock that blocks all writes.

Step 6: Consider Query Rewrites

If the index exists and statistics are fresh but the query is still slow, look at the query itself:

• Can a subquery be rewritten as a JOIN?
• Is OFFSET causing problems? Switch to keyset pagination
• Are you selecting more columns than needed?
• Can a correlated subquery become a window function?
• Is a CTE preventing the planner from optimizing?

Step 7: Check Server Settings

For specific query patterns, server settings matter:

If changing work_mem fixes a sort-to-disk problem, consider increasing it globally. But be careful — it's per-operation, not per-connection. A complex query with 10 sort operations and work_mem = 256MB could use 2.5 GB for a single query.

Step 8: Monitor After the Fix

Don't just fix and forget. Verify the fix holds up:

Bonus: Quick Wins I Apply to Every New Database

These are settings and practices I apply to every PostgreSQL database I set up, before any performance issues arise:

And a monitoring query I run weekly:

Final Thoughts

PostgreSQL query optimization isn't a dark art. It's a systematic process:

1. Measure — don't guess. EXPLAIN (ANALYZE, BUFFERS) is your best friend.
2. Understand the plan — learn to read scan types, join types, and row estimates.
3. Index strategically — the right index on the right columns, in the right order. Partial indexes and covering indexes are underused superpowers.
4. Write better queries — EXISTS over IN for existence checks, keyset pagination over OFFSET, window functions over correlated subqueries.
5. Maintain the database — autovacuum tuning, statistics updates, connection pooling.
6. Monitor continuously — pg_stat_statements tells you where your database spends its time. Check it regularly.

The difference between a query that takes 4 seconds and one that takes 0.3 milliseconds is rarely hardware. It's almost always knowledge — knowing where to look and what to change. And now you know where to look.