🧠 What is EXPLAIN in PostgreSQL?

My articles are open to everyone; non-member readers can read the full article by clicking this link

• EXPLAIN is used to understand the execution plan PostgreSQL will follow for a SQL query.
• It does not execute the query but estimates what the planner will do.
• Useful for performance tuning, understanding indexes, row width, and costs.

🧪 Example Table

• Table: grades
• Columns: id, grade (g), name
• Indexed on: id and grade, not on name
• Contains over 200 million rows

🔍 Key EXPLAIN Elements

1. Sequential Scan

EXPLAIN SELECT * FROM grades;

• Type: Seq Scan (full table scan)
• Postgres scans every row.
• Shown as:

Seq Scan on grades  (cost=0.00..289000.00 rows=200000000 width=31)

cost=0.00..289000.00

• Startup cost (first number): Estimated cost to return the first row.
• Total cost (second number): Estimated total cost to return all rows.
• Measured in arbitrary units, usually milliseconds.