How to Produce an EXPLAIN Plan in PostgreSQL

Purpose

The EXPLAIN command in PostgreSQL is a powerful tool for understanding how the query planner and executor work. It provides insight into query execution strategies, helping identify performance issues such as sequential scans, unnecessary sorts, or inefficient joins.

For performance analysis, the most effective form of EXPLAIN is:

EXPLAIN (ANALYZE, BUFFERS, TIMING) <your_sql_statement>;

This document explains how and why to use this form.

🛠️ Basic Usage

You can start with a simple EXPLAIN to see the query plan:

EXPLAIN SELECT * FROM gd_customer WHERE customer_id = 123;

⚠️ This does not execute the query — it only shows the planner's estimate.

For accurate performance diagnostics and to communicate with support / professional services, you need more detail.

✅ Recommended: Use ANALYZE, BUFFERS, TIMING

EXPLAIN (ANALYZE, BUFFERS, TIMING)
<your complicated select, insert, update, delete or merge statement>;

Parameter Breakdown:

ANALYZE
- Executes the query and shows actual row counts and execution times.
- Essential to compare planner estimates vs. reality.
BUFFERS
- Displays buffer usage (shared/local/temp).
- Helps diagnose I/O pressure and memory efficiency.
TIMING
- Shows how much time each node takes.
- Shows how parsing and actual execution contribute to overall execution time.

📈 Why Use All Three Parameters?

Parameter	Why It Matters

Parameter	Why It Matters
`ANALYZE`	See real execution stats, not just estimates.
`BUFFERS`	Understand memory vs disk usage and caching behavior.
`TIMING`	Identify which parts of the query are slow.

Using all three provides visibility into:

Planner accuracy ( you may have insufficient / bad statistics )
I/O vs CPU bottlenecks
Index or join strategy effectiveness

💡 How to share an EXPLAIN Plan

Always share the raw output of the EXPLAIN command (screenshots are not easily readable and will only slow down the analysis process
Don’t use the EXPLAIN button available in dBeaver or your favorite SQL client
Usehttps://explain.depesz.com (EXPLAIN visualizer, provides a shareable URL, and lets you add the query text and comments if need be)