How do you interpret PostgreSQL `EXPLAIN (ANALYZE, BUFFERS)` query execution plans?

`EXPLAIN (ANALYZE, BUFFERS)` provides a detailed, actual runtime analysis of a query's execution plan, including crucial buffer usage statistics, enabling precise identification of performance bottlenecks and I/O inefficiencies. It executes the query and reports real-world metrics, contrasting them with the planner's estimates. ### Key Metrics to Examine * **`actual time` (ms):** The real time taken by a node, split into startup (first row returned) and total (all rows returned). High total `actual time` indicates a slow operation. * **`rows` vs `actual rows`:** `rows` is the planner's estimated output cardinality; `actual rows` is the real count. Significant discrepancies (e.g., `rows` much less than `actual rows`) point to inaccurate statistics, leading to suboptimal plan choices. * **`loops`:** How many times a plan node was executed. For nested loops, this can be high. * **`Buffers`:** * `shared hit`: Blocks found in shared buffer cache (no disk I/O). * `shared read`: Blocks read from disk into shared buffer cache. High values indicate significant disk I/O. * `local hit`/`local read`: Similar to `shared`, but for temporary tables/indexes. * `dirty`: Blocks modified in cache. * `written`: Dirty blocks flushed to disk. ### Interpreting Bottlenecks 1. **High `actual time` on a node:** This is the primary indicator of a bottleneck. Trace upwards from the highest `actual time` node to understand its input and dependencies. 2. **`rows` vs `actual rows` Mismatch:** If `actual rows` is much higher than `rows` for an inner loop or join, the planner likely underestimated the data, choosing a less efficient join strategy (e.g., Nested Loop instead of Hash Join). Run `ANALYZE ` to update statistics. 3. **`Buffers: shared read` Dominance:** Many `shared read` buffers suggest heavy disk I/O. Consider adding indexes for `WHERE` clauses, `JOIN` conditions, or `ORDER BY` clauses to enable Index Scans. Optimizing the query to retrieve fewer columns or rows can also help. 4. **`Seq Scan` on large tables:** If a `Seq Scan` has high `actual time` and `shared read` buffers, an index is likely missing for the filtering condition. 5. **`Sort` operations:** These are resource-intensive. If `Sort` appears with high `actual time`, check if an index could satisfy the `ORDER BY` or `DISTINCT` requirement. 6. **Expensive Joins:** `Hash Join` or `Merge Join` with high `actual time` might indicate large intermediate result sets, especially if `work_mem` is insufficient, leading to disk spills. ### Example Command ```sql EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT p.product_name, SUM(oi.quantity * oi.price_per_unit) AS total_sales FROM products p JOIN order_items oi ON p.product_id = oi.product_id WHERE p.category = 'Electronics' GROUP BY p.product_name ORDER BY total_sales DESC LIMIT 10; ``` This command will provide a JSON output, which is often easier to parse and visualize with tools like `pev.dev` or `explain.depesz.com`.