EXPLAIN ANALYZE
intermediatepostgresql
2 min read
What is EXPLAIN ANALYZE?
EXPLAIN ANALYZE runs a query and shows the actual execution plan, including the time spent on each step and the actual number of rows processed. It is the essential tool for query performance analysis.
Syntax
EXPLAIN ANALYZE SELECT * FROM students WHERE age > 20;For more detail:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM students WHERE age > 20;When to Use
- Diagnosing slow queries
- Verifying that indexes are being used
- Comparing the performance of different query approaches
- Understanding join strategies chosen by the planner
Key Points
- Actually Runs the Query — Unlike plain EXPLAIN, ANALYZE executes the query. Use with caution on data-modifying queries.
- Execution Time — Shows actual time in milliseconds for each plan node.
- Rows — Compares estimated rows to actual rows. Large discrepancies indicate stale statistics.
- Scan Types — Seq Scan (full table), Index Scan, Index Only Scan, Bitmap Scan.
- BUFFERS — Shows shared/local buffer hits and reads (I/O information).
- Wrap in Transaction — For INSERT/UPDATE/DELETE, wrap in BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; to avoid side effects.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
EXPLAIN ANALYZE Challenge
Write a query that solve this task: view the execution plan for a simple query.
Expected result
Execution plan showing Seq Scan or Index Scan, estimated vs actual rows, and execution time.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is EXPLAIN ANALYZE?
Initializing database...Each run starts from fresh sample data.
More Examples
EXPLAIN with BUFFERS
See I/O details in the execution plan.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Is it safe to use EXPLAIN ANALYZE on DELETE or UPDATE?
EXPLAIN ANALYZE actually executes the query. Wrap modifying queries in BEGIN/ROLLBACK to avoid permanent changes.
What does 'actual time' mean in the output?
It shows the time in milliseconds for the first row (startup cost) and all rows. The format is actual time=startup..total.