Distinguish planning from execution
EXPLAIN shows the operations PostgreSQL has selected for a query: scans, joins, sorting, and aggregation form a tree. Adding ANALYZE executes the statement and records observations. Begin with a small SELECT whose effects and workload you understand. The command is not merely a viewer when execution is enabled.
Estimated cost is a planning unit, not elapsed milliseconds. Record the SQL, parameter values, data volume, server settings, and relevant load before comparing alternatives. A change in any of these conditions can explain a different plan without demonstrating that an optimization improved the original workload.
Find where estimates diverge
Read upward from the lower nodes and compare estimated rows with actual rows. Suppose a filter was expected to return one row but actually returned twenty-four. Ask whether skewed values, correlated columns, or stale statistics explain that difference before deciding that another index is required.
For a repeatedly executed node, actual rows and node times are averages per execution. Four actual rows with one hundred loops suggest about four hundred output rows in total. Parent timing includes child execution, so adding every node time double-counts work. Identify the scope of a number before performing arithmetic.
Use buffers to test a hypothesis
The following query illustrates a customer lookup; it makes no claim about the plan or speed on a particular database. TIMING OFF avoids individual node clock measurements while retaining useful row-count and overall execution information. That can help reduce measurement overhead when detailed timing is unnecessary.
Shared hits refer to accesses satisfied by PostgreSQL shared buffers; shared reads refer to blocks loaded into them. A shared read does not prove that a physical disk access occurred, because operating-system caches may participate. Combine the plan with applicable I/O timing and workload observations rather than equating a counter with one specific bottleneck.
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, total
FROM orders
WHERE customer_id = 42;Keep the comparison controlled
Repeat measurements under documented conditions and preserve variation. A warm second run can differ from a cold first run without a SQL change. Investigate the inputs and waits associated with slow executions instead of reporting only an average or assuming that the fastest single run represents ordinary behavior.
ANALYZE also executes modifying statements. Transactions can undo many database changes, but rollback does not erase every possible effect, including some sequence or external effects. Establish the scope of execution before testing. A plan supports a causal hypothesis; it does not by itself prove which intervention will improve the service.