Database index và query plan: đọc EXPLAIN không đoán mò

-- Query tìm đơn hàng của user cụ thể
SELECT * FROM orders WHERE user_id = 42 AND status = 'completed';

-- Không có index: Seq Scan, đọc 15 triệu row → 4.5 giây
-- Có index: Index Scan, đọc 12 row → 3ms
```text
Index giải quyết bằng cách tạo cấu trúc dữ liệu phụ cho phép tìm row nhanh hơn đọc tuần tự. Thay vì duyệt từ đầu đến cuối, index cho phép Postgres "nhảy" thẳng đến vị trí cần tìm, giống như mục lục cuối sách giúp bạn tìm trang nhanh hơn đọc từ trang 1.

---

## B-tree index, cấu trúc mặc định

Khi bạn chạy `CREATE INDEX` trong Postgres mà không chỉ định loại, Postgres tạo B-tree index. Đây là loại index phổ biến nhất, phù hợp cho hầu hết use case.

B-tree (balanced tree) là cây cân bằng, mỗi node chứa nhiều key đã sắp xếp, trỏ đến node con hoặc đến vị trí row trên disk. Khi tìm kiếm, Postgres bắt đầu từ root, so sánh giá trị cần tìm với key trong node, đi xuống nhánh phù hợp, lặp lại cho đến khi tìm được leaf node chứa con trỏ đến row thật.

Với bảng 10 triệu row, B-tree có chiều sâu khoảng 3-4 level. Tìm một giá trị cụ thể chỉ cần đọc 3-4 page từ disk, so với hàng nghìn page khi sequential scan. Đây là O(log n), data tăng gấp đôi, số bước tìm kiếm chỉ tăng thêm 1.

```sql
-- Tạo B-tree index (mặc định)
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Postgres dùng index cho: =, <, >, <=, >=, BETWEEN, IN, IS NULL
-- Postgres KHÔNG dùng B-tree cho: LIKE '%abc' (wildcard đầu), != (selectivity thấp)
```text
B-tree hoạt động tốt cho equality (`=`) và range query (`<`, `>`, `BETWEEN`) vì data đã sắp xếp. `WHERE user_id = 42` tìm trực tiếp vị trí. `WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'` tìm điểm bắt đầu rồi quét liên tiếp đến điểm kết thúc, rất hiệu quả vì leaf node liên kết với nhau theo thứ tự.

Nhưng `WHERE name LIKE '%john%'` thì B-tree không giúp được, pattern bắt đầu bằng wildcard không tận dụng được thứ tự sắp xếp. `WHERE name LIKE 'john%'` thì B-tree dùng được vì prefix match tương đương range scan.

---

## Single column index và khi nào nó được dùng

Tạo index trên một cột là bước đầu tiên và đơn giản nhất. Nhưng có index không có nghĩa Postgres sẽ dùng, planner quyết định dựa trên nhiều yếu tố.

```sql
CREATE INDEX idx_orders_status ON orders (status);

-- Query 1: dùng index (nếu status có selectivity cao)
SELECT * FROM orders WHERE status = 'refunded';

-- Query 2: KHÔNG dùng index (status = 'completed' chiếm 80% bảng)
SELECT * FROM orders WHERE status = 'completed';
```text
**Selectivity** là yếu tố quyết định. Selectivity đo tỷ lệ row thoả mãn điều kiện so với tổng row. `status = 'refunded'` trả về 0.1% bảng, selectivity cao, index rất hiệu quả vì chỉ cần đọc rất ít row. `status = 'completed'` trả về 80% bảng, selectivity thấp, dùng index phải nhảy giữa hàng triệu vị trí random trên disk, chậm hơn sequential scan đọc liên tục.

Quy tắc ngón tay cái: nếu query trả về hơn 10-15% bảng, Postgres thường chọn sequential scan thay vì index scan. Con số chính xác phụ thuộc nhiều yếu tố (random IO cost, bảng có nằm trong memory không), nhưng 10-15% là ước lượng hợp lý.

Postgres biết selectivity nhờ **statistics**, chạy `ANALYZE` (tự động hoặc thủ công) để Postgres sample data và tính phân phối giá trị. Statistics cũ hoặc sai dẫn đến planner chọn plan tệ. Sau khi load data lớn hoặc thay đổi phân phối dữ liệu đáng kể, chạy `ANALYZE` thủ công để cập nhật.

```sql
-- Xem statistics của cột
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
```text
---

## Composite index: thứ tự cột quyết định tất cả

Đây là phần mà mình thấy nhiều developer mắc lỗi nhất. Composite index (multi-column index) index nhiều cột cùng lúc, nhưng **thứ tự cột trong index ảnh hưởng trực tiếp đến query nào dùng được index**.

```sql
-- Composite index: user_id trước, status sau
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
```text
B-tree sắp xếp data theo thứ tự cột từ trái sang phải. Index `(user_id, status)` sắp xếp theo `user_id` trước, trong cùng `user_id` thì sắp theo `status`. Giống như danh bạ điện thoại sắp theo họ trước, trong cùng họ sắp theo tên.

**Leftmost prefix rule**: index `(user_id, status)` có thể phục vụ query filter theo `user_id` hoặc `(user_id AND status)`, nhưng **không thể** phục vụ query chỉ filter theo `status`. Lý do: data trong index sắp theo `user_id` trước, nếu bạn chỉ tìm `status = 'refunded'`, Postgres phải scan toàn bộ index vì các row cùng status nằm rải rác ở mọi user_id.

```sql
-- Dùng được index (user_id, status):
SELECT * FROM orders WHERE user_id = 42;                          -- ✓ dùng prefix user_id
SELECT * FROM orders WHERE user_id = 42 AND status = 'completed'; -- ✓ dùng cả hai cột
SELECT * FROM orders WHERE user_id = 42 AND status > 'a';         -- ✓ range trên cột thứ hai

-- KHÔNG dùng được index (user_id, status):
SELECT * FROM orders WHERE status = 'refunded';                   -- ✗ thiếu cột đầu tiên
```text
Vậy nên khi thiết kế composite index, cột **filter equality** (dùng `=`) nên đứng trước, cột **range** (dùng `<`, `>`, `BETWEEN`) hoặc cột **selectivity thấp hơn** đứng sau. Với query `WHERE user_id = 42 AND created_at BETWEEN ... AND ...`, index tối ưu là `(user_id, created_at)`, equality trên `user_id` thu hẹp phạm vi, rồi range trên `created_at` quét liên tiếp trong phạm vi đó.

Mình từng thấy team tạo index `(created_at, user_id)` cho query trên, Postgres dùng index nhưng không hiệu quả bằng: range scan trên `created_at` trả về hàng nghìn row trải trên nhiều user, rồi phải lọc thêm `user_id`. Đảo thứ tự sang `(user_id, created_at)` giảm rows scanned từ 50,000 xuống 200.

---

## Covering index, index-only scan

Khi Postgres dùng index để tìm row, nó thường phải quay lại bảng chính (heap) để đọc các cột không nằm trong index, bước này gọi là "heap fetch". Nếu bảng lớn và data nằm rải rác trên disk, heap fetch tốn IO đáng kể.

Covering index giải quyết bằng cách đưa thêm cột vào index, không phải để tìm kiếm mà để Postgres đọc luôn từ index mà không cần quay về heap. Postgres 11+ hỗ trợ `INCLUDE` clause cho mục đích này.

```sql
-- Covering index: tìm theo user_id, "bao phủ" thêm cột total, created_at
CREATE INDEX idx_orders_user_covering
  ON orders (user_id)
  INCLUDE (total, created_at);

-- Query này có thể chạy index-only scan:
SELECT user_id, total, created_at
FROM orders
WHERE user_id = 42;
```text
Khi tất cả cột mà query cần đều nằm trong index (search key + included columns), Postgres thực hiện **index-only scan**, nhanh hơn đáng kể vì không cần đọc heap. Trong `EXPLAIN ANALYZE` bạn sẽ thấy `Index Only Scan` thay vì `Index Scan`.

Có một điều kiện quan trọng cho index-only scan hoạt động hiệu quả: **visibility map** phải up-to-date. Postgres dùng MVCC, mỗi row có multiple versions. Index không biết version nào visible cho transaction hiện tại, nên phải kiểm tra heap. Visibility map đánh dấu page nào "tất cả row đều visible cho mọi transaction", chỉ khi page có flag này thì index-only scan mới skip heap fetch. `VACUUM` cập nhật visibility map, bảng ít được vacuum thì index-only scan phải fetch heap nhiều, mất lợi thế.

```sql
-- Kiểm tra tỷ lệ heap fetch trong index-only scan
EXPLAIN (ANALYZE, BUFFERS) SELECT user_id, total FROM orders WHERE user_id = 42;
-- Heap Fetches: 0  ← lý tưởng, visibility map tốt
-- Heap Fetches: 847 ← cần VACUUM
```text
Đừng lạm dụng covering index, mỗi cột thêm vào tăng kích thước index, tốn storage và chậm write. Chỉ include cột mà query thực sự cần và query đó đủ quan trọng (chạy thường xuyên hoặc nằm trên critical path).

---

## Partial index, nhỏ hơn, nhanh hơn

Partial index chỉ index **một phần** bảng, những row thoả mãn điều kiện WHERE trong `CREATE INDEX`. Index nhỏ hơn, nhanh hơn cả khi build lẫn khi scan, tốn ít storage hơn.

```sql
-- Chỉ index đơn hàng chưa xử lý (5% bảng)
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

-- Query thoả mãn điều kiện partial index:
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01';
-- → Postgres dùng idx_orders_pending, scan rất ít row

-- Query KHÔNG match:
SELECT * FROM orders WHERE status = 'completed' AND created_at > '2026-01-01';
-- → Partial index không áp dụng, cần index khác
```text
Partial index phù hợp khi bạn có query thường xuyên filter trên một subset nhỏ của bảng. Ví dụ kinh điển: bảng `jobs` với cột `status`, 95% job đã `completed`, chỉ 5% đang `pending` hoặc `running`. Full index trên `status` bao gồm cả triệu row `completed` mà bạn gần như không bao giờ query. Partial index chỉ trên `pending/running` nhỏ hơn 20 lần.

Mình dùng partial index cho unique constraint có điều kiện, ví dụ mỗi user chỉ có một đơn hàng `draft` tại một thời điểm:

```sql
CREATE UNIQUE INDEX idx_one_draft_per_user
  ON orders (user_id)
  WHERE status = 'draft';
-- User 42 không thể có 2 đơn hàng draft cùng lúc
```text
---

## Đọc EXPLAIN ANALYZE, không đoán mò

`EXPLAIN` là công cụ quan trọng nhất để hiểu Postgres đang làm gì với query. Không đoán, chạy `EXPLAIN ANALYZE` và đọc output.

```sql
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 'completed';
```text
Output trả về query plan, cây các node, mỗi node là một thao tác Postgres thực hiện. Mình sẽ đi qua từng loại scan phổ biến.

### Seq Scan, sequential scan

```text
Seq Scan on orders  (cost=0.00..458732.00 rows=12 width=128) (actual time=2145.32..4521.87 rows=12 loops=1)
  Filter: ((user_id = 42) AND (status = 'completed'))
  Rows Removed by Filter: 14999988
```text
Postgres đọc **toàn bộ** bảng, kiểm tra filter cho từng row. `Rows Removed by Filter: 14999988`, đọc gần 15 triệu row chỉ để tìm 12. Đây là dấu hiệu rõ ràng cần index.

### Index Scan

```text
Index Scan using idx_orders_user_status on orders  (cost=0.43..48.76 rows=12 width=128) (actual time=0.05..0.12 rows=12 loops=1)
  Index Cond: ((user_id = 42) AND (status = 'completed'))
```text
Postgres dùng index tìm vị trí row, rồi quay về heap đọc data. `actual time=0.05..0.12`, nhanh hơn Seq Scan hàng nghìn lần. Cost giảm từ 458,732 xuống 48.

### Index Only Scan

```text
Index Only Scan using idx_orders_covering on orders  (cost=0.43..36.21 rows=12 width=24) (actual time=0.03..0.08 rows=12 loops=1)
  Index Cond: (user_id = 42)
  Heap Fetches: 0
```text
Postgres đọc mọi thứ từ index, không quay về heap. `Heap Fetches: 0`, lý tưởng. Nhanh hơn Index Scan vì bỏ qua bước heap fetch.

### Bitmap Index Scan + Bitmap Heap Scan

```text
Bitmap Heap Scan on orders  (cost=124.56..8745.32 rows=5000 width=128) (actual time=12.34..45.67 rows=4987 loops=1)
  Recheck Cond: (user_id = 42)
  -> Bitmap Index Scan on idx_orders_user_id  (cost=0.00..123.31 rows=5000 width=0) (actual time=8.12..8.12 rows=5000 loops=1)
       Index Cond: (user_id = 42)
```text
Bitmap scan là phương án trung gian khi index scan trả về quá nhiều row (random IO đắt) nhưng sequential scan đọc quá nhiều row thừa. Postgres xây bitmap từ index, đánh dấu page nào chứa row cần thiết, rồi đọc các page đó theo thứ tự vật lý trên disk, giảm random IO. Bitmap scan xuất hiện khi selectivity trung bình, không đủ ít để index scan, không đủ nhiều để seq scan.

### Hiểu cost, rows, actual time, loops

Mỗi node trong EXPLAIN có hai phần: **estimate** (trong ngoặc tròn đầu tiên) và **actual** (ngoặc tròn thứ hai, chỉ có khi dùng `ANALYZE`).

`cost=0.43..48.76`, chi phí ước lượng. Số đầu là startup cost (trước khi trả row đầu tiên), số sau là total cost. Đơn vị là "cost unit" nội bộ của Postgres, không phải millisecond, dùng để so sánh giữa các plan, không phải đo thời gian tuyệt đối.

`rows=12`, số row Postgres **ước lượng** sẽ trả về, dựa trên statistics. Nếu con số này sai lệch nhiều so với `actual rows`, statistics cần cập nhật (`ANALYZE`).

`actual time=0.05..0.12`, thời gian thực tế tính bằng millisecond. Startup time 0.05ms, total time 0.12ms. Đây là con số bạn quan tâm nhất khi tối ưu.

`loops=1`, node này chạy bao nhiêu lần. Trong nested loop join, inner node có thể chạy hàng nghìn lần, `actual time` là thời gian **mỗi lần**, nhân với `loops` mới ra tổng thời gian thật. Đây là bẫy phổ biến khi đọc EXPLAIN: thấy `actual time=0.05` tưởng nhanh, nhưng `loops=50000` → tổng thời gian 2500ms.

```sql
-- Tip: thêm BUFFERS để thấy IO thật
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42;
-- Buffers: shared hit=48 read=3
-- hit=48: 48 page đọc từ cache
-- read=3: 3 page đọc từ disk (chậm hơn nhiều)
```text
---

## Khi Postgres chọn KHÔNG dùng index

Có index không có nghĩa Postgres sẽ dùng. Planner tính cost cho mỗi plan khả thi và chọn plan có cost thấp nhất. Có vài trường hợp Postgres bỏ qua index mà nhiều dev bất ngờ.

**Bảng nhỏ.** Bảng dưới vài nghìn row, sequential scan nhanh hơn index scan vì data nằm gọn trong vài page, đọc tuần tự nhanh hơn nhảy qua index rồi quay về heap.

**Selectivity thấp.** Query trả về hơn 10-15% bảng, sequential scan rẻ hơn vì đọc tuần tự ít random IO, trong khi index scan phải nhảy random giữa nhiều page.

**Type mismatch.** Index trên `user_id` (integer) nhưng query dùng `WHERE user_id = '42'` (string). Postgres phải cast, có thể không dùng được index. Mình từng debug 30 phút vì ORM truyền tham số sai type, thêm explicit cast fix ngay.

```sql
-- Type mismatch: index trên integer, query dùng string
SELECT * FROM orders WHERE user_id = '42';    -- có thể không dùng index
SELECT * FROM orders WHERE user_id = 42;      -- dùng index

-- Function trên cột: index trên email, query dùng LOWER()
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';  -- không dùng index trên email
SELECT * FROM users WHERE email = 'john@example.com';         -- dùng index
```text
**Function trên indexed column.** `WHERE LOWER(email) = '...'` không dùng được index trên `email` vì B-tree index lưu giá trị gốc, không lưu `LOWER(email)`. Cần expression index (phần sau).

**Implicit cast.** `WHERE varchar_col = 42`, Postgres cast `varchar_col` sang integer để so sánh, biến mỗi row thành function call, index vô dụng. Luôn so sánh cùng type.

---

## Expression index, index trên biểu thức

Khi query luôn dùng function trên cột, `LOWER()`, `DATE()`, JSON extract, tạo expression index để Postgres index kết quả của biểu thức.

```sql
-- Expression index cho case-insensitive search
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Query dùng được index:
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- Expression index cho JSONB field
CREATE INDEX idx_orders_metadata_type
  ON orders ((metadata->>'type'));

-- Query dùng được index:
SELECT * FROM orders WHERE metadata->>'type' = 'subscription';
```text
Expression index tính giá trị biểu thức lúc insert/update rồi lưu vào index. Khi query, Postgres so khớp biểu thức trong query với biểu thức trong index definition, phải **khớp chính xác**. `LOWER(email)` trong index nhưng query dùng `UPPER(email)` thì không match.

Mình hay dùng expression index cho hai trường hợp: search case-insensitive (`LOWER(email)`) và trích xuất JSON field (`metadata->>'type'`). Cả hai đều phổ biến trong ứng dụng thực tế và cải thiện performance đáng kể khi bảng lớn.

---

## GIN và GiST, index cho dữ liệu phi truyền thống

B-tree hoạt động tốt cho scalar value (số, text, date) nhưng không phù hợp cho dữ liệu phức tạp: full-text search, JSONB chứa nhiều key, array, geometric data.

### GIN (Generalized Inverted Index)

GIN index phân rã giá trị phức tạp thành nhiều key, mỗi key trỏ đến danh sách row chứa key đó, giống inverted index trong search engine.

```sql
-- GIN cho JSONB (hỗ trợ operator @>, ?, ?|, ?&)
CREATE INDEX idx_orders_metadata_gin ON orders USING GIN (metadata);

SELECT * FROM orders WHERE metadata @> '{"priority": "high"}';
-- GIN scan, rất nhanh dù JSONB chứa nhiều key khác nhau

-- GIN cho full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', body));

SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'postgresql & index');
```text
GIN index tốn nhiều thời gian build hơn B-tree và tốn storage hơn, nhưng search cực nhanh. Write chậm hơn vì mỗi row insert phải cập nhật nhiều entry trong inverted index. Postgres có `fastupdate` option (mặc định ON) buffer write rồi batch update GIN, giảm write overhead nhưng tăng nhẹ search latency cho pending entries.

### GiST (Generalized Search Tree)

GiST phù hợp cho dữ liệu có quan hệ spatial hoặc cần nearest-neighbor search: geometric point, range type, full-text search (alternative cho GIN). GiST lossy hơn GIN, có thể trả về false positive cần recheck, nhưng build nhanh hơn và support operator nhiều hơn (nearest-neighbor `<->` chỉ GiST hỗ trợ).

```sql
-- GiST cho range query
CREATE INDEX idx_events_timerange ON events USING GIST (tsrange(start_at, end_at));

SELECT * FROM events
WHERE tsrange(start_at, end_at) && tsrange('2026-05-01', '2026-05-31');
```text
Quy tắc ngón tay cái: dùng GIN cho full-text search và JSONB containment, dùng GiST khi cần nearest-neighbor hoặc overlap query trên range/geometry.

---

## Index bloat và REINDEX

Mình từng gặp bảng có index 2 GB cho 500 MB data, index to gấp 4 lần data. Nguyên nhân: Postgres MVCC giữ dead tuple trong index cho đến khi `VACUUM` dọn. Nếu bảng có nhiều UPDATE/DELETE mà VACUUM không chạy kịp, index phình lên, gọi là index bloat.

Index bloat làm scan chậm hơn vì phải đọc nhiều page hơn cần thiết. Detect bằng extension `pgstattuple`:

```sql
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstatindex('idx_orders_user_id');
-- leaf_fragmentation, avg_leaf_density, density thấp = bloat cao
```text
Hoặc so sánh kích thước index thực tế với kích thước lý thuyết. Nếu index thực tế lớn hơn 30-50% so với estimate, có bloat đáng kể.

Fix bằng `REINDEX`:

```sql
-- REINDEX lock bảng, cẩn thận trên production
REINDEX INDEX idx_orders_user_id;

-- REINDEX CONCURRENTLY (Postgres 12+), không lock, an toàn cho production
REINDEX INDEX CONCURRENTLY idx_orders_user_id;
```text
`REINDEX CONCURRENTLY` build index mới song song với index cũ, swap khi xong, xoá index cũ, tương tự `CREATE INDEX CONCURRENTLY`. Không lock table nhưng tốn thêm disk space tạm thời (có hai bản index cùng lúc) và CPU cho build.

Phòng ngừa bloat tốt hơn chữa: tune autovacuum aggressive hơn cho bảng có write nhiều. `autovacuum_vacuum_scale_factor` mặc định 0.2 (20% dead tuple mới trigger vacuum), với bảng lớn 100 triệu row, 20% = 20 triệu dead tuple trước khi vacuum chạy, quá chậm. Giảm xuống 0.01-0.05 cho bảng hot.

---

## Khi index gây hại

Index không miễn phí, mỗi index có chi phí, và có lúc chi phí vượt quá lợi ích.

**Write overhead.** Mỗi INSERT/UPDATE/DELETE phải cập nhật **tất cả** index trên bảng đó. Bảng có 10 index thì mỗi INSERT cập nhật 10 index, chậm gấp nhiều lần so với bảng có 2 index. Mình từng audit bảng `events` có 15 index (nhiều index do nhiều đợt tối ưu mà không ai dọn), drop 8 index không ai dùng, write throughput tăng 3 lần.

```sql
-- Xem index nào KHÔNG được dùng
SELECT
  schemaname, tablename, indexname,
  idx_scan,              -- số lần index được scan
  idx_tup_read,          -- số row đọc qua index
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0       -- chưa bao giờ được dùng!
ORDER BY pg_relation_size(indexrelid) DESC;
```text
Query trên cho bạn danh sách index chưa bao giờ được scan kể từ lần reset statistics gần nhất. Index không dùng = chi phí write không có lợi ích đọc. Cần cẩn thận: reset statistics (`pg_stat_reset()`) có thể xoá data, và một số index chỉ dùng cho query hiếm nhưng quan trọng (report cuối tháng). Kiểm tra kỹ trước khi drop.

**Storage cost.** Index tốn disk space, đôi khi tổng kích thước index lớn hơn data. Mỗi index cũng cần WAL khi write, tăng replication traffic. Trong cloud, storage và IOPS đều tốn tiền.

**Vacuum overhead.** VACUUM phải dọn dead tuple trong tất cả index. Bảng nhiều index thì vacuum chậm hơn, giữ lock lâu hơn. Đây là lý do nên giữ số index hợp lý, đủ cho query quan trọng, không hơn.

---

## Anti-pattern phổ biến

**Index mọi cột.** Tạo index riêng cho từng cột trong bảng vì "chắc sẽ cần". Write chậm, vacuum chậm, storage phình to, mà hầu hết index không ai dùng. Chỉ index cột mà query thực tế filter hoặc join, đọc slow query log, tìm query chậm, tạo index cho query đó.

**Thiếu composite index.** Có index riêng trên `user_id` và index riêng trên `status`, nhưng query filter cả hai: `WHERE user_id = 42 AND status = 'completed'`. Postgres có thể dùng Bitmap AND để kết hợp hai index, nhưng composite index `(user_id, status)` nhanh hơn đáng kể vì đọc một index thay vì hai rồi merge.

**Sai thứ tự cột composite.** Index `(status, user_id)` cho query `WHERE user_id = 42`, không dùng được vì thiếu cột đầu tiên `status`. Thứ tự cột phải match pattern query: equality trước, range sau, cột selectivity cao trước.

**Duplicate index.** Index `(user_id)` và index `(user_id, status)`, index thứ hai đã bao gồm chức năng của index thứ nhất (leftmost prefix). Index `(user_id)` riêng là thừa, tốn write overhead vô ích. Trừ khi index thứ nhất có thuộc tính đặc biệt (UNIQUE constraint, partial condition khác).

```sql
-- Tìm index có thể duplicate
SELECT
  a.indexname AS index_1,
  b.indexname AS index_2,
  a.indexdef
FROM pg_indexes a
JOIN pg_indexes b ON a.tablename = b.tablename
  AND a.indexname != b.indexname
  AND b.indexdef LIKE a.indexdef || '%'
WHERE a.schemaname = 'public';
```text
---

## Thực hành: tối ưu query chậm từng bước

Mình chia sẻ quy trình mà team áp dụng khi gặp query chậm trên production, không phải lý thuyết mà là các bước cụ thể.

**Bước 1: Xác định query chậm.** Bật `pg_stat_statements` extension (nên bật mặc định trên mọi Postgres production). Query xem câu nào tốn nhiều thời gian nhất:

```sql
SELECT
  query,
  calls,
  mean_exec_time,
  total_exec_time,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
```text
Ưu tiên query có `total_exec_time` cao nhất, đó là query ảnh hưởng lớn nhất đến hệ thống, dù mỗi lần chạy có thể không chậm nhưng gọi quá nhiều lần.

**Bước 2: Chạy EXPLAIN ANALYZE.** Với query đã xác định, chạy `EXPLAIN (ANALYZE, BUFFERS)` trên staging hoặc replica, không chạy ANALYZE trên production nếu query có side effect (INSERT, UPDATE, DELETE).

```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > '2026-05-01'
ORDER BY o.created_at DESC
LIMIT 20;
```text
**Bước 3: Đọc plan, tìm bottleneck.** Tìm node có `actual time` cao nhất, hoặc node có `rows` estimate sai lệch nhiều so với actual. Seq Scan trên bảng lớn, Nested Loop với loops cao, Sort trên nhiều row, đều là ứng viên tối ưu.

**Bước 4: Thêm hoặc sửa index.** Dựa trên WHERE clause và JOIN condition, tạo composite index phù hợp. Dùng `CREATE INDEX CONCURRENTLY` trên production để không lock bảng.

```sql
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC);
```text
**Bước 5: Verify.** Chạy lại `EXPLAIN ANALYZE`, confirm plan đã đổi sang Index Scan, actual time giảm. So sánh trước/sau. Nếu plan không đổi, kiểm tra `ANALYZE` đã chạy chưa (statistics cũ khiến planner không biết index mới tốt hơn).

**Bước 6: Monitor.** Sau khi deploy index, theo dõi `pg_stat_statements` vài ngày, mean exec time có giảm không, write latency có tăng đáng kể không. Index tốt giảm read time mà không tăng write time quá nhiều.

---

## Tóm tắt

B-tree index biến O(n) sequential scan thành O(log n) lookup, sự khác biệt giữa 4 giây và 3 millisecond trên bảng 15 triệu row. Composite index cần đúng thứ tự cột, equality trước, range sau, tuân theo leftmost prefix rule. Covering index với `INCLUDE` cho phép index-only scan, nhanh hơn nữa vì không cần quay về heap.

Partial index giảm kích thước index khi chỉ cần index subset data. Expression index cho phép index kết quả hàm (`LOWER()`, JSONB extract). GIN/GiST cho full-text search và JSONB containment, mỗi loại có trade-off riêng giữa build time, search speed, và write overhead.

`EXPLAIN ANALYZE` là công cụ bắt buộc, không đoán mò query chạy thế nào, đọc plan để hiểu Postgres thực sự làm gì. Chú ý `loops` nhân với `actual time`, chú ý Seq Scan trên bảng lớn, chú ý estimate `rows` sai lệch nhiều so với actual.

Index không miễn phí: mỗi index tốn write overhead, storage, vacuum time. Index không ai dùng là nợ, query `pg_stat_user_indexes` để tìm và drop. Tối ưu index là quy trình lặp: tìm query chậm → đọc EXPLAIN → thêm index → verify → monitor. Không phải "tạo index rồi quên" mà là liên tục đo lường và điều chỉnh theo workload thực tế.

---

## Tham khảo

- [PostgreSQL, Indexes](https://www.postgresql.org/docs/current/indexes.html)
- [PostgreSQL, Using EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html)
- [Use The Index, Luke, A Guide to Database Performance](https://use-the-index-luke.com/)