How to EXPLAIN ANALYZE in PostgreSQL

Galaxy Glossary

How do I use EXPLAIN ANALYZE to debug slow PostgreSQL queries?

EXPLAIN ANALYZE executes a query, then returns the planner’s execution plan plus real-time statistics so you can find and fix slow steps.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why use EXPLAIN ANALYZE?

EXPLAIN ANALYZE runs the statement and prints the chosen execution plan with actual timing, row counts, and loops, letting you spot bottlenecks immediately.

How do I run it?

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM Customers;

The ANALYZE flag adds runtime stats; BUFFERS shows shared/local/temp hits and reads.

What do the columns mean?

Every plan node lists estimated cost, estimated rows, actual rows, loops, and time.Large gaps between estimates and actuals signal outdated statistics or poor indexes.

When should I add FORMAT JSON?

FORMAT JSON returns a machine-readable plan that works well with visualizers such as pgAdmin or explain.depesz.com.

Example: optimize a 30-day revenue report

EXPLAIN (ANALYZE, BUFFERS) SELECT c.name, SUM(oi.quantity * p.price) AS total_spent FROM Customers c JOIN Orders o ON o.customer_id = c.id JOIN OrderItems oi ON oi.order_id = o.id JOIN Products p ON p.id = oi.product_id WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY c.name ORDER BY total_spent DESC LIMIT 10;

If the plan shows a sequential scan on Orders, create an index on (order_date) or a composite index with customer_id.

Best practices for plan analysis

1) Test on production-like data.2) Compare estimates to actuals. 3) Use auto_explain for automatic capture of slow plans. 4) Validate statistics with ANALYZE.

What does EXPLAIN VERBOSE add?

VERBOSE displays internal target-list and predicate details, useful for diagnosing sub-query push-down or CTE inlining.

.

Why How to EXPLAIN ANALYZE in PostgreSQL is important

How to EXPLAIN ANALYZE in PostgreSQL Example Usage


-- Identify products with low stock hit frequently in the last week
EXPLAIN (ANALYZE, BUFFERS)
SELECT p.id, p.name, SUM(oi.quantity) AS sold_qty
FROM   Products    p
JOIN   OrderItems  oi ON oi.product_id = p.id
JOIN   Orders      o  ON o.id = oi.order_id
WHERE  o.order_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP  BY p.id, p.name
HAVING p.stock < 20
ORDER  BY sold_qty DESC;

How to EXPLAIN ANALYZE in PostgreSQL Syntax


EXPLAIN [ ( option [, ...] ) ] statement;

option :=
    ANALYZE [ boolean ]
  | VERBOSE [ boolean ]
  | COSTS [ boolean ]
  | BUFFERS [ boolean ]
  | TIMING [ boolean ]
  | SUMMARY [ boolean ]
  | FORMAT { TEXT | XML | JSON | YAML }

-- Example with ecommerce tables
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, FORMAT JSON)
SELECT o.id, o.total_amount
FROM   Orders o
WHERE  o.order_date >= CURRENT_DATE - INTERVAL '7 days';

Common Mistakes

Running plain EXPLAIN without ANALYZE: This shows estimates only, hiding actual runtimes. Always add ANALYZE when investigating performance.
Ignoring buffer hits and reads: Time may look low while I/O is high. Include BUFFERS and watch shared/local/temp reads to uncover disk-bound nodes.

Frequently Asked Questions (FAQs)

Does EXPLAIN ANALYZE modify data?

Yes, because it actually executes the statement. Wrap DML inside a transaction and roll back when testing.

How can I store plans for later review?

Use auto_explain or ">" redirect in psql to save plans, or insert pg_store_plans extension to keep history.

Why are estimates way off?

Outdated statistics, non-uniform data distribution, or enable_* planner flags can skew estimates. Run ANALYZE or create extended statistics.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie