explain-cheatsheet

EXPLAIN ANALYZE Cheatsheet (Postgres + MySQL)

Quick reference khi đang debug query slow. Bookmark này.

Tags: attachment cheatsheet explain Liên quan: Tuan-04-Query-Optimization-EXPLAIN · Tuan-Bonus-MySQL-Internals

---

1. Postgres — Syntax

EXPLAIN <query>;                                        -- chỉ plan
EXPLAIN ANALYZE <query>;                                 -- chạy thật, đo thời gian
EXPLAIN (ANALYZE, BUFFERS) <query>;                      -- + I/O stats
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) <query>;-- full debug
 
-- Wrap trong transaction nếu test UPDATE/DELETE:
BEGIN; EXPLAIN (ANALYZE) UPDATE ... ; ROLLBACK;
 
-- PG16+: shows non-default settings
EXPLAIN (ANALYZE, SETTINGS) <query>;
 
-- PG13+: WAL volume per query
EXPLAIN (ANALYZE, WAL) <query>;
 
-- PG16+: GENERIC_PLAN cho prepared/parameterized
EXPLAIN (GENERIC_PLAN) <query>;

2. Postgres — Output Anatomy

Limit  (cost=0.43..8.55 rows=20 width=64) (actual time=0.025..0.075 rows=20 loops=1)
  ->  Index Scan using idx_x on orders  (cost=...) (actual time=...) (rows=20)
        Index Cond: (user_id = 42)
        Filter: (status = 'pending')
        Rows Removed by Filter: 30
        Buffers: shared hit=5 read=2
 Planning Time: 0.123 ms
 Execution Time: 0.105 ms

Field	Meaning
cost=X..Y	Estimated startup..total cost (abstract unit)
rows=N	Estimated rows
width=N	Bytes per row
actual time=X..Y	Real startup..total ms
rows=N loops=M	Node ran M times, returned N each
Index Cond	Used by index (efficient)
Filter	Post-scan filter (less efficient)
Rows Removed by Filter	Discarded rows (high = bad)
Heap Fetches (Index Only Scan)	Fetch from heap; 0 = perfect
Buffers: shared hit/read/dirtied	Buffer pool stats
Sort Method: quicksort	In-memory sort
Sort Method: external sort	Disk spill — bad
Batches: N (Hash)	>1 = hash spill

3. Postgres — Red Flags Quick Scan

Red flag	Cause	Fix
Seq Scan on huge table	No index	Add index
actual rows >> rows (10x+)	Stats stale	ANALYZE
Heap Fetches > 0 in IO Scan	VM stale	VACUUM
Sort Method: external sort	work_mem too low	SET work_mem = '256MB'
Batches: 4+ in Hash	Hash spill	Increase work_mem
loops > 1000 Nested Loop	Quadratic	Force Hash/Merge join
Buffers: shared read >> hit	Cold cache	Tune shared_buffers
Planning Time >> Execution Time	Complex plan	Prepared statement
Rows Removed by Filter huge	Post-index filter	Composite/partial index

4. Postgres — 30-Second Diagnose

1. Find slowest node (biggest actual time)
2. Check rows estimate vs actual — mismatch >10x → stats issue
3. Check scan type: Seq vs Index — appropriate?
4. Check joins: Nested Loop with high outer rows → fix
5. Check sort/hash: spill to disk?
6. Check buffer hit: cold or hot?

5. Postgres — Plan Nodes Reference

Scans

• Seq Scan — full table; fine if table small or >5-10% returns
• Index Scan — index → heap fetch per row
• Index Only Scan — read only from index; needs VM
• Bitmap Heap Scan — combine indexes via bitmap
• Tid Scan — direct CTID lookup
• Foreign Scan — FDW

Joins (3 algorithms)

• Nested Loop — for each outer, scan inner. Good for outer ≤ 100 + indexed inner.
• Hash Join — build hash on smaller, probe larger. O(N+M). Bad if spill.
• Merge Join — sorted both sides walk together. Good when sorted.

Other

• Sort — order rows
• Aggregate / HashAggregate / GroupAggregate
• Limit — top N
• Materialize — cache rows for reuse
• Hash — build hash table
• Gather / Gather Merge — parallel collection
• CTE Scan — materialized CTE
• SubPlan / InitPlan — uncorrelated subquery

6. Postgres — Useful Pragmatics

-- See top queries
SELECT substring(query,1,80), calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
 
-- Check stats freshness
SELECT relname, last_vacuum, last_autovacuum, last_analyze, n_dead_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
 
-- Find unused indexes
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey';
 
-- Plan visualizer
-- Paste EXPLAIN (FORMAT JSON) output to:
-- https://explain.dalibo.com or https://explain.depesz.com

7. MySQL — Syntax

EXPLAIN <query>;                           -- basic
EXPLAIN FORMAT=JSON <query>;                -- detailed
EXPLAIN ANALYZE <query>;                    -- MySQL 8.0.18+, actual run
SHOW WARNINGS;                              -- planner notes

8. MySQL — Output Columns

Column	Meaning
id	Step number (higher = inner)
select_type	SIMPLE/PRIMARY/SUBQUERY/etc
table	Table accessed
type	Access type (see below)
possible_keys	Indexes available
key	Index actually used
key_len	Used index bytes
ref	What column compared
rows	Estimated rows examined
filtered	% rows after filter
Extra	Hints (Using index, Using temporary, etc.)

9. MySQL — Access Types (best → worst)

Type	Meaning
system	1-row table
const	Const lookup via PK/unique
eq_ref	1-row from each table via PK/unique (joins)
ref	Index lookup, multiple rows
range	Index range scan
index	Full index scan
ALL	Full table scan — BAD

10. MySQL — Red Flags

Extra	Meaning
Using filesort	Sort can’t use index
Using temporary	Temp table created
Using join buffer (Block Nested Loop)	No index for join
Using where	Post-filter (often OK)
Using index	Covering index — GOOD
Using index condition	ICP optimization — GOOD
Impossible WHERE	Query returns nothing (planner knows)

11. Tools

• Postgres: pgMustard, pev2 (dalibo), explain.depesz.com, auto_explain
• MySQL: dbForge, MySQL Workbench Visual Explain, EVERSQL
• Both: DBeaver

---

Cập nhật: 2026-05-16