How to View an Execution Plan in PostgreSQL

Galaxy Glossary

How can I see and interpret an execution plan in PostgreSQL?

EXPLAIN reveals PostgreSQL’s execution plan, detailing how it scans, joins, and sorts data so you can optimize queries.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is the execution plan in PostgreSQL?

The execution plan is PostgreSQL’s step-by-step roadmap for running a query. Each node shows which table access method, join strategy, and sort operation the planner chooses. Inspecting plans uncovers slow scans, missing indexes, and sub-optimal joins.

How do I show an execution plan quickly?

Use EXPLAIN before any SELECT, INSERT, UPDATE, or DELETE. PostgreSQL returns a tree with estimated costs, rows, and width. No data is read—only the planner is invoked.

When should I add ANALYZE, BUFFERS, or VERBOSE?

Add ANALYZE to run the query and overlay actual runtime stats. Add BUFFERS to see shared-block hits and reads. Use VERBOSE to expose target column lists and internal details.

Which FORMAT is best for tooling?

FORMAT JSON returns a structured plan suited for code analysis and visualization tools. FORMAT YAML provides readable, indented output. Default TEXT works well for psql.

How do I identify slow scans?

Look for Seq Scan nodes reading many rows with high cost. If a selective predicate exists on an indexed column, create or tweak the index to convert the sequential scan to an Index Scan.

What are best practices for interpreting plans?

Start at the deepest child node—this is executed first. Compare rows vs actual rows; big gaps signal stale statistics. Focus on nodes with the largest actual time. Use BUFFERS to find disk reads.

Tip: Keep statistics fresh

Run ANALYZE regularly or enable autovacuum so the planner’s row estimates stay accurate, reducing surprise sequential scans.

Why How to View an Execution Plan in PostgreSQL is important

How to View an Execution Plan in PostgreSQL Example Usage


-- Find customers that bought out-of-stock products and inspect the plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT c.id, c.name
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 p.stock = 0;

How to View an Execution Plan in PostgreSQL Syntax


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

Key options:
  ANALYZE    -- execute statement and show runtime stats
  VERBOSE    -- include extra details
  BUFFERS    -- show shared/local/temp block usage (needs ANALYZE)
  COSTS      -- show estimated costs (default TRUE)
  FORMAT { TEXT | XML | JSON | YAML }

Example:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT o.id, total_amount
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
WHERE c.email = 'jane@shop.com';

Common Mistakes

Using EXPLAIN without ANALYZE for performance tuning. EXPLAIN alone shows estimates, which can differ wildly from reality. Add ANALYZE to compare actual vs estimated rows and time.
Ignoring stale statistics. If actual rows differ greatly from estimates, autovacuum or manual ANALYZE hasn’t updated stats. Refresh stats, then re-check the plan.

Frequently Asked Questions (FAQs)

Does EXPLAIN ANALYZE modify data?

Yes, if you run it with INSERT, UPDATE, or DELETE, the statement executes and changes data. Wrap the call in a transaction and roll back if you only need the plan.

How do I save plans for later review?

Use EXPLAIN (FORMAT JSON) and redirect output to a file, or call pg_store_plans extension to log plans automatically.

Why is my index not used?

Common causes include low selectivity, mismatched data types (e.g., varchar vs text), or functions on the indexed column. Rewrite predicates or create expression indexes.