PostgresEXPLAINANALYZEIsGaslightingYou:ReadPlansCorrectly

The Plan Is Lying to You

You run EXPLAIN ANALYZE on a slow query. Postgres spits out a wall of text. You squint at it, find the node with the highest cost number, maybe paste it into some online visualizer, and conclude that the problem is obvious. Except it isn't. The query is still slow. The index you added made things worse. Your coworker is questioning your competence. Welcome to the club.

Here's the uncomfortable truth: EXPLAIN ANALYZE doesn't lie outright, but it absolutely misleads you if you don't know how to read it. The numbers aren't wrong. The interpretation of those numbers is where engineers go sideways. I've spent more hours than I'd like to admit staring at execution plans and drawing completely wrong conclusions, and I've watched senior engineers do the same thing. This article is about the specific ways Postgres execution plans deceive you and what to actually look for.

Costs Are Not Milliseconds

This is the biggest trap. When you see cost=0.00..8.17, your brain immediately maps "8.17" to something in the real world, like a duration. It isn't. That number is a unit-less estimate — a relative score that Postgres's query planner uses internally to compare different possible execution strategies. It's based on abstract "cost units" where, by default, a sequential page read costs 1.0 and a random page read costs 4.0.

These numbers are calibrated against each other, not against wall-clock time. A cost of 50,000 might execute in 2ms on a warm cache. A cost of 200 might execute in 800ms because it's doing something the planner didn't anticipate, like hitting disk repeatedly or processing rows in a way that causes memory pressure. In my experience, developers who fixate on the cost numbers are usually chasing the wrong problem. The cost tells you what Postgres thought it would take. The actual time tells you what actually happened.

The estimated row counts versus actual row counts are where the real gold is hidden. When you see rows=1 estimated but rows=94823 actual, that's a fire alarm. That mismatch means the planner made a plan based on completely wrong assumptions, and every upstream decision — which join algorithm to use, whether to hash or merge or nest loops, how much memory to allocate — was based on bad data. Fix the statistics problem first. Everything else is noise.

The Buffering Problem Nobody Talks About

Run your query with EXPLAIN (ANALYZE, BUFFERS) and you unlock a whole new layer of deception. Those buffer numbers — shared hit=142, shared read=8901 — tell you whether Postgres found data in its shared buffer cache or had to go to disk. Most people skip right past this section.

Here's the analogy I use: imagine a library. Postgres's shared buffer cache is the books already on your desk. A "hit" means you grabbed something from your desk. A "read" means you had to walk to the stacks and retrieve it. If your query is doing thousands of reads, you're making thousands of trips to the stacks. The query looks efficient on paper because the row counts are reasonable and the cost estimate is low, but it's hammering disk I/O on every execution.

This matters especially when you're benchmarking. If you run EXPLAIN ANALYZE twice on the same query, the second run is almost always faster because the buffers warmed up. I've seen developers benchmark their "optimized" query, celebrate a 10x speedup, deploy to production, and then wonder why the improvement disappeared under load. Production has real concurrent workloads evicting pages from cache constantly. What ran hot in your local testing is running cold in production. Always look at the buffer numbers, and when possible, use pg_prewarm strategically or test with cold caches.

How Nested Loops Devour You Alive

Let's look at a real scenario. Say you have two tables: orders and customers, and you're joining them.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > NOW() - INTERVAL '7 days';

You might get a plan that looks like this (simplified):

Nested Loop  (cost=0.42..1893.21 rows=843 width=52)
             (actual time=0.082..312.441 rows=9402 loops=1)
  Buffers: shared hit=203 read=18821
  ->  Seq Scan on orders  (cost=0.00..843.00 rows=843 width=20)
                          (actual time=0.015..12.042 rows=9402 loops=1)
        Filter: (created_at > (now() - '7 days'::interval))
        Rows Removed by Filter: 40821
  ->  Index Scan using customers_pkey on customers
                          (cost=0.42..2.23 rows=1 width=36)
                          (actual time=0.030..0.030 rows=1 loops=9402)
        Index Cond: (id = o.customer_id)
        Buffers: shared hit=200 read=18621

See that loops=9402 on the inner index scan? Postgres executed that index scan 9,402 times — once per row from the outer scan. Each loop is doing an index lookup. Even though each individual lookup is fast, 9,402 of them is not. The planner estimated 843 rows from the outer scan and got 9,402 instead — that's the row count lie. It planned for a nested loop that would be fine at 843 rows, but catastrophic at 9,402. The fix here isn't the index itself — it's usually outdated statistics causing the planner to underestimate. Run ANALYZE orders; and check if your statistics targets are appropriate for high-cardinality columns.

Parallel Plans and Why Your Numbers Are Doubled

Modern Postgres uses parallel query execution, which is wonderful for performance and absolutely maddening for plan reading. When Postgres splits work across multiple worker processes, the timing numbers in the plan represent per-worker time, not total wall-clock time. This trips up even experienced engineers.

If you see a parallel plan and the node says actual time=0.012..4820.112 rows=500000 loops=1 and there are 4 workers, you might think the total time is around 5 seconds. But the workers ran in parallel, so the actual elapsed time was roughly 1.2 seconds. The Planning Time and Execution Time at the very bottom of the output are your friends here — those are actual wall-clock numbers. Everything in between is per-worker accounting.

There's also a subtle issue with Gather and Gather Merge nodes. These are the synchronization points where the parallel workers hand their results back to the leader process. The time reported at a Gather node includes the time to wait for all workers to finish — which means if one worker hit a slow disk page, your gather time spikes and it looks like the gather itself is the bottleneck, not the underlying worker computation. Don't treat Gather as a suspicious node. Look through it to the workers below.

The ANALYZE Penalty Is Real

When you run EXPLAIN ANALYZE, Postgres actually executes the query. This is obvious when you think about it, but the implications aren't. For write queries — INSERT, UPDATE, DELETE — this means running EXPLAIN ANALYZE in production without wrapping it in a transaction you roll back will actually mutate your data.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM sessions WHERE expires_at < NOW() - INTERVAL '30 days';
ROLLBACK;

Always do this. Wrap destructive queries in a transaction and roll back after you've seen the plan. I've seen someone EXPLAIN ANALYZE a cleanup job in production without a transaction wrapper and delete 6 months of audit logs. The query was very efficient, by the way. Beautifully fast. Catastrophically destructive.

There's also a subtler penalty: EXPLAIN ANALYZE has overhead from the instrumentation itself. The plan nodes need to track timing, row counts, and loop counts as the query executes. For very fast queries that execute in microseconds, this instrumentation overhead can make the query appear 2-5x slower in the plan output than it actually runs without instrumentation. This is rarely a practical problem, but if you're trying to diagnose a query that normally runs in under a millisecond, be aware you're adding noise.

Reading the Plan Bottom-Up (And Why Top-Down Thinking Fails You)

Postgres execution plans are trees. The actual execution starts at the leaf nodes — the deepest indented operations — and works upward. Data flows from the leaves to the root. If you read top-down, you're reading the plan in the opposite order of how it executes, which means you'll chronologically misread the cause-and-effect chain.

The mental model I use: think of it like a cooking recipe where you have to start with the prep work before you can do any cooking. A Hash Join at the top of the plan means nothing until you understand what each of its two children are producing. One child builds the hash table. The other probes it. Which one is the build side? Whichever has the smaller estimated row count — Postgres tries to build the smaller relation in memory. If the estimates are wrong and the "smaller" relation is actually massive, you're building an enormous hash table that spills to disk, and you'll see that in the buffers as temp writes: Temp Written: 8192 blocks.

Look for those temp block writes. They're a sign of a work_mem problem. When work_mem (the memory allocation per sort or hash operation) isn't large enough, Postgres spills intermediate data to disk. Each spill is a major performance penalty, and it often happens silently — your plan doesn't scream about it, it just quietly shows you temp block writes you weren't expecting.

Statistics, Correlation, and the Multi-Column Lie

Postgres maintains per-column statistics — histograms, most-common values, null fractions — to estimate how many rows a filter will return. These statistics are maintained column by column. The planner assumes column values are independent of each other, which in real data they almost never are.

Classic example: a table of people with columns city and state. Filtering on state = 'CA' returns 12% of rows. Filtering on city = 'Los Angeles' returns 3% of rows. Filtering on both — Postgres multiplies those selectivities: 0.12 × 0.03 = 0.36% of rows. But those columns are highly correlated. LA is in CA. The real selectivity is closer to 3%, not 0.36%. The planner will catastrophically underestimate rows and probably choose a nested loop when it should be doing a hash join.

The fix for this is extended statistics, introduced in Postgres 10:

CREATE STATISTICS city_state_stats (dependencies)
ON city, state FROM people;

ANALYZE people;

After running this, Postgres learns about the functional dependency between city and state and gives you dramatically better row estimates on multi-column filters. This is one of those features that most teams haven't touched yet and it can make a real difference. After creating extended statistics, re-run your EXPLAIN ANALYZE and watch the estimated vs actual row counts converge.

The Auto-Explain Shortcut for Production Queries

You can't always reproduce a slow query in development. Production data distributions are different. Connection pool states are different. The query that takes 40 seconds in production runs in 200ms on your laptop. This is maddening, and it's where auto_explain comes in.

Configure it in postgresql.conf or for a specific session:

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1000;  -- log plans for queries > 1 second
SET auto_explain.log_analyze = true;
SET auto_explain.log_buffers = true;
SET auto_explain.log_nested_statements = true;

Now Postgres will automatically write the full EXPLAIN ANALYZE output for any query exceeding your threshold into the Postgres log. You get the actual execution plan from the actual production execution, with real row counts, real buffer numbers, and real timing. This is invaluable. The log_nested_statements = true setting captures plans from inside functions and stored procedures too, which otherwise are invisible.

Be careful with auto_explain.log_analyze = true in high-throughput production systems — the instrumentation overhead adds up at scale. Start with a high threshold (5000ms) and lower it incrementally. Your DBA will thank you for not trashing performance while investigating a performance problem, which is a very specific category of embarrassment.

What "Good" Actually Looks Like

A healthy execution plan has a few characteristics that you start to recognize after reading enough of them. Estimated rows and actual rows should be in the same order of magnitude — within 3-5x is generally acceptable, but 10x or more is a red flag worth investigating. Index scans should be selective — if an index scan is returning more than maybe 10-15% of the table, a sequential scan would probably be faster and the planner might actually be making a mistake by using the index. Hash joins appear for larger datasets where neither side is tiny. Nested loops are fine for small outer relations with indexed inner lookups.

The most important habit I've developed: never diagnose from a single plan run. Run it multiple times. Check if the timing is stable. Use pg_stat_statements to see aggregate execution statistics across thousands of real calls. A query that occasionally spikes to 5 seconds but usually runs in 50ms has a completely different root cause than one that consistently takes 3 seconds. The plan alone can't tell you that. The plan is a snapshot. Treat it like one.