Partial, expression và covering index trong PostgreSQL Cơ bản

🎮 Thử tay

• Chọn loại index: partial, expression, covering trong tình huống thật.

Tóm tắt nhanh

Ba biến thể B-tree cho ba vấn đề khác nhau. Khác nhau ở việc entry chứa dòng nào và chứa gì, cấu trúc cây vẫn là B-tree.

Biến thể	Giải quyết	Index chứa	Query phải
Partial WHERE ...	Chỉ một thiểu số dòng được hỏi; unique có điều kiện	Key của dòng thoả predicate	WHERE suy ra được predicate
Expression (f(col))	Tìm theo kết quả biểu thức (lower(email))	Giá trị biểu thức	Dùng đúng biểu thức
Covering INCLUDE	Tránh ghé heap cho vài cột nhỏ	Key + payload đính kèm	Cần Index Only Scan và visibility map sạch

Đang tải diagram…

Mock: ba index nhìn bảng ra sao

Bảng job (id, status, created_at). Cột “Full” là index đầy đủ trên created_at; “Partial” chỉ giữ dòng pending:

id	status	Full (created_at)	Partial WHERE status='pending'
1	done	có	không
2	done	có	không
3	pending	có	có
4	done	có	không
5	pending	có	có

Covering: leaf entry của (tenant_id, created_at) INCLUDE (status) là (42, 10:00, [status = open]). Payload đi cùng entry, không tham gia sắp xếp.

1. Partial index

CREATE INDEX ON job_demo (created_at DESC) WHERE status = 'pending';

Bảng 100.000 dòng, 1% là pending. So với index đầy đủ trên cùng cột:

           relname            | index_size
------------------------------+------------
 job_demo_pending_created_idx | 40 kB
 job_demo_created_idx_full    | 2208 kB

40 kB so với 2208 kB, nhỏ hơn khoảng 55 lần. Dữ liệu chỉ 1% nên về lý thuyết gần 100 lần; phần chênh là overhead cố định (metapage, root page) của index nhỏ.

Index Scan using job_demo_pending_created_idx on job_demo  (cost=0.28..46.38 rows=940 width=12) (actual time=0.009..0.011 rows=10 loops=1)
Execution Time: 0.017 ms

Khi nào planner không dùng được partial index

WHERE của query phải suy ra predicate của index. Plan thật (100.000 dòng, 1% pending):

Query	Plan	Vì sao
status = 'pending' (literal)	Index Scan using job_pending	Khớp predicate
status = $1 + custom plan	Index Scan using job_pending	Giá trị 'pending' biết lúc lập plan
status = $1 + generic plan	Sort + Seq Scan, Filter: (status = $1)	Chưa biết giá trị, không chứng minh được
status <> 'done'	Sort + Seq Scan	Không suy ra = 'pending' vì còn running, failed…

Dòng <> là vấn đề logic, không phải planner kém: index không chứa đủ dòng cho điều kiện đó.

Bẫy production: ORM hoặc pgbouncer dùng prepared statement. Với plan_cache_mode = auto, 5 lần chạy đầu dùng custom plan rồi PostgreSQL có thể chuyển sang generic plan, khiến partial index âm thầm biến mất. Chẩn đoán bằng SET plan_cache_mode = force_generic_plan; sửa bằng literal trong query hoặc force_custom_plan cho phiên liên quan.

Partial UNIQUE: soft delete và ràng buộc có điều kiện

Ứng dụng đánh dấu xoá bằng deleted_at, nhưng email chỉ được trùng khi bản cũ đã xoá. Unique thường không làm được. Partial unique làm được:

CREATE UNIQUE INDEX app_user_email_active ON app_user (email) WHERE deleted_at IS NULL;

INSERT INTO app_user (email) VALUES ('a@x.com');   -- OK
INSERT INTO app_user (email) VALUES ('a@x.com');
ERROR:  duplicate key value violates unique constraint "app_user_email_active"
UPDATE app_user SET deleted_at = now() WHERE email = 'a@x.com';
INSERT INTO app_user (email) VALUES ('a@x.com');   -- OK: bản cũ đã xoá mềm

 id |  email  | deleted
----+---------+---------
  1 | a@x.com | t
  3 | a@x.com | f

Đây là use case partial index quan trọng nhất sau queue pending.

Queue/outbox: index chỉ cho việc chưa làm

Bảng outbox hàng trăm triệu dòng nhưng worker chỉ hỏi dòng pending. Index partial WHERE status = 'pending' có kích thước tỉ lệ với backlog, không tỉ lệ với lịch sử, và luôn nhỏ nên nằm gọn trong cache. Xem thêm SKIP LOCKED.

2. Expression index

CREATE INDEX ON user_demo (lower(email));

So sánh công bằng, cả ba query đều trả đúng một dòng, bảng 100.000 dòng:

Query	Plan	Thời gian
lower(email) = '...', chưa có index biểu thức	Seq Scan, ước lượng rows=500	11.682 ms
lower(email) = '...', có index, đã ANALYZE	Index Scan, ước lượng rows=1	0.037 ms
email = 'User42000@...' (nguyên văn), chỉ có index trên lower(email)	Seq Scan	3.149 ms

Hàng đầu so với hàng hai: nhanh hơn khoảng 316 lần. Hàng ba: index trên lower(email) không sắp theo giá trị email gốc, nên cách viết khác (dù cùng trả một dòng) rơi về Seq Scan. PostgreSQL không tự đảo biểu thức.

Index Scan using user_lower_email on user_demo  (cost=0.42..8.44 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=1)
  Index Cond: (lower(email) = 'user42000@example.test'::text)

Ba điều cần nhớ:

• Phải ANALYZE sau khi tạo. Ngay sau CREATE INDEX ước lượng vẫn là rows=500 (Bitmap Scan); sau ANALYZE mới có thống kê cho biểu thức và về rows=1.
• Biểu thức phải IMMUTABLE. Hàm như now() bị từ chối: ERROR: functions in index expression must be marked IMMUTABLE.
• Thay thế: kiểu citext (extension) cho so sánh không phân biệt hoa thường mà không cần bọc lower() ở mọi query.

3. Covering index (INCLUDE)

Cùng bảng activity_cover_demo, cùng query, chỉ khác index.

Không INCLUDE, cần cột status nên phải ghé heap:

CREATE INDEX ON activity_cover_demo (tenant_id, created_at DESC);

Index Scan using activity_cover_tenant_created_noinclude_idx on activity_cover_demo
  (cost=0.42..2492.96 rows=973 width=17) (actual time=0.014..0.016 rows=10 loops=1)
  Index Cond: (tenant_id = 42)

Có INCLUDE (id, status):

CREATE INDEX ON activity_cover_demo (tenant_id, created_at DESC) INCLUDE (id, status);

Index Only Scan using activity_cover_tenant_created_idx on activity_cover_demo
  (cost=0.42..45.97 rows=1003 width=17) (actual time=0.014..0.018 rows=10 loops=1)
  Index Cond: (tenant_id = 42)
  Heap Fetches: 0

INCLUDE (PG11 trở lên) là payload đi kèm leaf entry, không tham gia sắp xếp hay tìm kiếm.

Heap Fetches: 0 phụ thuộc visibility map

Index Only Scan vẫn cần biết dòng có hiển thị theo MVCC hay không, dựa vào visibility map. Đo thật, query lấy 1.000 dòng của một tenant:

Trạng thái bảng	Plan	Heap Fetches	Thời gian
Vừa VACUUM	Index Only Scan	0	0.243 ms
Sau UPDATE 20% bảng, chưa VACUUM	Index Only Scan	1.000	3.649 ms
Sau VACUUM	Index Only Scan	0	0.139 ms

Bảng ghi liên tục mà autovacuum không theo kịp sẽ mất lợi ích “index only” dù tên node không đổi. Xem vacuum và bloat.

Cái giá ẩn: INCLUDE chặn HOT update

Cột có trong index (cả key lẫn INCLUDE) làm update cột đó không thể là HOT (Heap-Only Tuple), phải ghi thêm entry vào mọi index. Đo thật, 5.000 UPDATE status trên 20.000 dòng (fillfactor = 70):

Index	n_tup_upd	n_tup_hot_upd
status không nằm trong index	5.000	2.184
status nằm trong INCLUDE	5.000	0

HOT còn phụ thuộc chỗ trống trong page nên không đạt 5.000 ngay cả ở hàng đầu. Điểm chính: đừng INCLUDE cột hay bị update.

INCLUDE hay thêm vào key?

	Thêm vào key	INCLUDE
Dùng để lọc/sắp xếp	Có	Không
Ràng buộc UNIQUE	Cột đó thành một phần điều kiện unique	Không ảnh hưởng unique
Dùng khi	Cột nằm trong WHERE/ORDER BY	Chỉ cần trả ra cột trong SELECT

Giới hạn kích thước entry

Mỗi entry B-tree không thể vượt khoảng 1/3 page. Index giá trị dài báo lỗi thật:

ERROR:  index row size 3216 exceeds btree version 4 maximum 2704 for index "big_key_v_idx"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

Đừng INCLUDE cột văn bản lớn; dùng hash biểu thức hoặc full-text.

Ví dụ thực tế

Hệ thống đăng ký tài khoản có soft delete

Bảng app_user 20 triệu dòng, 15% đã xoá mềm. Ba yêu cầu: email duy nhất giữa các tài khoản đang hoạt động, đăng nhập không phân biệt hoa thường, danh sách “người dùng mới” chỉ lấy tài khoản chưa xoá.

Giải pháp: CREATE UNIQUE INDEX ON app_user (lower(email)) WHERE deleted_at IS NULL (phải là unique index; ràng buộc UNIQUE trong CREATE TABLE không nhận biểu thức hay WHERE) kết hợp partial và expression trong một index, đồng thời phục vụ ràng buộc lẫn tra cứu đăng nhập. Query phải viết WHERE lower(email) = $1 AND deleted_at IS NULL để khớp cả hai.

Trade-offs

Ưu điểm

• Partial index nhỏ theo tỉ lệ dòng mục tiêu, và là cách đơn giản nhất làm unique có điều kiện (soft delete); EXCLUDE ... WHERE cũng làm được nhưng nặng hơn
• Expression index index hoá kết quả tính toán, nhanh hơn hàng trăm lần so với Seq Scan trong demo
• INCLUDE cho Index Only Scan mà không đưa cột payload vào key

Nhược điểm

• Partial index bị bỏ qua khi planner không chứng minh được (generic plan, <>), khó nhận ra trong production
• Expression index chỉ phục vụ đúng biểu thức; phải ANALYZE và biểu thức phải IMMUTABLE
• INCLUDE làm index lớn, chặn HOT update của cột đó, và lợi ích phụ thuộc visibility map

Checklist production

1. Partial index: chạy EXPLAIN với prepared statement (generic plan) để chắc index vẫn được dùng.
2. Soft delete: dùng CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL, đừng để ứng dụng tự kiểm tra trùng.
3. Expression index: ANALYZE ngay sau khi tạo; kiểm tra query dùng đúng biểu thức.
4. Covering: kiểm tra Heap Fetches sau tải thật, không chỉ sau VACUUM.
5. Đừng INCLUDE cột hay bị update (mất HOT) hoặc cột văn bản lớn.
6. Dùng CREATE INDEX CONCURRENTLY trên bảng đang chạy và kiểm tra indisvalid.

Sai lầm thường gặp

Sai lầm thường gặp

Cho rằng INCLUDE (status) lọc được theo status như key. Cột INCLUDE không tham gia điều hướng B-tree, chỉ đọc ra để trả kết quả. Muốn lọc theo status, đưa nó vào phần key (xem thứ tự cột).

Tóm tắt

Tóm tắt

• Cả ba vẫn là B-tree, khác nội dung entry: partial giới hạn theo dòng, expression theo biểu thức, covering thêm payload
• Partial: 40 kB so với 2208 kB trong demo; là cách làm UNIQUE có điều kiện cho soft delete
• Partial bị bỏ qua với generic plan và <>: kiểm tra bằng EXPLAIN trên prepared statement
• Expression: đúng biểu thức 0.037 ms, chưa có index 11.682 ms; cần ANALYZE và biểu thức IMMUTABLE
• Index Only Scan phụ thuộc visibility map (Heap Fetches 0, rồi 1.000 sau update, rồi 0 sau VACUUM) và INCLUDE chặn HOT update

Tự kiểm tra

1. Vì sao WHERE status <> 'done' không dùng được partial index WHERE status = 'pending'?
2. Viết index đảm bảo email duy nhất giữa các tài khoản chưa xoá mềm.
3. Vì sao Index Only Scan có thể vẫn có Heap Fetches lớn?
4. Vì sao INCLUDE (status) làm giảm số HOT update?

Tham khảo

• PostgreSQL 17 — Partial Indexes
• PostgreSQL 17 — Indexes on Expressions
• PostgreSQL 17 — Index-Only Scans and Covering Indexes
• PostgreSQL 17 — Unique Indexes