How to explain a query plan in PostgreSQL

Explaining a query plan exposes how PostgreSQL intends to execute a SQL statement, making slow-query work less like folklore and more like diagnosis.

PostgreSQL uses a cost-based planner to choose scan methods, join algorithms, and row ordering based on table statistics, available indexes, and configuration such as work_mem. EXPLAIN prints the chosen plan as a tree of nodes, where cost= and rows= are estimates and width= is an estimated average row size.

EXPLAIN does not execute the statement, but EXPLAIN ANALYZE runs it and reports actual timing and row counts that can be compared to estimates. Running EXPLAIN ANALYZE on production can compete with application workloads and can execute writes, so scope and impact should be understood before collecting runtime plans.

1. Print the estimated plan for a query without executing it.

   $ sudo -u postgres psql -d appdb -c "EXPLAIN SELECT * FROM orders WHERE customer_id = 42;"
                                         QUERY PLAN
   --------------------------------------------------------------------------------------
    Bitmap Heap Scan on orders  (cost=5.05..285.11 rows=98 width=36)
      Recheck Cond: (customer_id = 42)
      ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..5.03 rows=98 width=0)
            Index Cond: (customer_id = 42)
   (4 rows)

   Replace appdb and the sample SQL with the target database and statement.

2. Run the query with EXPLAIN (ANALYZE, BUFFERS) to capture actual timing and buffer usage.

   $ sudo -u postgres psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;"
                                                              QUERY PLAN
   ---------------------------------------------------------------------------------------------------------------------------------
    Bitmap Heap Scan on orders  (cost=5.05..285.11 rows=98 width=36) (actual time=0.020..0.217 rows=94 loops=1)
      Recheck Cond: (customer_id = 42)
      Heap Blocks: exact=86
      Buffers: shared hit=91
      ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..5.03 rows=98 width=0) (actual time=0.011..0.012 rows=94 loops=1)
            Index Cond: (customer_id = 42)
            Buffers: shared hit=5
    Planning:
      Buffers: shared hit=89
    Planning Time: 0.149 ms
    Execution Time: 0.244 ms
   (11 rows)

   Running with ANALYZE executes the statement and can be expensive on large datasets.

3. Capture a plan for write statements inside a transaction that ends with ROLLBACK.

   $ sudo -u postgres psql -d appdb -c "BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'shipped' WHERE id = 12345; ROLLBACK;"
   BEGIN
                                                           QUERY PLAN
   ---------------------------------------------------------------------------------------------------------------------------
    Update on orders  (cost=0.29..8.31 rows=0 width=0) (actual time=0.104..0.104 rows=0 loops=1)
      Buffers: shared hit=26 dirtied=1
      ->  Index Scan using orders_pkey on orders  (cost=0.29..8.31 rows=1 width=38) (actual time=0.022..0.022 rows=1 loops=1)
            Index Cond: (id = 12345)
            Buffers: shared hit=6
    Planning:
      Buffers: shared hit=77
    Planning Time: 0.379 ms
    Execution Time: 0.354 ms
   (9 rows)
   
   ROLLBACK

   ROLLBACK reverts table changes but does not undo all side effects, including sequence increments from nextval().

4. Compare the estimated rows= values to the actual rows= values for the nodes with the highest runtime.

   The actual time= range is startup-to-total time per node, and loops multiplies the work done by child nodes.

5. Flag plan nodes that show avoidable work, including Seq Scan on large tables, Nested Loop with large loops, and Sort or Hash nodes that spill to disk.

   Seq Scan can be normal for low-selectivity predicates, while disk-based Sort and multi-batch Hash often point to memory pressure or missing index order.

6. Refresh table statistics when estimates are consistently far from actuals.

   $ sudo -u postgres psql -d appdb -c "ANALYZE orders;"
   ANALYZE

   Targeted ANALYZE on involved tables is often enough to correct row-count estimates without changing the query.

   Related: How to vacuum and analyze tables in PostgreSQL

7. Create indexes when filters, joins, or ordering cannot use efficient access paths.

   Indexes pay off most when a predicate is selective, a join uses a stable key, or an ORDER BY can be satisfied directly from index order.

   Related: How to create an index in PostgreSQL

8. Re-run EXPLAIN (ANALYZE, BUFFERS) for the same statement to confirm changes in the plan match expectations.

   $ sudo -u postgres psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;"
                                                              QUERY PLAN
   ---------------------------------------------------------------------------------------------------------------------------------
    Bitmap Heap Scan on orders  (cost=5.06..287.62 rows=99 width=36) (actual time=0.021..0.214 rows=94 loops=1)
      Recheck Cond: (customer_id = 42)
      Heap Blocks: exact=86
      Buffers: shared hit=91
      ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..5.04 rows=99 width=0) (actual time=0.011..0.011 rows=94 loops=1)
            Index Cond: (customer_id = 42)
            Buffers: shared hit=5
    Planning:
      Buffers: shared hit=97
    Planning Time: 0.169 ms
    Execution Time: 0.243 ms
   (11 rows)