Index & EXPLAIN ANALYZE

Ý tưởng một câu: index là một cấu trúc dữ liệu phụ, đã sắp xếp, giúp database tìm row mà không phải đọc cả bảng, giống mục lục cuối sách. (Partition là chuyện khác: chia bảng thành nhiều bảng nhỏ, để ở bài 6.9.)

1. Bài toán

Bảng orders của shop-app có 20 triệu row. Màn hình "Đơn hàng của tôi":

SELECT id, total, status, created_at
FROM orders
WHERE user_id = 7 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;          -- mất 3 giây

2. Vấn đề

Không có index, PostgreSQL phải Seq Scan: đọc mọi page của bảng, lọc từng row, rồi sort. 20 triệu row cho 20 kết quả.

3. Giải pháp: B-tree index

B-tree trên (user_id):

                    [ 500 | 1000 ]               ← root
                 /        |         \
        [ 7 | 120 ]   [ 600 | 800 ]   [ ... ]    ← internal
         /    |
  [5,6,7,7,7] [...]                              ← leaf: key + con trỏ (ctid) tới row
       │  │  │
       ▼  ▼  ▼
   heap (bảng orders) page (12,3) (98,1) (4410,7)

Tìm user_id=7: đi từ root xuống leaf ≈ 3–4 page, thay vì đọc hàng trăm nghìn page.
Leaf nối với nhau theo thứ tự → quét range và ORDER BY được luôn.

4. Code từng bước

Bước 1: Đo trước

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, status, created_at FROM orders
WHERE user_id = 7 AND status = 'PAID'
ORDER BY created_at DESC LIMIT 20;

Limit (actual time=2980..2980 rows=20)
  -> Sort (Sort Key: created_at DESC)
     -> Seq Scan on orders (rows=20000000 → Rows Removed by Filter: 19999850)
        Buffers: shared read=250000

Bước 2: Composite index, đúng thứ tự cột

CREATE INDEX CONCURRENTLY idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);

Luật thứ tự: cột so sánh bằng (=) đặt trước, cột range/ORDER BY đặt sau. Index (a, b, c) phục vụ được a, a,b, a,b,c (leftmost prefix), nhưng hầu như không giúp WHERE b = ? một mình.

Bước 3: Đo lại

Limit (actual time=0.05..0.09 rows=20)
  -> Index Scan using idx_orders_user_status_created on orders
       Index Cond: ((user_id = 7) AND (status = 'PAID'))
       Buffers: shared hit=24
-- không còn Sort, đọc 24 page thay vì 250000

Bước 4: Covering index và partial index

-- Covering: thêm cột vào leaf để khỏi đọc heap → Index Only Scan
CREATE INDEX CONCURRENTLY idx_orders_user_created_cover
  ON orders (user_id, created_at DESC) INCLUDE (total, status);

-- Partial: chỉ index phần row hay hỏi → index nhỏ hơn nhiều
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at) WHERE status = 'PENDING';
-- phục vụ job: SELECT ... WHERE status='PENDING' AND created_at < now() - interval '15 min'

Bước 5: Khai báo trong migration (Flyway, bài 4.3.12). CONCURRENTLY không chạy được trong transaction nên tách file riêng:

-- V12__orders_user_index.sql
-- flyway:executeInTransaction=false
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);

// Repository giữ nguyên, index phục vụ query sinh ra
List<OrderSummary> findTop20ByUserIdAndStatusOrderByCreatedAtDesc(Long userId, OrderStatus status);

Bước 6: Test

curl -w '\n%{time_total}s\n' 'localhost:8080/api/users/7/orders?status=PAID'
psql -c "SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname='orders';"

5. Đọc EXPLAIN ANALYZE

Đọc từ node thụt sâu nhất ra ngoài. Mỗi node có:
  (cost=khởi_động..tổng  rows=ƯỚC_LƯỢNG)  (actual time=..  rows=THỰC_TẾ  loops=N)

Cần soi:
  1. Node nào tốn actual time nhiều nhất
  2. rows ước lượng vs thực tế lệch 100 lần? → statistics cũ → ANALYZE orders;
  3. Seq Scan trên bảng lớn + "Rows Removed by Filter" lớn → thiếu index
  4. Sort / Hash dùng "external merge Disk" → work_mem nhỏ
  5. Nested Loop với loops lớn → có thể là N+1 kiểu SQL
  6. Buffers: shared hit (RAM) vs read (đĩa)

Lưu ý: EXPLAIN ANALYZE CHẠY THẬT câu lệnh. Với UPDATE/DELETE: bọc trong BEGIN; ... ROLLBACK;

6. Khi index không được dùng

7. Khi nào nên / không nên tạo index

• Nên: cột trong WHERE/JOIN/ORDER BY của query nóng; cột foreign key (order_items.order_id): PostgreSQL không tự tạo index cho FK.
• Không nên: index mọi cột "cho chắc". Mỗi INSERT/UPDATE phải cập nhật mọi index liên quan, tốn WAL, mất HOT update (bài 6.3), tốn RAM.
• Định kỳ tìm index không dùng: idx_scan = 0 trong pg_stat_user_indexes.
• Production: luôn CREATE INDEX CONCURRENTLY để không khóa ghi cả bảng.

PostgreSQL có thể kết hợp hai index riêng bằng BitmapAnd, nhưng thường kém hơn composite:

2 index riêng:                         Composite (user_id, status, created_at):
  quét idx_user  → bitmap A              đi thẳng tới đoạn leaf (7,'PAID',...)
  quét idx_status → bitmap B (rất to)    đã sắp theo created_at → LIMIT 20 dừng sớm
  A AND B → đọc heap → vẫn phải SORT     không Sort

Thêm nữa, status có ít giá trị (selectivity thấp) nên index riêng trên nó gần như vô dụng. Quy tắc: thiết kế index theo query, không theo từng cột.

Planner chọn plan dựa vào statistics và dữ liệu thật. Local 1000 row thì Seq Scan cũng nhanh; production 20 triệu row, dữ liệu lệch (user bán sỉ có 2 triệu order) thì plan khác hẳn. Cách làm:

1. Chạy EXPLAIN (ANALYZE, BUFFERS) trên bản sao dữ liệu production (hoặc replica).
2. Bật pg_stat_statements để tìm query tốn tổng thời gian nhiều nhất, không phải query chậm nhất một lần.
3. Bật auto_explain với log_min_duration để log plan của query chậm thật.
4. Rows ước lượng lệch nhiều → ANALYZE, tăng statistics target cho cột lệch.

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Q1. Index hoạt động thế nào, đánh đổi gì?

🇻🇳 Tiếng Việt:

Index mặc định trong PostgreSQL là B-tree: cây cân bằng, leaf chứa key đã sắp xếp và con trỏ tới row trong bảng. Tìm theo key chỉ cần đi vài page từ root xuống leaf, và vì leaf có thứ tự nên dùng được cho range và ORDER BY. Đánh đổi là mỗi lần ghi phải cập nhật thêm index, tốn dung lượng và WAL, nên em chỉ tạo index theo query thật sự cần.

🇬🇧 English:

The default PostgreSQL index is a B-tree: a balanced tree whose leaves hold sorted keys and pointers to table rows. A lookup walks a few pages from root to leaf, and because leaves are ordered it also serves range queries and ORDER BY. The trade-off is that every write must also update the indexes, costing space and WAL, so I only create indexes for real query patterns.

Q2. Thứ tự cột trong composite index quan trọng thế nào?

🇻🇳 Tiếng Việt:

Index (a, b, c) được sắp theo a, rồi b, rồi c, nên chỉ hiệu quả khi query dùng tiền tố bên trái: a, hoặc a và b. Em đặt các cột so sánh bằng trước, cột range hoặc ORDER BY sau cùng; như vậy query lọc xong là đã có thứ tự, không cần sort và LIMIT dừng sớm. Nếu thêm INCLUDE các cột cần SELECT thì có thể Index Only Scan.

🇬🇧 English:

An index on (a, b, c) is sorted by a, then b, then c, so it works well only when the query uses a left prefix: a, or a and b. I put equality columns first and the range or ORDER BY column last; then the filtered rows are already ordered, no sort is needed and LIMIT can stop early. Adding the selected columns with INCLUDE can enable an Index Only Scan.

Q3. Query chậm, bạn điều tra thế nào?

🇻🇳 Tiếng Việt:

Em tìm query tốn nhiều thời gian nhất bằng pg_stat_statements, rồi chạy EXPLAIN ANALYZE với BUFFERS trên dữ liệu giống production. Em xem node nào tốn thời gian, có Seq Scan lớn với nhiều row bị loại không, rows ước lượng có lệch thực tế không, có sort ra đĩa không. Sau đó sửa bằng index phù hợp, viết lại điều kiện để index dùng được, hoặc ANALYZE lại. Index trên production em tạo bằng CREATE INDEX CONCURRENTLY.

🇬🇧 English:

I find the most expensive queries with pg_stat_statements, then run EXPLAIN ANALYZE with BUFFERS on production-like data. I check which node takes the time, whether there is a large Seq Scan removing many rows, whether estimated rows differ from actual rows, and whether sorts spill to disk. Then I add a fitting index, rewrite conditions so the index can be used, or refresh statistics with ANALYZE. On production I create indexes with CREATE INDEX CONCURRENTLY.

Cách nhớ ngắn gọn

• Index = mục lục đã sắp xếp; đọc nhanh, ghi chậm hơn.
• Composite: bằng trước, range/ORDER BY sau; leftmost prefix.
• Hàm bọc cột, LIKE '%x', selectivity thấp → không dùng index.
• EXPLAIN ANALYZE: so rows ước lượng vs thực tế, soi node tốn nhất.
• Production: CONCURRENTLY, index cột FK.