Tại sao cần index — heap scan 800ms vs index scan 0.2ms

Heap (tasks table):
[Page 1] row(id=42, title='Fix login'), row(id=7, title='Write docs'), row(id=99, title='Deploy')
[Page 2] row(id=15, title='Review PR'), row(id=3, title='Setup CI'), row(id=88, title='Load test')
[Page 3] row(id=1, title='Init repo'), row(id=55, title='Write tests'), ...
...
[Page N] ... (10 million rows total, no ordering)

B-tree index ON tasks(id):

            [Root: 5000000]
           /               \
   [Node: 1-2500000]   [Node: 2500001-10000000]
        |                       |
[Leaf: 1,3,7,15,42,...]  [Leaf: 2500001,2500003,...]
      |      |                  |
  (ptr: P1   (ptr: P2       (ptr: P47
   off=2)     off=0)         off=1)

-- Tao bang demo 10 trieu row
CREATE TABLE tasks_demo (
  id BIGSERIAL PRIMARY KEY,
  title TEXT,
  status TEXT,
  due_at TIMESTAMPTZ
);

INSERT INTO tasks_demo(title, status, due_at)
SELECT
  'Task ' || g,
  (ARRAY['todo','doing','done'])[1 + (random() * 2)::int],
  now() + (random() * 30) * INTERVAL '1 day'
FROM generate_series(1, 10000000) g;

CREATE INDEX idx_tasks_demo_status ON tasks_demo(status);
CREATE INDEX idx_tasks_demo_due    ON tasks_demo(due_at);

ANALYZE tasks_demo;  -- cap nhat statistics sau bulk insert

-- Query khong co WHERE -> bat buoc Seq Scan
EXPLAIN ANALYZE SELECT * FROM tasks_demo;
-- Seq Scan on tasks_demo
-- actual time=0.05..800.00 rows=10000000
-- Buffers: shared hit=88496 (doc ~88k page)

-- Query tren column khong co index
EXPLAIN ANALYZE SELECT * FROM tasks_demo WHERE title = 'Task 12345';
-- Seq Scan (khong co index tren title)
-- actual time=0.05..750.00 rows=1

-- Selectivity cao: tim 1 row trong 10 trieu
EXPLAIN ANALYZE SELECT * FROM tasks_demo WHERE id = 12345;
-- Index Scan using tasks_demo_pkey on tasks_demo
-- Index Cond: (id = 12345)
-- actual time=0.05..0.20 rows=1
-- Buffers: shared hit=4 (chi doc 4 page: 3 B-tree node + 1 heap page)

-- Selectivity thap: status = 'todo' ~ 33% row -> planner co the chon Seq Scan
EXPLAIN ANALYZE SELECT * FROM tasks_demo WHERE status = 'todo';
-- Seq Scan (33% = ~3.3 trieu row, random fetch 3.3M heap page dat hon seq scan)

-- Index co du moi column query can (status filter + id return)
CREATE INDEX idx_tasks_status_id
  ON tasks_demo(status)
  INCLUDE (id);

VACUUM ANALYZE tasks_demo;  -- can thiet: cap nhat visibility map

EXPLAIN ANALYZE SELECT id FROM tasks_demo WHERE status = 'done';
-- Index Only Scan using idx_tasks_status_id on tasks_demo
-- actual time=0.05..12.00 rows=3333000
-- Heap Fetches: 0  (khong touch heap)

-- TaskFlow: 100k task, query dashboard theo assignee
-- Truoc khi co index
EXPLAIN ANALYZE
SELECT * FROM tasks
WHERE assignee_id = 5;
-- Seq Scan on tasks
-- actual time=5.00..250.00 rows=412 loops=1
-- Planning Time: 0.5 ms
-- Execution Time: 250.3 ms

-- Tao index
CREATE INDEX idx_tasks_assignee ON tasks(assignee_id);
ANALYZE tasks;  -- cap nhat statistics ngay sau khi tao index

-- Sau khi co index
EXPLAIN ANALYZE
SELECT * FROM tasks
WHERE assignee_id = 5;
-- Index Scan using idx_tasks_assignee on tasks
-- Index Cond: (assignee_id = 5)
-- actual time=0.05..1.20 rows=412 loops=1
-- Planning Time: 0.3 ms
-- Execution Time: 1.2 ms

-- 1. Selectivity thap: status = 'todo' chiem ~33% bang
--    Planner chon Seq Scan vi random index fetch 3.3M heap page dat hon seq scan
EXPLAIN ANALYZE SELECT * FROM tasks WHERE status = 'todo';
-- Seq Scan (du co idx_tasks_demo_status)

-- 2. Function tren indexed column: index tren email, query dung LOWER()
--    B-tree index luu gia tri goc, khong luu LOWER() gia tri
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Seq Scan (index tren email khong dung cho LOWER(email))

-- Fix: tao expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Index Scan using idx_users_email_lower

-- 3. Implicit cast mismatch: id BIGINT, query compare voi text
SELECT * FROM tasks WHERE id::text = '12345';
-- Seq Scan: cast BIGINT -> TEXT, index tren BIGINT khong applicable
-- Fix: bo cast, compare dung type
SELECT * FROM tasks WHERE id = 12345;
-- Index Scan

-- 4. Leading wildcard LIKE: % dau chuoi -> B-tree khong biet bat dau tu dau
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Seq Scan: B-tree chi support prefix search (LIKE 'foo%'), khong support suffix
-- Fix: pg_trgm GIN index (Module 9 cua khoa nay Full Text Search deep)

-- Tao index (block writes trong luc build)
CREATE INDEX idx_tasks_due ON tasks(due_at);

-- Tao CONCURRENTLY: khong block writes, nen dung tren production
-- Lau hon (phai doc heap 2 lan), nhung safe
CREATE INDEX CONCURRENTLY idx_tasks_due ON tasks(due_at);

-- Xoa
DROP INDEX idx_tasks_due;
DROP INDEX CONCURRENTLY idx_tasks_due;  -- khong block reads

-- Phat hien index khong duoc dung (idx_scan = 0 sau khi deploy du lau)
SELECT schemaname, tablename, indexname,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild index bi bloat (forward Module 11)
REINDEX INDEX CONCURRENTLY idx_tasks_due;

SELECT id, title, project_id, due_at
FROM tasks
WHERE assignee_id = $1
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '7 days'
ORDER BY due_at;

CREATE INDEX ON tasks(assignee_id);
-- PG dung Index Scan tren assignee_id, roi filter status + due_at tren heap
-- Ket qua: 200ms (filter hang ngan row sau khi fetch heap)

CREATE INDEX ON tasks(assignee_id, status, due_at);
-- PG dung Index Scan, filter status va due_at trong index (khong can fetch heap row thua)
-- Ket qua: ~5ms

CREATE INDEX ON tasks(assignee_id, status, due_at) INCLUDE (project_id, title);
-- PG dung Index Only Scan: moi column SELECT (id, title, project_id, due_at)
--   deu co trong index -> khong can fetch heap
-- Ket qua: ~1ms