How do you read and interpret a PostgreSQL EXPLAIN ANALYZE output to identify query performance bottlenecks?

Question

Accepted Answer

Reading execution plans is one of the most critical skills for a mid-level DBA focused on performance tuning. EXPLAIN ANALYZE in PostgreSQL executes the query and provides detailed timing and row count information for each step in the execution plan, allowing you to pinpoint exactly where time is being spent.

The output is a tree of plan nodes, read from the innermost node outward and from bottom to top. Each node represents an operation like a sequential scan, index scan, hash join, or sort. For each node, the plan shows the estimated cost as a range from startup cost to total cost, the estimated number of rows, the actual time in milliseconds, and the actual number of rows processed. Comparing estimated versus actual row counts is one of the most important diagnostics. When the estimates are significantly different from actual values, it indicates stale or inaccurate statistics, which causes the optimizer to choose suboptimal execution strategies.

Sequential scans, shown as Seq Scan, read every row in a table. For small tables this is efficient, but for large tables it is usually a sign that an appropriate index is missing. When you see a Seq Scan on a table with millions of rows and the query filters on a specific column, creating an index on that column often transforms the Seq Scan into an Index Scan with dramatic performance improvement.

Index Scan and Index Only Scan are generally what you want to see for selective queries on large tables. An Index Only Scan is even better because it retrieves all needed data from the index without accessing the table heap, but it requires that all columns in the SELECT list are included in the index and that the visibility map is up to date through regular VACUUM operations.

Join algorithms include Nested Loop, Hash Join, and Merge Join. Nested Loop is efficient when the inner relation is small or has a fast index lookup. Hash Join is efficient for larger datasets where a hash table can be built in memory. Merge Join works well when both inputs are already sorted on the join key. An unexpected join algorithm often indicates a statistics problem. If the optimizer estimates a small table but the actual table is large, it may choose Nested Loop when Hash Join would be far more appropriate.

Sort operations are expensive for large datasets. If a sort shows a sort method of external merge, it means the data did not fit in work_mem and spilled to disk, which is much slower. Increasing work_mem for the session or adding an index that pre-sorts the data can eliminate disk-based sorts.

The Buffers section, shown when you use EXPLAIN (ANALYZE, BUFFERS), reveals the I/O behavior. Shared hit indicates pages read from the buffer cache, while shared read indicates pages read from disk. A high ratio of reads to hits suggests the buffer cache is too small or the query is scanning too much data. Temp read and temp written indicate disk spillover for sorts and hash operations.

When investigating a slow query, focus on the nodes with the highest actual time, check for large discrepancies between estimated and actual row counts, look for sequential scans on large tables, identify disk spills in sort and hash operations, and verify that statistics are current by running ANALYZE on the involved tables.