How to explain a query plan in PostgreSQL

When a PostgreSQL query is slow, unexpectedly broad, or hard to tune, the plan shows whether the database expects to scan a table, use an index, sort rows, join repeatedly, or read more data than the query should need.

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

EXPLAIN by itself does not execute the statement. EXPLAIN (ANALYZE, BUFFERS) runs the statement and reports actual timing, row counts, and buffer activity, so it should be used carefully on production systems and wrapped in a rollback transaction before checking write statements.

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

   $ sudo -u postgres psql -d appdb -c "EXPLAIN SELECT id, status FROM orders WHERE customer_id = 42;"
                                         QUERY PLAN                                       
   ---------------------------------------------------------------------------------------
    Bitmap Heap Scan on orders  (cost=5.06..84.23 rows=100 width=12)
      Recheck Cond: (customer_id = 42)
      ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..5.04 rows=100 width=0)
            Index Cond: (customer_id = 42)
   (4 rows)

   Replace appdb and the sample SQL with the target database and statement. The first line is the top node, and indented lines are work done underneath it.

2. Run the query with EXPLAIN (ANALYZE, BUFFERS) when actual row counts and buffer activity are needed.

   $ sudo -u postgres psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id, status FROM orders WHERE customer_id = 42;"
                                                                 QUERY PLAN                                                              
   --------------------------------------------------------------------------------------------------------------------------------------
    Bitmap Heap Scan on orders  (cost=5.06..84.23 rows=100 width=12) (actual time=0.129..0.584 rows=100.00 loops=1)
      Recheck Cond: (customer_id = 42)
      Heap Blocks: exact=74
      Buffers: shared hit=74 read=2
      ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..5.04 rows=100 width=0) (actual time=0.095..0.095 rows=100.00 loops=1)
            Index Cond: (customer_id = 42)
            Index Searches: 1
            Buffers: shared read=2
    Planning:
      Buffers: shared hit=83
    Planning Time: 0.443 ms
    Execution Time: 0.651 ms
   (12 rows)

   Running with ANALYZE executes the statement and can compete with application queries on large tables.

3. Capture a runtime 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 = 1234; ROLLBACK;"
   BEGIN
                                                             QUERY PLAN                                                          
   ------------------------------------------------------------------------------------------------------------------------------
    Update on orders  (cost=0.29..8.30 rows=0 width=0) (actual time=0.226..0.226 rows=0.00 loops=1)
      Buffers: shared hit=11 read=1 dirtied=1
      ->  Index Scan using orders_pkey on orders  (cost=0.29..8.30 rows=1 width=38) (actual time=0.023..0.025 rows=1.00 loops=1)
            Index Cond: (id = 1234)
            Index Searches: 1
            Buffers: shared hit=3
    Planning:
      Buffers: shared hit=77
    Planning Time: 0.469 ms
    Execution Time: 0.315 ms
   (10 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 on nodes with the highest runtime.

   The actual time= range is startup-to-total time per loop, and loops shows how many times that node ran.

5. Check buffer and node signals before changing SQL or indexes.

   Buffers: shared hit means pages came from PostgreSQL cache, read means pages were read from storage, and dirtied means the statement changed buffers. A Seq Scan can be expected for low-selectivity predicates, while disk-based Sort and multi-batch Hash nodes often point to memory pressure or missing index order.

   Related: How to tune work_mem in PostgreSQL

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 or adjust indexes only when the plan shows filters, joins, or ordering that cannot use an efficient access path.

   Indexes help 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 after each change and compare the top node, row estimates, buffer reads, and execution time against the earlier plan.