PostgreSQL — EXPLAIN ANALYZE & Query Planner

Kỹ năng đọc EXPLAIN ANALYZE tốt giải quyết được 80% vấn đề performance mà không cần hiểu sâu internals.

---

1. EXPLAIN ANALYZE là gì

PostgreSQL có một query planner — trước khi chạy query, planner phân tích các lựa chọn (dùng index nào, join strategy nào, thứ tự join...) rồi chọn plan tốt nhất theo ước lượng cost.

Query SQL → Parser → Planner → Executor → Kết quả
                        ↑
              dựa vào statistics về data
              (số rows, distribution, cardinality...)

EXPLAIN cho biết planner định làm gì. EXPLAIN ANALYZE thực sự chạy query và so sánh ước lượng với thực tế.

-- Đủ thông tin nhất cho debugging
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;

• ANALYZE — thực thi và đo thời gian thực tế
• BUFFERS — hiện số page đọc từ cache vs disk
• FORMAT TEXT — output dạng text (dễ đọc hơn JSON khi debug nhanh)

---

2. Đọc output

Cấu trúc cơ bản

Seq Scan on orders  (cost=0.00..1842.00 rows=50000 width=64)
                     (actual time=0.123..45.678 rows=49821 loops=1)
                    Buffers: shared hit=1230 read=342

cost=startup..total
  startup = cost để bắt đầu trả ra row đầu tiên (sort cần sort xong mới ra row → startup cao)
  total   = tổng cost ước lượng (đơn vị tương đối, không phải ms)

rows     = số row planner ước lượng trả ra
width    = kích thước trung bình 1 row (bytes)

actual time = thời gian thực tế (ms): startup..total
actual rows  = số row thực tế — nếu lệch nhiều với rows ước lượng → planner thiếu thông tin
loops        = số lần node này chạy (nested loop bên trong chạy nhiều lần)

Buffers:
  shared hit = số page đọc từ shared_buffers (RAM) — tốt
  read       = số page đọc từ disk — cần tránh
  written    = số page dirty được flush (ít gặp)

Đọc từ trong ra ngoài

Output là một cây, node trong cùng (indented nhiều nhất) chạy trước:

Hash Join  (cost=...)
  →  Hash  (cost=...)          ← bước 2: build hash table từ kết quả bước 3
       →  Seq Scan on users    ← bước 3: scan users trước
  →  Seq Scan on orders        ← bước 1: scan orders (outer)

---

3. Các node quan trọng

Scan nodes

Node	Ý nghĩa	Khi nào xuất hiện
Seq Scan	Đọc toàn bộ bảng page by page	Không có index, hoặc selectivity thấp
Index Scan	Dùng index → lấy ctid → đọc heap	Có index, selectivity cao
Index Only Scan	Dùng index, không cần đọc heap	Index covering (INCLUDE hoặc tất cả column cần có trong index)
Bitmap Heap Scan	Collect ctids từ index → đọc heap theo batch	Nhiều rows cần đọc, hoặc kết hợp nhiều index

Index Scan vs Bitmap Heap Scan:

Index Scan:
  row 1 → ctid(0,1) → đọc page 0
  row 2 → ctid(5,3) → đọc page 5
  row 3 → ctid(0,4) → đọc page 0 lại
  → random I/O, page có thể đọc nhiều lần

Bitmap Heap Scan:
  Bước 1 (Bitmap Index Scan): thu thập TẤT CẢ ctids vào bitmap
  Bước 2 (Bitmap Heap Scan): sort ctids theo page → đọc từng page 1 lần
  → ít I/O hơn khi rows trải đều nhiều page

-- Thấy Bitmap Heap Scan với nhiều rows? Bình thường
-- Thấy Seq Scan trên bảng lớn với filter? Cần index

-- Kiểm tra có dùng Index Only Scan không
EXPLAIN SELECT id, email FROM users WHERE email = 'foo@bar.com';
-- Nếu có index trên (email) nhưng không INCLUDE id → Index Scan
-- Thêm INCLUDE (id) → Index Only Scan

---

4. Tín hiệu cần chú ý

Rows ước lượng lệch nhiều

Index Scan  (cost=0.43..500.00 rows=1000 ...)
            (actual time=0.05..120.00 rows=50000 ...)
                                           ^^^^^^
                                    thực tế 50x nhiều hơn ước lượng

Planner ước lượng 1.000 rows nhưng thực tế 50.000 → planner đã chọn plan tối ưu cho 1.000 rows, trong khi thực tế cần một plan khác.

Nguyên nhân thường gặp:

• Statistics lỗi thời → chạy ANALYZE table_name
• Column có correlation cao với column khác → tạo extended statistics
• Data distribution lệch (skew) → tăng statistics target

Seq Scan trên bảng lớn

Seq Scan on orders  (cost=0.00..45000.00 rows=2000000 ...)

Không phải lúc nào Seq Scan cũng xấu — nếu query cần lấy phần lớn bảng thì Seq Scan nhanh hơn Index Scan (vì sequential I/O nhanh hơn random I/O).

Seq Scan đáng lo khi:

• Bảng lớn + filter selectivity cao (chỉ cần vài %) mà không có index
• actual rows nhỏ hơn nhiều so với tổng rows bảng

Buffers: read cao

Buffers: shared hit=10 read=5000

5.000 page đọc từ disk → page chưa được cache, hoặc bảng quá lớn để fit vào shared_buffers. Lần chạy đầu tiên sau khi restart thường có read cao, các lần sau sẽ là hit.

---

5. Join strategies

Planner chọn join strategy dựa vào kích thước, index, và sort order của hai bảng.

Hash Join

Hash Join
  → Hash
       → Seq Scan on users     ← build side (bảng nhỏ hơn)
  → Seq Scan on orders         ← probe side

Build hash table từ bảng nhỏ → scan bảng lớn và probe:

Tốt khi:
✅ Một bảng vừa fit vào work_mem
✅ Không có index hữu ích trên join key
✅ Cần lấy nhiều rows (full table join)

Không tốt khi:
❌ Cả hai bảng đều lớn → spill to disk
❌ Chỉ cần vài rows (outer nhỏ)

Nested Loop

Nested Loop
  → Index Scan on orders (outer)      ← vòng ngoài, chạy 1 lần
  → Index Scan on users (inner)       ← vòng trong, chạy N lần (1 lần/outer row)
      Index Cond: (users.id = orders.user_id)

Tốt khi:
✅ Outer set nhỏ (vài trăm rows)
✅ Inner có index trên join key
✅ OLTP queries điển hình

Không tốt khi:
❌ Outer lớn → inner chạy hàng triệu lần

Merge Join

Merge Join
  → Index Scan on orders (sorted by user_id)
  → Sort → Seq Scan on users

Cả hai bảng được sort theo join key, merge như merge sort:

Tốt khi:
✅ Dữ liệu đã sorted (có index phù hợp)
✅ Cần kết quả sorted theo join key
✅ Window functions, ORDER BY

Không tốt khi:
❌ Phải sort cả hai bảng → chi phí cao

Force join strategy (debug only)

-- Tắt một loại để xem plan thay thế
SET enable_hashjoin = off;
SET enable_nestloop = off;
SET enable_mergejoin = off;

-- Chạy EXPLAIN để xem plan mới
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- Nhớ reset sau khi test
RESET enable_hashjoin;

Đừng để các setting này trong production — chỉ dùng để debug hoặc test plan thay thế.

---

6. Planner statistics

Planner dựa vào statistics của từng column để ước lượng rows. Statistics không chính xác → plan sai.

-- Xem statistics hiện tại của một column
SELECT
  attname,
  n_distinct,       -- số giá trị distinct (-1 = unique)
  correlation,      -- thứ tự vật lý vs logical (1 = tăng dần hoàn toàn)
  most_common_vals, -- giá trị phổ biến nhất
  most_common_freqs -- tần suất tương ứng
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

Tăng statistics target

Mặc định PostgreSQL lưu 100 most common values per column. Với column có nhiều distinct values, có thể tăng lên:

-- Mặc định: 100. Tăng lên 500 cho column quan trọng
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 500;
ANALYZE orders;

Sau khi ANALYZE, planner có thêm thông tin → ước lượng rows chính xác hơn.

Extended statistics (PG 10+)

Planner mặc định coi các column là độc lập. Nhưng trong thực tế, nhiều column có correlation — ví dụ city và country không độc lập.

-- Tạo extended statistics cho cặp column hay dùng cùng nhau trong WHERE
-- (orders.status và orders.region thường filter cùng nhau)
CREATE STATISTICS stat_status_region ON status, region FROM orders;
ANALYZE orders;

-- Planner giờ biết correlation giữa status và region
-- → ước lượng rows chính xác hơn khi WHERE status = 'delivered' AND region = 'north'

-- Với correlated columns trong query:
-- Trước extended stats:  rows=1000 (ước lượng độc lập, sai)
-- Sau extended stats:    rows=85   (biết correlation, đúng)

-- Xem extended statistics đã tạo
SELECT stxname, stxkeys, stxkind FROM pg_statistic_ext WHERE stxname = 'stat_status_region';

---

7. CTE optimization

PG 12+ thay đổi quan trọng

-- PG 11 trở về trước: CTE luôn là optimization fence
-- → planner không thể push conditions vào trong CTE
-- → luôn materialize, không inline

-- PG 12+: CTE mặc định được INLINE (như subquery)
WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT * FROM recent_orders WHERE user_id = 123;

-- Planner có thể push user_id = 123 vào trong → dùng index

MATERIALIZED vs NOT MATERIALIZED

-- Force materialize: tính toán 1 lần, lưu tạm, dùng lại nhiều lần
-- Tốt khi CTE được reference nhiều lần
WITH expensive AS MATERIALIZED (
  SELECT user_id, sum(total) AS total_spent
  FROM orders
  GROUP BY user_id
)
SELECT * FROM expensive WHERE total_spent > 1000
UNION ALL
SELECT * FROM expensive WHERE total_spent < 10;

-- NOT MATERIALIZED: force inline (PG 12+ default, nhưng explicit khi cần)
WITH recent AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT * FROM recent WHERE user_id = 123;

LATERAL join

LATERAL cho phép subquery tham chiếu column từ outer query — như một "correlated subquery" nhưng trả về nhiều rows:

-- N orders gần nhất của mỗi user
SELECT u.id, u.email, recent.*
FROM users u
CROSS JOIN LATERAL (
  SELECT id, total_amount, created_at
  FROM orders
  WHERE user_id = u.id        -- tham chiếu u.id từ outer
  ORDER BY created_at DESC
  LIMIT 3
) recent;

Không có LATERAL: phải dùng window function (ROW_NUMBER) + subquery → phức tạp hơn
Có LATERAL: đọc tự nhiên, planner có thể dùng index trên (user_id, created_at)

---

8. Parallel query

PostgreSQL có thể chia query thành nhiều worker processes chạy song song.

Gather  ← merge kết quả từ các worker
  → Parallel Seq Scan on large_table (N workers)

Cấu hình

-- Xem config hiện tại
SHOW max_parallel_workers_per_gather;  -- số worker tối đa cho 1 query node
SHOW max_parallel_workers;             -- tổng worker tối đa

-- Session-level (test)
SET max_parallel_workers_per_gather = 4;

Khi nào parallel query được dùng

✅ Seq Scan trên bảng lớn (parallel_tuple_cost thấp)
✅ Hash Join, Merge Join (aggregate song song)
✅ Aggregate (COUNT, SUM trên nhiều rows)

❌ Index Scan (thường không parallel — random I/O không benefit từ parallel)
❌ Query đã đủ nhanh (overhead setup worker không đáng)
❌ max_parallel_workers_per_gather = 0

Kiểm tra query có dùng parallel không

EXPLAIN SELECT count(*) FROM large_table;

Tìm Gather hoặc Gather Merge trong plan — đó là dấu hiệu parallel đang được dùng.

Finalize Aggregate
  → Gather
       → Partial Aggregate
            → Parallel Seq Scan on large_table
                                   ^^^^^^^
                                   Workers Planned: 3

---

9. Workflow debug performance

Khi query chậm

-- Bước 1: chạy EXPLAIN ANALYZE để xem plan
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- Bước 2: tìm node tốn nhiều thời gian nhất
-- actual time cao + rows thực tế << ước lượng → thống kê lỗi thời

-- Bước 3: nếu thống kê lỗi thời
ANALYZE table_name;

-- Bước 4: nếu vẫn ước lượng sai → tăng statistics target
ALTER TABLE orders ALTER COLUMN col SET STATISTICS 500;
ANALYZE orders;

-- Bước 5: nếu Seq Scan trên bảng lớn → cân nhắc thêm index
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

Công cụ online

explain.depesz.com và explain.dalibo.com — paste output của EXPLAIN ANALYZE vào, tự highlight node chậm, dễ đọc hơn raw text.

-- Copy output này vào các tool trên
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;

---

10. Tóm tắt — đọc EXPLAIN nhanh

Nhìn actual rows vs estimated rows:
  Lệch > 10x → ANALYZE, extended stats, tăng statistics target

Nhìn node tốn thời gian nhất:
  Seq Scan bảng lớn → thiếu index?
  Sort → có thể dùng index để tránh sort?
  Hash Join spill to disk → tăng work_mem?

Nhìn Buffers:
  read cao lần đầu → bình thường (cold cache)
  read cao mọi lần → bảng quá lớn / cần index để đọc ít page hơn

Seq Scan không phải lúc nào cũng xấu:
  Lấy > 5-10% bảng → Seq Scan thường nhanh hơn Index Scan
  Lấy < 1% bảng mà vẫn Seq Scan → cần index