Mini-challenge — TaskFlow dashboard 2.1s → 50ms

Dashboard TaskFlow từ mini-challenge dashboard query ban đầu chạy 2,1 giây trên 1 triệu task. Mục tiêu dưới 50 ms, tức 40× nhanh hơn. Bài này không dạy concept mới: seed 1M row, chạy EXPLAIN ANALYZE baseline, rồi iterate 4 version index (đơn cột → composite → INCLUDE → partial) và đo sau mỗi bước. Mọi thứ bạn cần đã có ở ba bài trước của module; đây là chỗ áp chúng vào một ca thực chiến.

1. Query đích cần tối ưu

Query dashboard hiển thị task active của một assignee, due trong 3 ngày tới, paginate 20:

SELECT id, title, status, due_at,
       EXTRACT(DAY FROM (due_at - now())) AS days_to_due
FROM tasks
WHERE assignee_id = $1
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at ASC NULLS LAST, id
LIMIT 20;

Mục tiêu: 2100 ms xuống dưới 50 ms. Ba điều kiện filter (assignee_id, status, due_at) + ORDER BY + LIMIT là bộ đặc trưng để thiết kế composite index.

2. Step 1 — Setup 1M task dataset

Xoá sạch bảng rồi seed lại 1 triệu row, phân bố sát thực tế:

TRUNCATE tasks RESTART IDENTITY CASCADE;

INSERT INTO tasks (project_id, assignee_id, title, status, due_at, created_at, updated_at)
SELECT
  1 + floor(random() * 5)::int,           -- 5 project (1-5), deu
  1 + floor(random() * 10)::int,          -- 10 assignee (1-10), deu
  'Task ' || g,
  (ARRAY['todo', 'doing', 'done', 'archived'])[1 + floor(random() * 4)::int],
  CASE WHEN random() < 0.7
       THEN now() + (random() * 30 - 15) * INTERVAL '1 day'
       ELSE NULL
  END,
  now() - (random() * 180) * INTERVAL '1 day',
  now() - (random() * 30) * INTERVAL '1 day'
FROM generate_series(1, 1000000) g;

ANALYZE tasks;
SELECT COUNT(*) FROM tasks;  -- expected: 1000000

Phân bố sau seed: 10 assignee → mỗi user ~100k row; 4 status → todo/doing ~50% (500k row); due_at NULL ~30%, còn lại random trong 30 ngày → match cửa sổ 3 ngày khoảng 7%.

3. Step 2 — EXPLAIN baseline (không có index ngoài PK)

Trước khi chạy: bảng chưa có index nào ngoài PK. Bạn đoán planner chọn scan gì, và bước nào trong plan sẽ tốn nhất?

\timing on

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, status, due_at,
       EXTRACT(DAY FROM (due_at - now())) AS days_to_due
FROM tasks
WHERE assignee_id = 5
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at ASC NULLS LAST, id
LIMIT 20;

Output mong đợi (số thực tế dao động theo máy):

 Limit  (cost=... rows=20 width=...)
   ->  Sort  (cost=...)
         Sort Key: due_at NULLS LAST, id
         ->  Seq Scan on tasks  (cost=... rows=...)
               Filter: ((assignee_id = 5) AND
                        (status = ANY ('{todo,doing}')) AND
                        (due_at >= now()) AND
                        (due_at <= now() + interval '3 days'))
               Rows Removed by Filter: ~990000
 Planning Time: 0.5 ms
 Execution Time: 2100.00 ms

Seq Scan đọc toàn bộ 1M row vì không có index nào để dùng. Filter loại 99%: 10 user (10×), ~50% match status, ~7% match due range → còn ~3500 row, Sort 3500 row rồi LIMIT 20. Tổng 2,1 s là CPU cộng I/O đọc cả bảng.

4. Step 3 — v1: index một cột trên assignee_id

Trước khi chạy: với index chỉ trên assignee_id, bạn đoán planner chọn Index Scan hay Bitmap Heap Scan, và Sort node còn không?

Gợi ý: một cột thì chọn cột nào lọc mạnh nhất trong ba điều kiện? Và khi index chỉ trả về vị trí row, việc gì còn phải làm trên heap?

CREATE INDEX idx_tasks_assignee ON tasks(assignee_id);
ANALYZE tasks;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, status, due_at,
       EXTRACT(DAY FROM (due_at - now())) AS days_to_due
FROM tasks
WHERE assignee_id = 5
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days'
ORDER BY due_at ASC NULLS LAST, id
LIMIT 20;

 Limit  (cost=...)
   ->  Sort  (cost=...)
         Sort Key: due_at NULLS LAST, id
         ->  Bitmap Heap Scan on tasks  (cost=...)
               Recheck Cond: (assignee_id = 5)
               Filter: ((status = ANY ('{todo,doing}')) AND ...)
               ->  Bitmap Index Scan on idx_tasks_assignee
                     Index Cond: (assignee_id = 5)
 Execution Time: ~250.00 ms

Từ 2100 ms còn 250 ms, khoảng 8×. Index thu hẹp xuống 100k row của assignee=5 và tránh 900k row kia, nhưng vẫn phải fetch heap cho cả 100k row, áp filter status và due_at trên heap, rồi sort. Muốn đẩy filter vào index thì cần composite.

5. Step 4 — v2: composite (assignee_id, status, due_at)

Index v1 vẫn còn Sort. Muốn Sort biến mất thì cột nào phải đứng cuối index? Đặt cược trước khi mở lời giải, và đoán luôn Execution Time cỡ nào. Thứ tự cột xem lại ở bài composite.

DROP INDEX idx_tasks_assignee;
CREATE INDEX idx_tasks_dashboard_v2 ON tasks(assignee_id, status, due_at);
ANALYZE tasks;

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;   -- cung query dashboard o muc 1

 Limit
   ->  Index Scan using idx_tasks_dashboard_v2 on tasks
         Index Cond: ((assignee_id = 5) AND
                      (status = ANY ('{todo,doing}')) AND
                      (due_at >= now()) AND
                      (due_at <= now() + interval '3 days'))
 Execution Time: ~5.00 ms

Bước nhảy lớn nhất của cả bài: 5 ms, tức 420× so với baseline. Ba điều kiện đều nằm trong Index Cond, index chỉ trả ~3500 row match, và Sort node biến mất vì index đã sắp theo due_at. Vẫn còn một khoản: fetch heap để lấy id và title, hai cột không có trong index.

Thứ tự cột (assignee_id, status, due_at) đúng vì assignee_id equality thu hẹp nhanh nhất, status equality tiếp theo (chỉ 4 giá trị nhưng loại 50%), và due_at là range nên phải đứng cuối, đúng luật của bài 03.

6. Step 5 — v3: INCLUDE (id, title) cho Index Only Scan

Trước khi chạy: thêm id, title vào INCLUDE. Bạn đoán dòng Heap Fetches trong plan sẽ là bao nhiêu, và vì sao cần VACUUM trước khi đo?

DROP INDEX idx_tasks_dashboard_v2;
CREATE INDEX idx_tasks_dashboard_v3
  ON tasks(assignee_id, status, due_at)
  INCLUDE (id, title);

-- Cap nhat visibility map (can cho Index Only Scan)
VACUUM ANALYZE tasks;

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;   -- cung query dashboard o muc 1

 Limit
   ->  Index Only Scan using idx_tasks_dashboard_v3 on tasks
         Index Cond: ((assignee_id = 5) AND
                      (status = ANY ('{todo,doing}')) AND
                      (due_at >= now()) AND
                      (due_at <= now() + interval '3 days'))
         Heap Fetches: 0
 Execution Time: ~2.00 ms

Heap Fetches: 0: mọi cột query cần (id, title, status, due_at) đều nằm trên leaf page, heap không được mở lần nào. Trên máy dev chênh 5 ms và 2 ms không đáng kể; trên bảng không fit RAM, đây là chỗ tiết kiệm I/O nhiều nhất.

7. Step 6 — v4: partial index, bớt row done/archived

Index v3 chứa cả 1M row kể cả done và archived, những row không bao giờ xuất hiện trong dashboard. Trước khi chạy: bỏ status khỏi sort key và đưa vào WHERE của index. Bạn đoán plan có còn dòng Filter: status không?

Gợi ý: điều kiện nào của query cố định ở mọi lần chạy và loại được nửa bảng? Đưa nó vào WHERE của index thì cột đó còn cần trong sort key không?

DROP INDEX idx_tasks_dashboard_v3;
CREATE INDEX idx_tasks_dashboard_v4
  ON tasks(assignee_id, due_at)
  INCLUDE (id, title)
  WHERE status IN ('todo', 'doing');

VACUUM ANALYZE tasks;

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;   -- cung query dashboard o muc 1

 Limit
   ->  Index Only Scan using idx_tasks_dashboard_v4 on tasks
         Index Cond: ((assignee_id = 5) AND
                      (due_at >= now()) AND
                      (due_at <= now() + interval '3 days'))
         Filter: (status = ANY ('{todo,doing}'))
         Heap Fetches: 0
 Execution Time: ~1.50 ms

Planner vẫn in Filter: status dù partial index chỉ chứa row todo/doing; PG giữ bước verify này cho chắc nhưng nó chạy trên dữ liệu đã có trong leaf, không tốn I/O heap.

So sánh size:

SELECT pg_size_pretty(pg_relation_size('idx_tasks_dashboard_v3'));
-- ~80 MB (full: 1M entry, ~84 byte moi entry)

SELECT pg_size_pretty(pg_relation_size('idx_tasks_dashboard_v4'));
-- ~35 MB (partial: ~500k entry, bot cot status)

Partial index nhỏ hơn khoảng 55%: fit buffer cache tốt hơn, và INSERT/UPDATE trên row done/archived không phải đụng index này.

8. Bảng so sánh kết quả

Mục tiêu 50 ms đạt ngay tại v2. v3 và v4 là cải tiến dần, đáng tiền khi bảng tiếp tục lớn và không còn vừa RAM. Đối chiếu với 4 câu CREATE INDEX bạn viết ở mục 1: bản nào của bạn khác lời giải, và plan có đúng như bạn đoán không?

Thử ngẫmv1 chỉ nhanh 8× trong khi v2 nhanh 420×, dù cả hai đều bắt đầu bằng assignee_id. Khoảng cách đó đến từ heap fetch hay từ Sort node nhiều hơn? Nếu bảng chỉ có 2 assignee thay vì 10, v1 sẽ tệ đi hay tốt lên?

9. Pitfall — VACUUM quan trọng cho Index Only Scan

-- Simulate UPDATE lon -> visibility map cu
UPDATE tasks SET status = 'doing'
WHERE id IN (SELECT id FROM tasks WHERE status = 'todo' LIMIT 1000);

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;   -- cung query dashboard
-- Index Only Scan
-- Heap Fetches: 50  -- visibility map stale cho ~50 page

VACUUM ANALYZE tasks;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
-- Heap Fetches: 0

Production: autovacuum thường đủ cho workload bình thường; sau batch UPDATE lớn thì schedule VACUUM ANALYZE tay hoặc tune autovacuum_vacuum_scale_factor cho bảng đó (VACUUM và dead tuple).

Thử ngẫmv4 đạt 1400× nhưng chỉ vì partial index khớp đúng điều kiện status IN ('todo','doing'). Nếu dashboard sau này thêm bộ lọc theo priority nữa thì index này còn dùng được không, và bạn sẽ sửa gì trước?

10. Hướng dẫn production — quyết định index strategy

11. Deliverable — so với bản của bạn

Tạo file dashboard_tuning.sql với 4 câu CREATE INDEX do bạn viết ở mục 1, mỗi câu kèm EXPLAIN (ANALYZE, BUFFERS) và comment ghi số đo thật trên máy bạn. Khung file:

-- Section 1: seed 1M row (script Step 1) + ANALYZE
-- Section 2: baseline EXPLAIN, record: Seq Scan, ~2100ms
-- Section 3: v1 -- index cua ban + EXPLAIN, record loai scan + thoi gian
-- Section 4: v2 -- index cua ban + EXPLAIN
-- Section 5: v3 -- index cua ban + VACUUM ANALYZE + EXPLAIN, record Heap Fetches
-- Section 6: v4 -- index cua ban + VACUUM ANALYZE + EXPLAIN
-- Section 7: pg_relation_size cua v3 va v4
-- Section 8: cleanup: DROP INDEX, TRUNCATE tasks RESTART IDENTITY CASCADE

Kèm một note ngắn (comment cuối file): bản nào của bạn khác lời giải và vì sao; insight bất ngờ nhất (ví dụ khoảng cách 8× vs 420× giữa v1 và v2); và câu hỏi bạn muốn trả lời ở EXPLAIN format khi đọc cost model kỹ hơn.

12. Đi tiếp ở đâu

13. Tóm tắt

• Thứ tự cột và đưa filter vào Index Cond cho 420× (2100 ms xuống 5 ms); INCLUDE và partial chỉ đáng khi bảng không còn vừa RAM.
• Index Only Scan chỉ thật sự "only" khi Heap Fetches: 0, nên VACUUM ANALYZE sau khi tạo index và sau batch UPDATE.
• Partial index bớt ~55% size với cùng tốc độ, đổi lại mọi query muốn hưởng phải mang đúng điều kiện status.
• Trước khi thêm index ở production: đo bằng pg_stat_statements, sau 30 ngày drop index có idx_scan = 0.

Tiếp theo là module MVCC: vì sao UPDATE tạo tuple mới, và visibility map bạn vừa VACUUM ở trên thật ra ghi gì.

Bài tiếp theo: MVCC internals: xmin, xmax và snapshot. Phần ACID và isolation level (nguyên lý agnostic) nằm ở khoá trước: Isolation level và 4 anomaly.