GIN/BRIN/partial/expression — chọn index theo data type và workload

-- TaskFlow: tasks_v2 co JSONB metadata
CREATE TABLE tasks_v2 (
  id          BIGSERIAL PRIMARY KEY,
  title       TEXT NOT NULL,
  metadata    JSONB,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Tao GIN index -- mac dinh operator class jsonb_ops
CREATE INDEX idx_tasks_metadata ON tasks_v2 USING GIN (metadata);

-- Hoac jsonb_path_ops: nho hon, chi ho tro @> (containment)
CREATE INDEX idx_tasks_metadata_path ON tasks_v2 USING GIN (metadata jsonb_path_ops);

-- Query containment
SELECT * FROM tasks_v2 WHERE metadata @> '{"priority":"high"}';
-- Plan: Bitmap Index Scan on idx_tasks_metadata -> Bitmap Heap Scan

Document (row) data:
  Row 1: {"priority":"high", "tag":"bug"}
  Row 2: {"priority":"low",  "tag":"feature"}
  Row 3: {"priority":"high", "tag":"feature"}

GIN index entries (element -> list row):
  "priority":"high"  ->  [row1, row3]
  "priority":"low"   ->  [row2]
  "tag":"bug"        ->  [row1]
  "tag":"feature"    ->  [row2, row3]

-- task_history: append-only audit table, 100 trieu row
CREATE TABLE task_history (
  id          BIGSERIAL PRIMARY KEY,
  task_id     BIGINT NOT NULL,
  action      TEXT NOT NULL,
  event_at    TIMESTAMPTZ DEFAULT NOW(),
  payload     JSONB
);

-- BRIN index -- pages_per_range=128 la mac dinh (128 page = ~1 MB per range)
CREATE INDEX idx_task_history_event_at ON task_history USING BRIN (event_at);

-- So sanh size
SELECT pg_size_pretty(pg_relation_size('idx_task_history_event_at'));
-- ~48 KB (so voi B-tree ~4 GB cung data)

Table: task_history (append-only by event_at)

Block range 1 (page 1-128):   event_at min='2026-01-01', max='2026-01-31'
Block range 2 (page 129-256): event_at min='2026-02-01', max='2026-02-28'
Block range 3 (page 257-384): event_at min='2026-03-01', max='2026-03-31'
...

Query: WHERE event_at > '2026-03-01'
-> BRIN check: range 1 max='2026-01-31' < threshold -> skip
-> BRIN check: range 2 max='2026-02-28' < threshold -> skip
-> BRIN check: range 3 min='2026-03-01' -> scan range 3+
-> Faster than full seq scan (skip 2/3 blocks)

-- Kiem tra correlation trong pg_stats
SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'task_history' AND attname = 'event_at';
-- correlation = 0.99 -> append-only theo thoi gian -> BRIN hieu qua

-- Neu tasks table co UPDATE lam thay doi physical order:
SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'tasks' AND attname = 'created_at';
-- correlation = 0.45 -> du lieu xao tron -> BRIN kem hieu qua

-- Chi index task chua hoan thanh (active tasks ~30% data)
CREATE INDEX idx_tasks_active_due ON tasks(due_at)
WHERE status IN ('todo', 'doing');

-- Query PHAI include WHERE clause matching de dung partial index
SELECT * FROM tasks
WHERE status IN ('todo', 'doing') AND due_at > NOW();
-- Plan: Index Scan on idx_tasks_active_due

-- Query thieu dieu kien matching -> planner KHONG dung partial
SELECT * FROM tasks WHERE due_at > NOW();
-- Plan: Seq Scan (partial index khong dam bao subset)

-- Soft delete: 95% row co deleted_at IS NULL
CREATE INDEX idx_tasks_not_deleted ON tasks(created_at)
WHERE deleted_at IS NULL;

-- CMS draft/published: 90% la draft
CREATE INDEX idx_content_published ON content(updated_at)
WHERE published = true;

-- Active task filter la query pho bien nhat
CREATE INDEX idx_tasks_assignee_active ON tasks(assignee_id, due_at)
WHERE status IN ('todo', 'doing');

-- Query case-insensitive -> B-tree tren email khong dung duoc
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Plan: Seq Scan (B-tree index tren email luu gia tri goc, khong phai LOWER())

-- Tao expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Query SAME nay dung duoc index
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Plan: Index Scan on idx_users_email_lower

-- Date truncation: group by day
CREATE INDEX idx_tasks_created_day ON tasks(date_trunc('day', created_at));
SELECT date_trunc('day', created_at), COUNT(*)
FROM tasks GROUP BY 1;
-- Index scan cho group by

-- JSONB extract: filter theo field cu the
CREATE INDEX idx_tasks_priority ON tasks((metadata->>'priority'));
SELECT * FROM tasks WHERE metadata->>'priority' = 'high';

-- Computed value
CREATE INDEX idx_order_total ON orders((price * quantity));
SELECT * FROM orders WHERE price * quantity > 1000;

-- KHONG OK: now() khong immutable (ket qua thay doi theo thoi gian)
CREATE INDEX bad_idx ON tasks((created_at - NOW()));
-- ERROR: functions in index expression must be marked IMMUTABLE

-- OK: LOWER() la immutable
CREATE INDEX ok_idx ON users(LOWER(email));

-- OK: date_trunc voi literal timezone string la immutable
CREATE INDEX ok_day_idx ON tasks(date_trunc('day', created_at));

-- Expression + INCLUDE: Index Only Scan khong can heap fetch
CREATE INDEX idx_users_email_lower_cover
ON users(LOWER(email)) INCLUDE (id, display_name);

SELECT id, display_name FROM users WHERE LOWER(email) = '[email protected]';
-- Plan: Index Only Scan (khong can fetch heap page)

Buoc 1: Data type va access pattern?
|
+-- Scalar (int, text, date) + equality / range / sort
|   -> B-tree (default)
|
+-- Multi-value: array, JSONB, tsvector
|   -> GIN
|   -> GIN jsonb_path_ops neu chi dung @> containment
|
+-- Time-series append-only, table cuc lon
|   -> BRIN (kiem tra correlation truoc)
|
+-- Geometric: point, polygon, range type
|   -> GiST
|
+-- Function transformation tren column
    -> Expression index

Buoc 2: Chi index subset row?
+-- Co WHERE clause pho bien va skip phan lon data
    -> Partial index (ket hop voi type o Buoc 1)

Buoc 3: Query fetch nhieu column tu SELECT?
+-- Co the cover het bang INCLUDE
    -> INCLUDE columns -> Index Only Scan

-- Partial + B-tree (pho bien nhat)
CREATE INDEX idx_active ON tasks(due_at) WHERE status IN ('todo', 'doing');

-- Partial + GIN
CREATE INDEX idx_meta_active ON tasks USING GIN (metadata)
WHERE status IN ('todo', 'doing');

-- Expression + INCLUDE
CREATE INDEX idx_email_cover ON users(LOWER(email)) INCLUDE (id, display_name);

-- Expression + Partial
CREATE INDEX idx_email_verified ON users(LOWER(email))
WHERE verified = true;

-- ANTI-PATTERN: GIN cho scalar TEXT equality
CREATE INDEX ON tasks USING GIN (status);  -- status la TEXT scalar
-- Sai: GIN tao inverted index cho tung ky tu / token cua status
-- Hau qua: write 5-10x cham, index lon, equality query khong nhanh hon B-tree

-- DUNG: B-tree cho scalar equality
CREATE INDEX ON tasks(status);  -- B-tree default, du dung

-- ANTI-PATTERN: BRIN cho table co nhieu UPDATE
CREATE INDEX ON tasks USING BRIN (created_at);
-- Sai: UPDATE lam xao tron physical order -> correlation thap -> BRIN vo dung
-- Fix: kiem tra correlation truoc

SELECT correlation FROM pg_stats
WHERE tablename = 'tasks' AND attname = 'created_at';
-- Neu < 0.7 -> BRIN kem hieu qua -> dung B-tree

-- ANTI-PATTERN: Partial index voi WHERE clause it gap
CREATE INDEX ON tasks(id) WHERE status = 'archived';
-- archived chi chiem 0.1% row -> partial index qua nho de co ich
-- Planner co the prefer seq scan do overhead

-- Kiem tra GIN duoc dung cho containment query
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM tasks WHERE metadata @> '{"priority":"high"}';
-- Mong doi: Bitmap Index Scan on idx_tasks_metadata_path

-- Kiem tra BRIN duoc dung cho time range scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM task_history WHERE event_at > NOW() - INTERVAL '7 days';
-- Mong doi: Bitmap Index Scan on idx_task_history_event_at

-- Kiem tra partial index duoc dung
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM tasks
WHERE status IN ('todo', 'doing') AND due_at > NOW();
-- Mong doi: Index Scan on idx_tasks_active_due

-- Kiem tra expression index duoc dung
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Mong doi: Index Scan on idx_users_email_lower