EXPLAIN & query optimization

EXPLAIN & EXPLAIN ANALYZE — đọc query plan của PostgreSQL

EXPLAIN show plan + estimated cost (không chạy query). EXPLAIN ANALYZE chạy thật + actual time + actual rows. Đọc plan tree bottom-up: cost, rows, width, loops. BUFFERS đếm I/O page. Base cho mọi optimization.

Scan strategies — Seq Scan / Index Scan / Index Only Scan / Bitmap Heap Scan

PostgreSQL có 4 cách đọc data từ table. Planner chọn strategy dựa trên selectivity, random_page_cost, và visibility map. Hiểu 4 strategy này là nền tảng của query tuning.

Join algorithms — Nested Loop, Hash Join, Merge Join

PostgreSQL có 3 cách thực thi JOIN. Nested Loop streaming-friendly, Hash Join O(A+B) nhưng cần work_mem, Merge Join tối ưu khi đã sorted. Hiểu 3 algorithm = 80% query tuning.

Statistics & cost model — planner ước tính row count như thế nào

Planner chọn plan dựa trên statistics: n_distinct, MCV, histogram. Stats stale → estimate sai → plan sai. pg_stats view, default_statistics_target, cross-column correlation và CREATE STATISTICS extended.

5 anti-pattern khiến planner chọn plan tệ

Function on indexed column, implicit cast type mismatch, OR predicate, leading wildcard LIKE, và parameter sniffing — 5 anti-pattern gây slow query trong production. Nhận biết và fix từng cái với EXPLAIN before/after.

Tuning toolbox — 6 cách fix theo hierarchy chi phí thấp → cao

Sau khi diagnose bad plan, áp 6 cách fix theo thứ tự: rewrite query → ANALYZE → add index → statistics_target → materialized view → restructure schema. Mỗi tier có EXPLAIN before/after trên TaskFlow.

Mini-challenge M07 — Dashboard 2.1s → 50ms trên 5M task dataset

Setup 5M task dataset. EXPLAIN baseline 2.1s. Ba fix incremental: composite partial covering index, keyset pagination thay OFFSET, extended statistics. Đo ratio sau mỗi bước — từ 2100ms xuống 1.5ms (1400x).