Composite index ordering — leftmost prefix rule + INCLUDE covering

CREATE INDEX ON tasks(project_id, status, due_at);

SELECT * FROM tasks WHERE status = 'done';

CREATE INDEX idx_tasks_psd ON tasks(project_id, status, due_at);

-- Entry order (sort by all 3 columns lexicographically):
(1, 'doing', 2026-05-01)
(1, 'doing', 2026-05-03)
(1, 'done',  2026-04-28)
(1, 'todo',  2026-05-02)
(2, 'doing', 2026-05-01)
(2, 'done',  2026-05-05)
(2, 'todo',  2026-04-30)
...

-- INDEX (project_id, status, due_at)

-- DUNG INDEX:
SELECT * FROM tasks WHERE project_id = 5;
-- prefix col 1 -> B-tree navigate den subtree project=5

SELECT * FROM tasks WHERE project_id = 5 AND status = 'done';
-- prefix col 1+2 -> subtree project=5, sub-subtree status='done'

SELECT * FROM tasks WHERE project_id = 5 AND status = 'done'
  AND due_at > '2026-05-01';
-- full 3 column -> narrow xuong range due_at

-- KHONG DUNG INDEX (skip col 1):
SELECT * FROM tasks WHERE status = 'done';
-- skip col 1 -> khong xac dinh duoc subtree nao

SELECT * FROM tasks WHERE due_at > '2026-05-01';
-- skip col 1 + col 2 -> khong navigate duoc

SELECT * FROM tasks WHERE status = 'done' AND due_at > '2026-05-01';
-- skip col 1 -> van khong dung duoc du co col 2+3

-- DUNG INDEX OPTIMAL: equality truoc, range sau
CREATE INDEX ON tasks(status, due_at);

SELECT * FROM tasks WHERE status = 'done' AND due_at > '2026-05-01';
-- Plan: Index Scan
-- B-tree narrow den status='done' subtree, range scan due_at trong subtree do

-- KHONG OPTIMAL: range truoc, equality sau
CREATE INDEX ON tasks(due_at, status);

SELECT * FROM tasks WHERE status = 'done' AND due_at > '2026-05-01';
-- Plan: Index Scan (range tren due_at) + Filter (status='done')
-- Doc nhieu row hon vi status filter khong dung duoc index sau range column

-- Composite (assignee_id, status, due_at): equality assignee + status, range due_at
CREATE INDEX ON tasks(assignee_id, status, due_at);

SELECT * FROM tasks
WHERE assignee_id = 5
  AND status IN ('todo', 'doing')
  AND due_at BETWEEN now() AND now() + INTERVAL '3 days';
-- B-tree: assignee_id=5 subtree -> status IN subtree -> range scan due_at
-- Optimal: filter thu hep tu trai sang phai

-- Without INCLUDE: heap fetch sau index scan
CREATE INDEX ON tasks(status, due_at);

SELECT id, title FROM tasks WHERE status = 'done' AND due_at > '2026-05-01';
-- Plan: Index Scan -> Heap Fetch (id, title khong co trong index)
-- Moi row match can 1 random heap I/O

-- With INCLUDE: cover moi column SELECT
CREATE INDEX ON tasks(status, due_at) INCLUDE (id, title);

SELECT id, title FROM tasks WHERE status = 'done' AND due_at > '2026-05-01';
-- Plan: Index Only Scan (no heap fetch neu visibility map allow)
-- Toan bo data lay tu index leaf, khong can mo heap page

Query: WHERE col1 = X [AND col2 OP Y [AND col3 OP Z]]

Heuristic chon thu tu column:
1. Equality column truoc range column
2. Trong equality column: high cardinality truoc
   (narrow subtree som, it row can scan o level sau)
3. Range column o cuoi sort key
4. ORDER BY column cuoi neu trung range column -> planner skip sort step
5. SELECT-only column -> INCLUDE (khong sort, chi stored at leaf)

-- Query: dashboard assignee
SELECT id, title 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;

-- Equality: assignee_id (cardinality cao: nhieu assignee)
--           status (cardinality thap: 3 gia tri)
-- Range:    due_at
-- ORDER BY: due_at (cung range column -> bonus: planner skip sort)
-- SELECT:   id, title -> INCLUDE

-- Optimal index:
CREATE INDEX idx_tasks_dashboard
  ON tasks(assignee_id, status, due_at)
  INCLUDE (id, title);

-- Index (a, b)
-- Query WHERE b = X (skip a) -> Seq Scan theo default

-- MySQL va Oracle co "index skip scan" native:
-- DB tu dong enumerate distinct gia tri cua a, chay scan cho moi gia tri
-- PG chua implement (roadmap, khong co ETA chinh thuc tinh den 2026)

-- Workaround PG: tao index thu 2
CREATE INDEX ON tasks(status);  -- serve WHERE status = X

SELECT id, title 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 NULLS LAST, id LIMIT 20;

-- v1: single column - poor selectivity
CREATE INDEX ON tasks(assignee_id);
-- Plan: Index Scan (lots of rows for assignee=5) + Filter status + Filter due_at + Sort
-- Estimated: ~200ms (filter hang ngan row tren heap)

-- v2: composite assignee + status
CREATE INDEX ON tasks(assignee_id, status);
-- Plan: Index Scan narrow assignee+status, then Filter due_at + Sort due_at
-- Estimated: ~30ms (it row hom nhung van can sort)

-- v3: composite full (equality + range, ORDER BY aligned)
CREATE INDEX ON tasks(assignee_id, status, due_at);
-- Plan: Index Scan narrow all 3, Sort skipped (due_at sorted in index)
-- Estimated: ~5ms (no sort step)

-- v4: voi INCLUDE - Index Only Scan
CREATE INDEX ON tasks(assignee_id, status, due_at) INCLUDE (id, title);
-- Plan: Index Only Scan - no heap fetch, sort skipped
-- Estimated: ~1ms (pure index read)