query-execution • Advanced

EXPLAIN ANALYZE & Query Execution Plans

EXPLAIN displays the optimizer estimated execution plan; EXPLAIN ANALYZE actually runs the query, reporting real wall-clock timing, row counts, and buffer page cache hits.

Intuitive Mental Model

The Architect Blueprint vs Construction Inspection: EXPLAIN is the blueprint estimate (Estimated Cost); EXPLAIN ANALYZE is the inspector measuring the exact time and bricks used.

SQL DDL / DML QueryPostgreSQL / MySQL

-- PostgreSQL Explain Plan:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 942 AND total > 100;

/* Output:
Index Scan using idx_orders_user_id on orders (cost=0.42..8.45 rows=2 width=32) (actual time=0.042..0.051 rows=2 loops=1)
  Index Cond: (user_id = 942)
  Filter: (total > 100.00)
  Buffers: shared hit=3
Planning Time: 0.120 ms
Execution Time: 0.082 ms
*/

Key Architectural Takeaways

•Seq Scan: Full table scan reading every page from disk/memory sequentially.
•Index Scan: Traverses B-Tree, then fetches matching row tuples from the table heap.
•Index Only Scan: Satisfies the entire query directly from the index leaves without touching the table heap.
•Bitmap Index Scan: Combines multiple indexes via boolean AND/OR bitmaps before batching heap page reads.

Common Production Mistake

Assuming low cost means an index will always be used on tiny tables with under 100 rows.