How to Analyze Execution Plans in PostgreSQL

Galaxy Glossary

How do I use EXPLAIN ANALYZE to optimize PostgreSQL queries?

EXPLAIN and EXPLAIN ANALYZE reveal PostgreSQL’s execution plan, helping you pinpoint bottlenecks and tune queries.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is an execution plan in PostgreSQL?

An execution plan is PostgreSQL’s step-by-step roadmap for running a query.It shows chosen indexes, join strategies, and estimated costs, allowing you to diagnose slow SQL.

How do I generate an execution plan?

Prefix your statement with EXPLAIN to see the planner’s estimates, or EXPLAIN ANALYZE to run the query and append actual runtime metrics.

Example: investigate slow customer orders

EXPLAIN ANALYZE SELECT o.id, o.total_amount FROM Orders o JOIN Customers c ON c.id = o.customer_id WHERE c.email LIKE '%@gmail.com' AND o.order_date > NOW() - INTERVAL '30 days';

The output lists nodes such as Seq Scan, Index Scan, or Hash Join, each with cost, rows, and timing.

Which plan nodes should I watch?

Focus on nodes with the highest actual time or rows mismatch.Sequential scans on large tables, nested-loop joins on big result sets, and sorts spilling to disk are prime suspects.

How can I improve a bad plan?

Add or tune indexes, rewrite filters to be sargable, force parallelism via set max_parallel_workers_per_gather, or refactor joins/subqueries. Rerun EXPLAIN ANALYZE to verify improvement.

When should I use additional EXPLAIN options?

BUFFERS shows cache hits vs. reads; VERBOSE reveals column-level details; FORMAT JSON feeds visual tools. Combine them for deeper insight.

Best practices for plan analysis

1) Always compare estimated vs.actual rows.
2) Test on production-like data.
3) Keep statistics current with ANALYZE.
4) Save baseline plans to track regressions.

What are common mistakes?

Skipping ANALYZE leads to stale stats and bad plans. Misreading cost units as milliseconds causes wrong conclusions; they are abstract weights, not time.

Quick recap

Use EXPLAIN for planning insight, EXPLAIN ANALYZE for real metrics, watch high-cost nodes, and iterate with indexing or query rewrites.

Why How to Analyze Execution Plans in PostgreSQL is important

How to Analyze Execution Plans in PostgreSQL Example Usage


EXPLAIN ANALYZE SELECT p.name, p.stock - SUM(oi.quantity) AS remaining_stock
FROM Products p
LEFT JOIN OrderItems oi ON oi.product_id = p.id
GROUP BY p.id
HAVING p.stock - SUM(oi.quantity) < 10;

How to Analyze Execution Plans in PostgreSQL Syntax


EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] [ COSTS ] [ SETTINGS ]
       [ BUFFERS ] [ WAL ] [ TIMING ]
       [ SUMMARY ] [ FORMAT { TEXT | JSON | YAML | XML } ] statement;

-- Ecommerce examples
-- Show planner estimates only
EXPLAIN SELECT * FROM Products WHERE price > 100;

-- Show runtime metrics, I/O stats, and JSON output
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT c.name, SUM(oi.quantity) AS items_bought
FROM Customers c
JOIN Orders o  ON o.customer_id = c.id
JOIN OrderItems oi ON oi.order_id = o.id
GROUP BY c.name;

Common Mistakes

Forgetting the ANALYZE keyword. EXPLAIN alone skips execution, so you miss actual time and rows. Always use EXPLAIN ANALYZE when measuring real performance.
Interpreting cost numbers as milliseconds. Costs are relative weights the planner uses; rely on actual time columns for runtime and compare costs only between plans.