EXPLAIN/EXPLAIN ANALYZE
EXPLAIN shows the plan PostgreSQL will use for a query, and EXPLAIN ANALYZE actually runs it and reports real timings.
In this page:
Syntax
EXPLAIN SELECT columns FROM table_name WHERE condition;
EXPLAIN ANALYZE SELECT columns FROM table_name WHERE condition;EXPLAIN/EXPLAIN ANALYZE
Read the plan from the innermost line outwards. Seq Scan reads the whole table, Index Scan and Index Only Scan use an index, and Bitmap scans combine index results.
Cost numbers are estimates while ANALYZE adds actual time and rows. Big differences between estimated and actual rows suggest stale statistics, fixed by ANALYZE.
Note:
EXPLAIN ANALYZE really executes the statement, so wrap data-changing ones in a transaction and roll back.
Example: EXPLAIN/EXPLAIN ANALYZE
shop=# EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Scan using users_email_key on users (cost=0.42..8.44 rows=1 width=72) (actual time=0.030..0.031 rows=1 loops=1)
Index Cond: (email = '[email protected]'::text)
Planning Time: 0.120 ms
Execution Time: 0.055 ms
shop=# EXPLAIN SELECT * FROM users WHERE lower(name) = 'ada';
Seq Scan on users (cost=0.00..25.00 rows=1 width=72)
Filter: (lower(name) = 'ada'::text)⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Running EXPLAIN ANALYZE on destructive statements
- Ignoring differences between estimated and actual rows
- Optimising without measuring
Chapter Summary
- EXPLAIN shows the plan
- ANALYZE runs it and times it
- Seq Scan versus Index Scan
- Compare estimated and actual rows
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: