Nội dung
Danh sách bài học
- 01~20 phút
Tại sao cần index — heap scan 800ms vs index scan 0.2ms
Heap = unsorted data. Index = sorted lookup table. 4000x speedup. 3 scan strategy: Seq/Index/Index Only. When index NOT help.
- 02~25 phút
B-tree internals — page split, fill factor, height log branching
B-tree không phải binary tree. Multi-way balanced (~400 key/page). Height = log_400(N). Page split cost. Fill factor 90 default. UUIDv4 amplification 2-3x write.
- 03~22 phút
Composite index ordering — leftmost prefix rule + INCLUDE covering
(a, b, c) index không help WHERE b=x. Equality column trước range. INCLUDE cho Index Only Scan. Decision matrix theo query pattern.
- 04~22 phút
GIN/BRIN/partial/expression — chọn index theo data type và workload
B-tree không index được JSONB array tsvector. PG có 6 loại index. GIN inverted, BRIN block-range time-series, partial subset, expression function.
- 05~30 phút
Mini-challenge M05 — TaskFlow dashboard 2.1s → 50ms qua index strategy
Setup 1M task dataset. EXPLAIN baseline 2.1s. Iterate 4 index version: single → composite → INCLUDE → partial. Measure ratio + Index Only Scan.