Reading EXPLAIN ANALYZE in PostgreSQL for Beginners

1. Overview

EXPLAIN ANALYZE is the most important tool for diagnosing slow queries in PostgreSQL. This article helps you understand each part of the output: node type, cost, rows, actual time, and know when a query has a problem. After reading, you’ll confidently read execution plans and know when to add an index or fix the query.

2. EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN only shows the estimated plan based on statistics, without executing the query. EXPLAIN ANALYZE actually runs the query and measures the actual time.

-- Only view the estimated plan, don't run the query
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Actually run and measure real time
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Note: EXPLAIN ANALYZE runs the actual query, so if it’s an UPDATE or DELETE, data will be changed. Use it in a transaction and ROLLBACK if you only want to test:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'SHIPPED' WHERE id = 1;
ROLLBACK;

3. Structure of a node in the plan

Each line in the output is a node — a step in data processing. Example with an orders table:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=8 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 99992
Planning Time: 0.120 ms
Execution Time: 12.465 ms

Explanation of each part:

• Seq Scan on orders: node type, here is a full table sequential scan.
• cost=0.00..1834.00: estimated cost (startup cost .. total cost), a relative unit, not milliseconds.
• rows=10: estimated number of rows the planner thinks will be returned.
• width=72: average size of each row in bytes.
• actual time=0.015..12.430: actual time in milliseconds — starting to return the first row .. completing the entire node.
• rows=8 loops=1: actual number of rows returned, and how many times this node was executed (loops).
• Rows Removed by Filter: number of rows scanned but not matching the filter — a large number here is a sign of a missing index.

4. Comparing Seq Scan and Index Scan

4.1. When there’s no index

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=8 loops=1)
  Filter: (customer_id = 42)
Execution Time: 12.465 ms

PostgreSQL must read the entire table and then filter each row. With large tables, this is the main cause of slowness.

4.2. After adding an index

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer_id on orders
  (cost=0.29..8.31 rows=10 width=72)
  (actual time=0.018..0.025 rows=8 loops=1)
  Index Cond: (customer_id = 42)
Execution Time: 0.045 ms

Comparison: cost drops from 1834 to 8.31, execution time drops from 12.465 ms to 0.045 ms. This is a classic example showing how an index helps PostgreSQL jump directly to the needed rows, without scanning the entire table.

Note: PostgreSQL doesn’t always use an index even if one exists. If the query returns most of the table (for example, > 20-30% of rows), the planner usually chooses Seq Scan because it’s cheaper than jumping randomly through the index and back to the table (random I/O).

5. Reading a plan with JOIN

When a query has a join, the plan will have multiple nested nodes. Child nodes execute first, and their results are passed up to the parent node.

EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'VN';

Hash Join  (cost=45.50..520.75 rows=200 width=40)
           (actual time=1.200..8.900 rows=195 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o
        (cost=0.00..420.00 rows=10000 width=12)
        (actual time=0.010..3.500 rows=10000 loops=1)
  ->  Hash  (cost=40.00..40.00 rows=440 width=36)
            (actual time=1.100..1.100 rows=440 loops=1)
        ->  Seq Scan on customers c
              (cost=0.00..40.00 rows=440 width=36)
              (actual time=0.005..0.700 rows=440 loops=1)
              Filter: (country = 'VN'::text)
Execution Time: 9.050 ms

How to read: Hash Join is the outermost node (root), receiving data from two child nodes (Seq Scan on orders and Hash containing Seq Scan on customers). PostgreSQL builds a hash table from the filtered customers table, then scans orders and matches it into the hash table. The actual time of the parent node already includes the time of the child nodes inside it.

6. Warning signs to watch for

6.1. Estimate is way off from actual

Seq Scan on orders  (cost=0.00..1834.00 rows=10 width=72)
                     (actual time=0.015..12.430 rows=9500 loops=1)

The planner predicted rows=10 but actually returned 9500. Such a large discrepancy is usually due to outdated statistics. Fix it with:

ANALYZE orders;

This command updates the statistics of the data distribution so the planner can estimate more accurately.

6.2. Unusually large loops (Nested Loop)

Nested Loop  (cost=0.43..5000.00 rows=1 width=40)
             (actual time=0.020..450.000 rows=1 loops=1)
  ->  Seq Scan on customers c (actual time=0.010..0.015 rows=1 loops=1)
  ->  Index Scan on orders o
        (actual time=0.002..0.450 rows=1 loops=1000)

loops=1000 means the Index Scan on orders node was called 1000 times (corresponding to 1000 rows from the outer node). If each call costs significant time, the total cost will be very large even though the actual time of the node looks small on its own. Multiply actual time by loops to estimate the total actual time spent in that node.

6.3. Use BUFFERS to see I/O

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer_id on orders
  (cost=0.29..8.31 rows=10 width=72)
  (actual time=0.018..0.025 rows=8 loops=1)
  Index Cond: (customer_id = 42)
  Buffers: shared hit=4
Execution Time: 0.045 ms

shared hit=4 means 4 blocks were read from cache (memory), not from disk. If you see large shared read values, it means data had to be read from disk — a sign of cache miss, possibly due to shared_buffers being too small or the query scanning too much cold data.

7. Using EXPLAIN in Java/Spring code

When debugging performance in a Spring Boot application, you can log the plan directly through JdbcTemplate:

@Service
public class QueryDiagnosticsService {

    private final JdbcTemplate jdbcTemplate;

    public QueryDiagnosticsService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<String> explainAnalyze(String sql) {
        String query = "EXPLAIN (ANALYZE, FORMAT TEXT) " + sql;
        return jdbcTemplate.queryForList(query, String.class);
    }
}

explainAnalyze takes the original SQL, wraps it with EXPLAIN (ANALYZE, FORMAT TEXT), and returns each line of the plan as a String. Use it in dev/staging environments to log the plan when a slow query is detected.

@Test
void explainAnalyze_shouldReturnPlanWithExecutionTime() {
    QueryDiagnosticsService service = new QueryDiagnosticsService(jdbcTemplate);

    List<String> plan = service.explainAnalyze(
        "SELECT * FROM orders WHERE customer_id = 42"
    );

    assertThat(plan)
        .isNotEmpty()
        .anyMatch(line -> line.contains("Execution Time"));
}

This test doesn’t assert specific numbers (since execution time varies by machine), just confirms that the plan returned contains the Execution Time line — enough to verify that the statement executed successfully and returned a valid plan.

Note: Don’t run EXPLAIN ANALYZE on production for INSERT/UPDATE/DELETE queries without wrapping them in a transaction with ROLLBACK, as it will actually execute them.

8. When NOT to use EXPLAIN ANALYZE

• Query is already fast and stable (under a few ms, little data) — no need to optimize further, effort not worth it.
• Still in early schema design phase, don’t have enough real data for statistics to reflect reality correctly (few rows always makes Seq Scan beat Index Scan).
• Query runs on a small temporary table (TEMP TABLE) only once — overhead of analysis not worth the benefit.

9. Conclusion

EXPLAIN ANALYZE shows you the difference between what PostgreSQL thinks will happen (cost, estimated rows) and what actually happens (actual time, real rows). When reading a plan, pay attention to the execution order (from inner to outer), node type (Seq Scan or Index Scan), and especially the gap between estimate and actual — that’s the key to knowing whether the query needs an index, needs ANALYZE run again on the table, or needs the logic rewritten.