PostgreSQL Index-Only Scan: Covering index nhanh nhưng không miễn phí

PostgreSQL có thể trả kết quả trực tiếp từ index mà không đọc phần lớn dữ liệu trong heap. Nhưng tên kế hoạch Index Only Scan chưa đủ chứng minh truy vấn đã “chỉ đọc index”: hiệu quả còn phụ thuộc visibility map, nhịp ghi của bảng, VACUUM và số Heap Fetches thực tế.

Bài viết xây dựng covering index bằng INCLUDE, đọc EXPLAIN (ANALYZE, BUFFERS), giải thích MVCC/visibility map và đưa ra quy trình rollout an toàn. Nội dung bám theo tài liệu PostgreSQL hiện hành về index-only scan và covering index, visibility map, CREATE INDEX và VACUUM.

1. Vì sao một Index Scan vẫn phải đọc bảng?

Index PostgreSQL là cấu trúc tách khỏi heap. Một Index Scan tìm tuple identifier trong index rồi quay lại heap để lấy các cột còn thiếu và kiểm tra phiên bản hàng có nhìn thấy trong snapshot MVCC hay không. Khi kết quả lớn hoặc heap phân tán, các lần truy cập này có thể tạo nhiều I/O ngẫu nhiên.

Index Only Scan có thể tránh bước đó khi đồng thời thỏa hai điều kiện:

1. Access method của index có thể trả lại giá trị gốc; B-tree luôn hỗ trợ, khả năng của GiST/SP-GiST phụ thuộc operator class, còn GIN không hỗ trợ theo cách này.
2. Mọi cột query cần để lọc, sắp xếp và trả kết quả đều có trong index.

Ngay cả khi hai điều kiện trên đúng, PostgreSQL vẫn phải bảo đảm visibility. Nó dùng một bit nhỏ trong visibility map để biết toàn bộ tuple trên heap page đã visible với mọi transaction hay chưa. Bit đã set cho phép bỏ qua heap; bit chưa set buộc executor quay lại heap.

2. Ví dụ: màn hình đơn hàng gần đây

Giả sử API chạy truy vấn sau rất thường xuyên:

SELECT created_at, order_number, total_cents, status
FROM orders
WHERE tenant_id = $1
  AND created_at >= $2
ORDER BY created_at DESC
LIMIT 50;

Index tối thiểu để tìm và sắp xếp là:

CREATE INDEX CONCURRENTLY idx_orders_tenant_created
ON orders (tenant_id, created_at DESC);

Index này giúp tránh sort và thu hẹp phạm vi, nhưng executor vẫn cần heap để lấy order_number, total_cents và status. Một covering index có thể giữ ba cột đó dưới dạng payload:

CREATE INDEX CONCURRENTLY idx_orders_tenant_created_cover
ON orders (tenant_id, created_at DESC)
INCLUDE (order_number, total_cents, status);

Cột trong INCLUDE không tham gia định vị hoặc thứ tự B-tree. Chúng chỉ được lưu ở leaf tuple để query có thể lấy dữ liệu. Vì vậy hãy để cột dùng trong predicate và ordering ở phần key; payload chỉ chứa cột cần trả về.

3. INCLUDE khác index ghép ở điểm nào?

Với CREATE UNIQUE INDEX ... (email) INCLUDE (display_name), chỉ email phải duy nhất. Đây là khác biệt quan trọng so với UNIQUE (email, display_name). Payload cũng không được dùng làm điều kiện tìm kiếm trực tiếp của index; nếu workload thường xuyên lọc theo nó, thiết kế key cần được đánh giá lại.

4. Đọc đúng EXPLAIN: nhìn Heap Fetches và buffers

EXPLAIN (ANALYZE, BUFFERS)
SELECT created_at, order_number, total_cents, status
FROM orders
WHERE tenant_id = 42
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

Một kế hoạch tốt có thể chứa:

Index Only Scan using idx_orders_tenant_created_cover on orders
  Index Cond: ((tenant_id = 42) AND (created_at >= ...))
  Heap Fetches: 0
  Buffers: shared hit=...

Đánh giá bằng nhiều tín hiệu, không chỉ tên node:

• Actual time, rows, loops: latency và số hàng thật có đúng kỳ vọng.
• Heap Fetches: càng gần 0 càng chứng minh visibility map giúp tránh heap.
• Buffers: so sánh shared hit/read trước và sau trên workload đại diện.
• Planning time và execution time: đo riêng, chạy nhiều lần, phân biệt cold cache với warm cache.
• Rows Removed by Filter: nếu lớn, index key có thể chưa khớp predicate.

EXPLAIN ANALYZE thực thi câu lệnh. Với DML, phải dùng transaction có rollback hoặc môi trường thử nghiệm; ví dụ ở đây là SELECT an toàn.

5. Visibility map là điều kiện vận hành

Mỗi heap relation có visibility map riêng với hai bit trên mỗi heap page: all-visible và all-frozen. Index-only scan quan tâm bit all-visible. VACUUM có thể set bit khi chứng minh page an toàn; INSERT, UPDATE hoặc DELETE trên page sẽ clear bit tương ứng.

Vì vậy cùng một index có thể rất hiệu quả trên dữ liệu lịch sử ổn định nhưng vẫn có nhiều heap fetch ở “đuôi nóng” vừa ghi. Đây không phải lỗi planner. Nó phản ánh đúng MVCC và workload:

• bảng append-heavy, phần cũ ít đổi thường hưởng lợi tốt;
• bảng cập nhật liên tục trên phần lớn page có thể ít all-visible;
• autovacuum chậm, transaction dài hoặc replication slot giữ horizon có thể làm quá trình dọn dẹp tụt lại;
• chạy VACUUM thủ công để benchmark có thể làm kết quả đẹp tạm thời nhưng không đại diện vận hành hàng ngày.

6. Đo mức độ all-visible thay vì đoán

pg_visibility cho phép kiểm tra visibility map nhưng thường cần quyền cao; chỉ bật extension và cấp quyền theo quy trình quản trị:

CREATE EXTENSION IF NOT EXISTS pg_visibility;

SELECT
  count(*) FILTER (WHERE all_visible) AS all_visible_pages,
  count(*) AS total_pages,
  round(
    100.0 * count(*) FILTER (WHERE all_visible)
    / NULLIF(count(*), 0), 2
  ) AS all_visible_pct
FROM pg_visibility_map('public.orders'::regclass);

Kết hợp số này với pg_stat_all_tables, lịch autovacuum và kế hoạch thật. Một tỷ lệ toàn bảng cao chưa chắc giúp query nếu query tập trung đúng vùng page mới ghi; ngược lại, query lịch sử có thể hưởng lợi dù phần đuôi nóng thường xuyên mất bit.

7. Payload rộng có thể biến tối ưu thành gánh nặng

Mỗi cột INCLUDE làm dữ liệu lặp lại trong index. Hậu quả là index lớn hơn, cache chứa ít page hữu ích hơn, write tạo thêm WAL và vacuum/reindex tốn công hơn. Index tuple quá lớn còn có thể làm INSERT thất bại theo giới hạn của access method.

Tránh include JSON document, text dài, blob hoặc mọi cột “phòng khi cần”. Chỉ cover query:

• chạy thường xuyên và có giá trị kinh doanh rõ;
• đọc đủ nhiều hàng để heap access là chi phí đáng kể;
• trả payload hẹp, tương đối ổn định;
• đã có baseline latency, buffers và write cost.

Nếu query chỉ trả một hàng theo primary key và toàn bộ working set đã ở cache, covering index lớn hơn có thể không đem lại lợi ích đáng kể.

8. UPDATE payload vẫn làm tăng chi phí ghi

Cột INCLUDE là dữ liệu của index. Khi payload thay đổi, PostgreSQL phải duy trì index tương ứng. Điều này cũng có thể làm UPDATE không đủ điều kiện cho HOT, vì HOT yêu cầu không sửa cột được index tham chiếu. Cover một cột như status trong bảng mà status đổi nhiều lần có thể đổi latency đọc lấy write amplification, WAL và bloat.

Trước khi thêm payload, kiểm tra tần suất đổi của từng cột và tỷ lệ HOT hiện tại. Một lựa chọn thực dụng là giữ index hẹp cho vùng transactional nóng, còn dashboard tổng hợp dùng read model, materialized view hoặc bảng lịch sử ít đổi.

9. Planner không bắt buộc chọn Index Only Scan

Cover đủ cột chỉ làm kế hoạch này khả thi. Planner vẫn có thể chọn sequential scan, bitmap heap scan hoặc index scan thường dựa trên selectivity, kích thước bảng, correlation, thống kê và cost settings. Không tắt enable_seqscan ở production để ép kế hoạch; đó chỉ là công cụ chẩn đoán hạn chế.

Sau khi tạo index, bảo đảm statistics đủ mới bằng autovacuum/analyze hoặc chạy ANALYZE có chủ đích. Nếu estimate sai, xem distribution, extended statistics và cách viết predicate trước khi tăng thêm index.

10. Rollout index trên production

1. Lấy baseline bằng query thật hoặc bản sao tham số đại diện; ghi latency percentile, buffers, rows và plan.
2. Kiểm tra index hiện có để tránh tạo một biến thể trùng hoặc index có prefix tương đương.
3. Ước lượng dung lượng, WAL và headroom trên primary lẫn replica.
4. Dùng CREATE INDEX CONCURRENTLY để không chặn write theo cách build thường, nhưng chuẩn bị cho thời gian build dài hơn và tải I/O/CPU.
5. Theo dõi tiến độ qua pg_stat_progress_create_index, replica lag và latency ứng dụng.
6. Nếu build lỗi, kiểm tra index invalid trước khi retry; không coi tên index tồn tại là thành công.
7. Chạy lại benchmark ở trạng thái cache và write load đại diện; so Heap Fetches, buffers và write cost.
8. Giữ cửa sổ quan sát rồi mới xóa index cũ. DROP INDEX CONCURRENTLY có giới hạn riêng và không chạy trong transaction block.

11. Monitoring sau khi triển khai

Không có một metric duy nhất chứng minh index tốt. Dashboard nên kết hợp:

• latency và throughput của query mục tiêu từ pg_stat_statements;
• kế hoạch định kỳ có kiểm soát, đặc biệt Heap Fetches và buffers;
• idx_scan trong pg_stat_user_indexes, nhưng nhớ counter có thể reset và một scan không biểu thị lượng công việc;
• kích thước qua pg_relation_size(), WAL rate, checkpoint pressure và replica lag;
• dead tuples, thời điểm autovacuum/analyze gần nhất và transaction lâu;
• write latency cùng HOT update ratio của bảng.

Nếu index hầu như không được dùng, lợi ích đọc không đạt SLO hoặc chi phí ghi vượt ngân sách, hãy coi việc gỡ bỏ là một phần bình thường của vòng đời tối ưu.

12. Những lỗi thiết kế thường gặp

• Thấy chữ Index Only Scan rồi bỏ qua Heap Fetches.
• Đưa mọi cột SELECT vào INCLUDE mà không đo kích thước và write cost.
• Đặt cột cần lọc thường xuyên vào payload thay vì key.
• Nhầm included column là một phần của unique constraint.
• Benchmark ngay sau VACUUM thủ công rồi suy ra hiệu năng ổn định.
• Tạo index thường trên bảng nóng mà không đánh giá lock.
• Tắt planner option hoặc thay global cost settings để chữa một query.
• Xóa index cũ ngay khi index mới vừa build xong, chưa quan sát replica và workload.

Checklist production

• Query mục tiêu và tham số đại diện đã có baseline.
• Key columns khớp predicate/order; payload chỉ gồm cột cần trả về.
• Không include dữ liệu rộng hoặc thay đổi thường xuyên nếu chưa chứng minh lợi ích.
• Đã đo all-visible/heap fetch trong điều kiện workload bình thường.
• Index được build concurrent với headroom và giám sát replica lag.
• Đã xác minh index valid và planner thực sự dùng nó.
• Đã so sánh latency, buffers, WAL, write latency và HOT ratio.
• Autovacuum không bị transaction/slot lâu giữ chân.
• Có tiêu chí rollback và thời điểm đánh giá xóa index cũ.

Kết luận

Covering index không phải mẹo thêm INCLUDE để mọi query nhanh hơn. Nó là thỏa thuận giữa access pattern, kích thước payload, nhịp ghi và khả năng duy trì visibility map. Hãy thiết kế key đúng mục đích, đọc Heap Fetches cùng buffers, đo trong workload thật và tính cả chi phí write. Khi bảng có nhiều page ổn định và query nóng cần một payload hẹp, index-only scan có thể loại bỏ lượng lớn heap I/O với thay đổi kiến trúc rất nhỏ.