How to Use EXPLAIN ANALYZE in PostgreSQL: A Visual Guide

# How to Use EXPLAIN ANALYZE in PostgreSQL: A Visual Guide

TL;DR -- Key Takeaways

>

- EXPLAIN shows what PostgreSQL *plans* to do; EXPLAIN ANALYZE shows what it *actually did*.

- Always read query plans bottom-up, from the innermost node to the outermost.

- Watch for Seq Scans on large tables, high row-estimate mismatches, and nested loops with large outer sets.

- Adding the right index can turn a 3-second query into a 3-millisecond query.

- Visual tools like QueryDeck make it dramatically easier to spot bottlenecks at a glance.

A single slow query can cascade through your entire application. It holds connections, stalls other transactions, and -- if it runs often enough -- drives up your cloud bill faster than any feature could. The question is never *if* you will encounter a slow query, but *when*. And when that moment arrives, EXPLAIN ANALYZE in PostgreSQL is the single most important diagnostic tool you have.

This guide walks you through everything: how to run it, how to read the output, and how to act on what you find. Every example uses realistic SQL against a schema you could encounter in production.

What Is EXPLAIN ANALYZE in PostgreSQL?

PostgreSQL ships with two related commands that let you inspect query execution:

`EXPLAIN` displays the query plan the planner *intends* to use. It shows the estimated cost, expected row counts, and chosen access methods -- without actually running the query.

`EXPLAIN ANALYZE` does everything EXPLAIN does, and then *executes* the query for real. The output includes actual runtimes, actual row counts, and loop counts for every node in the plan.

Why the distinction matters

EXPLAIN is safe to run on production databases against destructive statements because it never touches data. EXPLAIN ANALYZE will execute the query, which means:

• SELECT queries are generally safe (the results are discarded).
• INSERT, UPDATE, and DELETE will modify data unless you wrap them in a transaction and roll back.

A safe pattern for write statements:

BEGIN;
EXPLAIN ANALYZE
  UPDATE orders SET status = 'shipped' WHERE id = 42;
ROLLBACK;

You get the full execution statistics without committing the change.

Useful format options

PostgreSQL supports several output formats. The most practical for day-to-day work:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

• BUFFERS adds information about shared and local buffer hits and reads -- essential for understanding I/O behavior.
• FORMAT TEXT is the default human-readable output. FORMAT JSON is useful if you plan to feed the plan into a visualization tool.

How to Read a PostgreSQL Query Plan

A query plan is a tree. The execution starts at the leaf nodes (deepest indentation) and flows upward to the root. Each node represents an operation: scanning a table, joining two sets, sorting, aggregating.

Here is a minimal example:

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'alice@example.com';

Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=72)
  (actual time=0.027..0.029 rows=1 loops=1)
  Index Cond: (email = 'alice@example.com'::text)
Planning Time: 0.085 ms
Execution Time: 0.052 ms

Breaking down each field

When actual rows diverge significantly from estimated rows, it usually means the planner's statistics are stale. Run ANALYZE <table> to refresh them.

Common Node Types in PostgreSQL Query Plans

Understanding the node types you will encounter most often is essential for effective optimization.

Scan nodes (data access)

Seq Scan -- Sequential scan. PostgreSQL reads every row in the table. Fine for small tables or queries that need most rows. A red flag on large tables with selective filters.

Index Scan -- Uses a B-tree (or other) index to find matching rows, then fetches the heap tuple. Fast for selective queries.

Index Only Scan -- The index contains all columns needed by the query. No heap fetch required. The fastest scan type.

Bitmap Index Scan + Bitmap Heap Scan -- Builds a bitmap of matching pages from the index, then fetches them from the heap in physical order. Common when selectivity is moderate (too many rows for a plain index scan, too few for a seq scan).

Join nodes

Nested Loop -- For each row in the outer set, scans the inner set. Efficient when the outer set is small and the inner set has an index. Disastrous when both sets are large.

Hash Join -- Builds a hash table from the smaller relation, then probes it with the larger one. Good for equi-joins on medium to large data sets.

Merge Join -- Both inputs must be sorted on the join key. Efficient for large pre-sorted sets and can leverage existing indexes.

Other operations

Sort -- Sorts rows, often for ORDER BY, MERGE JOIN, or DISTINCT. Watch the sort method: quicksort in memory is fast; external merge means the sort spilled to disk.

HashAggregate / GroupAggregate -- Used for GROUP BY operations.

Limit -- Stops execution after the specified number of rows.

Real-World Examples with EXPLAIN ANALYZE

Example 1: Spotting a full table scan

Suppose you have an orders table with 5 million rows:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 8821;

Seq Scan on orders  (cost=0.00..125432.00 rows=47 width=96)
  (actual time=892.113..1543.207 rows=52 loops=1)
  Filter: (customer_id = 8821)
  Rows Removed by Filter: 4999948
Planning Time: 0.091 ms
Execution Time: 1543.289 ms

The problem is immediately visible: PostgreSQL scanned all 5 million rows (Rows Removed by Filter: 4999948) to find just 52 matches. Execution took over 1.5 seconds.

Fix: Create an index on the filter column.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Re-running the same query:

Index Scan using idx_orders_customer_id on orders  (cost=0.43..196.12 rows=47 width=96)
  (actual time=0.031..0.187 rows=52 loops=1)
  Index Cond: (customer_id = 8821)
Planning Time: 0.112 ms
Execution Time: 0.214 ms

From 1,543 ms to 0.2 ms. The index reduced the cost by over 7,000x.

Example 2: Diagnosing a bad join strategy

Consider a query joining orders with line items:

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.created_at, li.product_id, li.quantity
FROM orders o
JOIN line_items li ON li.order_id = o.id
WHERE o.status = 'pending'
  AND o.created_at > '2026-01-01';

Nested Loop  (cost=1.00..98234.56 rows=2340 width=36)
  (actual time=0.045..4231.882 rows=2287 loops=1)
  ->  Seq Scan on orders o  (cost=0.00..145678.00 rows=2340 width=20)
        (actual time=0.031..3412.009 rows=2287 loops=1)
        Filter: ((status = 'pending') AND (created_at > '2026-01-01'))
        Rows Removed by Filter: 4997713
  ->  Index Scan using idx_line_items_order_id on line_items li
        (cost=0.43..3.12 rows=3 width=16)
        (actual time=0.008..0.011 rows=3 loops=2287)
        Index Cond: (order_id = o.id)
Planning Time: 0.203 ms
Execution Time: 4232.104 ms

The Nested Loop itself is appropriate here -- the inner side uses an index. The real bottleneck is the Seq Scan on orders: it reads all 5 million rows to filter down to 2,287.

Fix: A composite index on the filter columns.

CREATE INDEX idx_orders_status_created ON orders (status, created_at);

After the index:

Nested Loop  (cost=1.14..7892.33 rows=2340 width=36)
  (actual time=0.052..18.443 rows=2287 loops=1)
  ->  Index Scan using idx_orders_status_created on orders o
        (cost=0.56..892.10 rows=2340 width=20)
        (actual time=0.038..4.112 rows=2287 loops=1)
        Index Cond: ((status = 'pending') AND (created_at > '2026-01-01'))
  ->  Index Scan using idx_line_items_order_id on line_items li
        (cost=0.43..3.12 rows=3 width=16)
        (actual time=0.005..0.008 rows=3 loops=2287)
        Index Cond: (order_id = o.id)
Planning Time: 0.215 ms
Execution Time: 18.881 ms

From 4.2 seconds to 19 milliseconds -- a 224x improvement.

Example 3: Row estimate mismatch causing wrong plan

Sometimes the planner picks a poor strategy because its statistics are wrong:

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.plan_type = 'enterprise'
GROUP BY u.name
ORDER BY COUNT(o.id) DESC;

Sort  (cost=345678.12..345678.45 rows=132 width=40)
  (actual time=8921.334..8921.341 rows=8 loops=1)
  Sort Key: (count(o.id)) DESC
  Sort Method: quicksort  Memory: 25kB
  ->  HashAggregate  (cost=345672.00..345674.64 rows=132 width=40)
        (actual time=8921.298..8921.310 rows=8 loops=1)
        Group Key: u.name
        ->  Hash Join  (cost=12.44..345123.00 rows=109800 width=40)
              (actual time=0.112..8918.223 rows=847 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (cost=0.00..125432.00 rows=5000000 width=12)
                    (actual time=0.009..4102.008 rows=5000000 loops=1)
              ->  Hash  (cost=11.75..11.75 rows=55 width=36)
                    (actual time=0.076..0.077 rows=8 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
                    ->  Seq Scan on users u  (cost=0.00..11.75 rows=55 width=36)
                          (actual time=0.011..0.068 rows=8 loops=1)
                          Filter: (plan_type = 'enterprise')
                          Rows Removed by Filter: 342
Planning Time: 0.301 ms
Execution Time: 8921.502 ms

The planner estimated 55 enterprise users and 109,800 matching orders. Reality: 8 users, 847 orders. The Hash Join strategy scanned all 5 million orders. With better stats, PostgreSQL could push down the filter and use a Nested Loop with index lookups on orders.

Fix:

ANALYZE users;
ANALYZE orders;

After refreshing statistics, PostgreSQL chose a Nested Loop with index scans, dropping execution time to under 5 ms.

Example 4: Sort spilling to disk

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
ORDER BY created_at DESC
LIMIT 100;

Limit  (cost=892345.12..892345.37 rows=100 width=128)
  (actual time=12432.112..12432.145 rows=100 loops=1)
  ->  Sort  (cost=892345.12..917345.12 rows=10000000 width=128)
        (actual time=12432.108..12432.131 rows=100 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 41kB
        Buffers: shared hit=5234 read=162345
        ->  Seq Scan on events  (cost=0.00..423456.00 rows=10000000 width=128)
              (actual time=0.012..8923.334 rows=10000000 loops=1)
              Buffers: shared hit=5234 read=162345
Planning Time: 0.078 ms
Execution Time: 12432.203 ms

Even though we only need 100 rows, PostgreSQL has to scan and sort the entire table because there is no index on created_at.

Fix:

CREATE INDEX idx_events_created_at_desc ON events (created_at DESC);

After the index, PostgreSQL can do an Index Scan and stop after 100 rows -- execution time drops to under 1 ms.

How to Spot Performance Problems in Query Plans

After reviewing hundreds of query plans, patterns emerge. Here is what to look for:

1. Seq Scan on large tables with selective filters

If Rows Removed by Filter is orders of magnitude larger than the rows returned, you likely need an index.

2. Actual rows far from estimated rows

When estimates are off by 10x or more, PostgreSQL may choose the wrong join strategy or access method. Run ANALYZE on the relevant tables, or look into extended statistics (CREATE STATISTICS) for correlated columns.

3. Nested Loop with high loop counts and no inner index

A Nested Loop executing thousands of times against an inner Seq Scan is a combinatorial explosion. Either add an index on the inner table's join column, or restructure the query so PostgreSQL can use a Hash Join.

4. Sort spilling to disk

If you see Sort Method: external merge Disk: ..., the sort exceeded work_mem. Consider increasing work_mem for the session, adding an index that provides the required sort order, or reducing the result set with tighter filters.

5. Excessive buffer reads

With BUFFERS enabled, high read= counts (as opposed to hit=) indicate cold cache or excessive I/O. This often points to missing indexes or queries that touch far more data than necessary.

Visualizing Query Plans: Why Text Output Falls Short

Textual EXPLAIN output works for simple queries. But once you have a plan with 15+ nodes, multiple joins, subqueries, and CTEs, reading indented text becomes an exercise in frustration. You lose track of which branch you are in, you cannot easily compare estimated vs. actual rows, and the cost breakdown is buried in numbers.

This is where visual query plan tools earn their keep.

QueryDeck includes a built-in visual EXPLAIN ANALYZE feature designed specifically for this problem. When you run EXPLAIN ANALYZE inside QueryDeck, the plan is rendered as an interactive node graph where each operation is color-coded:

• Green nodes -- execution time and row estimates are healthy.
• Amber nodes -- something deserves attention (moderate deviation or cost).
• Red nodes -- a clear bottleneck that needs investigation.

Because QueryDeck is a native macOS application, the visualization renders instantly without the overhead of a browser-based tool. You can click into any node to see buffer stats, filter conditions, and the exact time breakdown.

Other tools that help visualize query plans include explain.dalibo.com (web-based, paste your plan) and pgAdmin's built-in graphical explain. Each has tradeoffs -- web tools require copying plan text out of your terminal, and pgAdmin bundles a full admin UI you may not need.

Practical Optimization Checklist

Use this checklist every time you investigate a slow query with EXPLAIN ANALYZE:

• [ ] Run with BUFFERS. Always use EXPLAIN (ANALYZE, BUFFERS) to see I/O behavior.
• [ ] Check for Seq Scans on large tables. If the filter is selective, add an index.
• [ ] Compare estimated vs. actual rows. Mismatches above 10x warrant a fresh ANALYZE or extended statistics.
• [ ] Look at loop counts in Nested Loops. High loop counts with inner Seq Scans are a red flag.
• [ ] Check sort method. Disk sorts mean you need more work_mem or a supporting index.
• [ ] Review join order. PostgreSQL usually gets this right, but if the smaller table is not driving the join, check statistics.
• ] **Use a visual tool.** Complex plans are much easier to diagnose with a [visual query plan viewer than with raw text.
• [ ] Test your fix. After adding an index or rewriting the query, run EXPLAIN ANALYZE again and compare.

Beyond EXPLAIN ANALYZE: Continuous PostgreSQL Performance Tuning

EXPLAIN ANALYZE is a diagnostic tool -- it tells you what is happening *now*. For ongoing PostgreSQL performance tuning, you also need:

• pg_stat_statements -- Tracks cumulative query statistics across all executions. Essential for finding the queries that consume the most total time.
• auto_explain -- Logs execution plans for queries that exceed a time threshold. Useful for catching regressions in production.
• Regular ANALYZE runs -- Ensure autovacuum is configured to keep table statistics fresh.
• A good GUI for exploration -- A dedicated PostgreSQL client for Mac that integrates EXPLAIN ANALYZE into your workflow removes friction and makes performance work a habit rather than a chore.

Try QueryDeck's Visual EXPLAIN ANALYZE -- Free for 14 Days

Stop squinting at indented text output. QueryDeck renders your PostgreSQL query plans as interactive, color-coded node graphs -- right on your Mac, with zero browser overhead. Green means go, red means fix it.

Start your free trial and see your query plans clearly for the first time.