Vì sao có index rồi mà PostgreSQL vẫn quét toàn bộ bảng?

Đợt rà soát dung lượng quý ấy không bắt đầu từ sự cố nào cả. Chỉ là bảng postings, nơi giữ bút toán hai năm gần nhất, đã phình tới mức tôi muốn biết chỗ nào ăn đĩa nhiều nhất trước khi đi xin thêm chỗ.

Bảng có mười một index. Cái to nhất 214 MB, xấp xỉ một phần ba phần dữ liệu thật. Tôi mở pg_stat_user_indexes ra xem cái nào đáng đồng tiền:

SELECT s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS kich_thuoc
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.relname = 'postings'
  AND NOT i.indisunique AND NOT i.indisprimary
ORDER BY s.idx_scan;

Bốn dòng đầu có idx_scan bằng 0. Không phải ít dùng, mà là chưa một lần nào kể từ ngày bộ đếm được reset. Một trong bốn cái đó do chính tôi tạo mấy tháng trước, cho một câu truy vấn kết xuất mà tôi nhớ rõ là đã chậm thật.

Bạn junior ngồi cùng bàn hỏi đúng câu đáng hỏi nhất lúc đó: xoá đi thì query nào chậm lại? Tôi không trả lời được. Cả bốn index ấy đều sinh ra để đỡ cho một câu truy vấn có thật, và cả bốn đều nằm đúng trên cột mà câu truy vấn ấy đang lọc. Có index, đúng cột, số lần dùng vẫn tròn 0.

Bệnh thứ nhất: đúng cột, sai trục

Một hàm bọc quanh cột trong WHERE đẩy index trên cột đó ra ngoài cuộc chơi: B-tree sắp thứ tự theo giá trị gốc, còn predicate lại hỏi một giá trị dẫn xuất mà index chưa từng sắp. Câu truy vấn đẻ ra index đầu tiên rơi đúng vào bẫy này — nó là kết xuất sao kê theo ngày:

SELECT count(*), sum(amount)
FROM postings
WHERE DATE(posted_at) = DATE '2026-05-04';

Nhìn thì khớp: index nằm trên posted_at, WHERE cũng lọc posted_at. Nhưng đọc chậm lại một nhịp thì không phải vậy. WHERE không lọc theo posted_at, nó lọc theo DATE(posted_at), và đấy là một giá trị khác.

B-tree vốn chỉ là một danh sách đã sắp thứ tự cộng thêm cách nhảy vào giữa danh sách ấy cho nhanh. Index này sắp mọi dòng theo posted_at, chính xác tới từng phần triệu giây. Thứ câu truy vấn hỏi lại là giá trị sau khi cắt bỏ toàn bộ phần thời gian, cả giờ lẫn phút lẫn giây. Trên trục thứ tự mà index đã dựng, giá trị ấy không tồn tại: muốn biết một dòng có khớp hay không thì phải đọc dòng đó ra rồi mới tính được DATE() của nó. Mà đọc mọi dòng ra thì đúng bằng định nghĩa của Seq Scan.

Cứ hình dung một cuốn danh bạ xếp theo ngày sinh đầy đủ. Hỏi "ai sinh vào tháng Năm" thì cuốn danh bạ ấy vô dụng, không phải vì nó xếp sai, mà vì nó xếp theo một trục khác với trục bạn đang hỏi.

Trên bảng thật, con số như sau. PostgreSQL 16.14, 10 triệu bút toán trải hai năm, phần dữ liệu 651 MB, index trên posted_at 214 MB. Câu bọc DATE() chạy hết 187,6 ms và đọc 83.334 trang, tức toàn bộ bảng. Dòng đáng đọc trong EXPLAIN ANALYZE không phải thời gian mà là dòng này:

Parallel Seq Scan on postings
  Filter: (date(posted_at) = '2026-05-04'::date)
  Rows Removed by Filter: 3328770

Mỗi worker vứt đi hơn 3,3 triệu dòng để giữ lại vài nghìn. Giờ hỏi lại đúng câu ấy, nhưng hỏi trên trục mà index đã sắp — cùng dữ liệu, cùng index, không tạo thêm gì:

SELECT count(*), sum(amount)
FROM postings
WHERE posted_at >= TIMESTAMP '2026-05-04'
  AND posted_at <  TIMESTAMP '2026-05-05';

5,9 ms, đọc 12.695 trang, trả về đúng 13.690 dòng như câu cũ. Nhanh hơn khoảng 32 lần, mà thứ thay đổi không phải index: chỉ là hình dạng câu hỏi.

Cùng bệnh này còn có WHERE UPPER(email) = ... và WHERE CAST(so_tk AS TEXT) LIKE .... Dấu hiệu chung của cả họ: giữa cột và index có cái gì đó chen vào.

Hai trường hợp nữa cũng cho ra Seq Scan nhưng theo cơ chế khác, và đáng tách riêng. Thứ nhất là LIKE '%1234' với ký tự đại diện đứng đầu: chẳng có hàm nào bọc cột cả, chỉ là B-tree được sắp từ đầu chuỗi nên nó chỉ trả lời được câu hỏi dạng "bắt đầu bằng gì", không trả lời được "kết thúc bằng gì".

Thứ hai thì khó thấy hơn nhiều, vì câu lệnh trông sạch sẽ. Trên database chạy collation thường gặp như en_US.utf8, ngay cả LIKE 'abc%' — tiền tố đàng hoàng, ký tự đại diện nằm cuối — cũng không dùng được index B-tree mặc định. Lý do là index ấy sắp chuỗi theo quy tắc ngôn ngữ, còn phép so khớp tiền tố cần thứ tự so từng ký tự theo mã. Bảng 300.000 dòng trong máy tôi cho Seq Scan 9,3 ms; dựng thêm một index khai rõ lớp toán tử so theo mã ký tự thì xuống Index Only Scan 0,106 ms:

CREATE INDEX idx_ttext_pat ON ttext(s text_pattern_ops);

Ngõ cụt: lúc tôi tưởng planner chọn sai

Bệnh thứ hai thì ngược hẳn: predicate viết chuẩn, index tra được, nhưng khi lượng dòng khớp đủ lớn thì index chỉ đưa con trỏ chứ không đưa dữ liệu, và planner tính ra quét thẳng rẻ hơn nên chủ động bỏ. Bệnh thứ nhất xử được ba trong bốn ngôi mộ. Cái thứ tư mới là chỗ tôi mất cả buổi chiều.

Nó phục vụ một báo cáo quét khoảng 90 ngày, và predicate của báo cáo đó viết chuẩn ngay từ đầu: dạng khoảng, không hàm nào bọc, không ép kiểu nào chen giữa. idx_scan vẫn bằng 0.

Giả thuyết đầu nghe hiển nhiên tới mức tôi chạy luôn không kịp nghĩ: thống kê cũ. Bảng ghi nặng, ANALYZE không chạy đủ gần đây thì planner ước lượng sai số dòng khớp là chuyện thường ngày. Tôi chạy ANALYZE postings, mở lại EXPLAIN. Vẫn Seq Scan, không xê dịch một dòng nào.

Giả thuyết thứ hai thì tự tin hơn: planner đang chọn sai, và tôi sẽ chứng minh được. PostgreSQL có sẵn công tắc để ép nó bỏ quét thẳng:

SET enable_seqscan = off;

Tôi tưởng mình sắp cầm tang chứng trong tay. Kết quả đi ngược lại:

Ép dùng index thì chậm hơn, và đọc nhiều hơn đúng 3.371 trang, tức phần trang index phải lật thêm. Planner không sai. Tôi sai.

Trong kế hoạch bị ép còn hai dòng nói thẳng vì sao:

Heap Blocks: exact=17284 lossy=11406
Rows Removed by Index Recheck: 1159121

lossy nghĩa là tấm bitmap dựng từ index không vừa work_mem (mặc định 4 MB), nên nó tụt xuống ghi nhớ ở mức trang thay vì mức dòng. Với những trang đó, PostgreSQL phải đọc nguyên trang rồi kiểm lại điều kiện trên từng dòng, và 1.159.121 dòng bị loại ở đúng bước kiểm lại ấy. Index sinh ra để tránh chính việc đó.

Một dòng EXPLAIN, hai bệnh

Cơ chế đằng sau cột phải nằm ở một chỗ dễ quên: index cho bạn con trỏ, không cho bạn dữ liệu. Khoảng 90 ngày khớp 1.232.429 dòng, và dữ liệu của chúng nằm ở heap, rải trên 83.334 trang. Vì bút toán trong bảng này được sinh rải rác chứ không xếp theo thời gian, 1,2 triệu con trỏ ấy trỏ tới gần như mọi trang trong bảng. Tra index xong thì vẫn phải đọc gần hết bảng, chỉ khác là đọc theo thứ tự lộn xộn và tốn thêm 3.371 trang index nữa. Quét thẳng từ đầu tới cuối, tuần tự, đọc ít trang hơn.

Ranh giới ấy đo được. Nới dần khoảng ngày trên cùng bảng đó:

Giữa 70 và 75 ngày, planner đổi ý. Đáng chú ý là cột cuối: chỗ nó đổi ý cũng đúng là chỗ quét thẳng bắt đầu nhanh hơn thật, 155,5 ms so với 201,2 ms. Nó không bỏ index vì lười.

Từ đây có một phép thử phân biệt hai bệnh gọn hơn nhiều so với việc ngồi đoán. Bật enable_seqscan = off rồi chạy lại: nếu kế hoạch chuyển sang index mà chậm hơn thì bạn đang ở bệnh thứ hai, planner đúng, đừng đắp thêm gì cả. Còn nếu kế hoạch vẫn là Seq Scan thì đó là bệnh thứ nhất — công tắc kia chỉ cộng một khoản phạt vào chi phí chứ không cấm được, và khi index không dùng được thì phạt bao nhiêu cũng vô nghĩa. Trên câu bọc DATE() ở đầu bài, ép xong EXPLAIN in ra Seq Scan với chi phí khởi điểm 10000000000.00, đúng khoản phạt đó nằm chình ình.

Ngưỡng mười phần trăm ấy không phải hằng số

Con số quanh 10% ở bảng trên rất dễ bị nhớ thành quy tắc. Nó không phải quy tắc, và cách phá nhanh nhất là đổi đúng một thứ: thứ tự vật lý của các dòng trên đĩa.

PostgreSQL theo dõi việc đó bằng correlation, mức khớp giữa thứ tự giá trị trong cột và thứ tự các dòng nằm trên đĩa. Bảng test của tôi sinh ngẫu nhiên nên correlation chỉ 0,013, nghĩa là hai dòng liền nhau theo thời gian có thể nằm ở hai đầu đĩa. Lệnh CLUSTER postings USING idx_postings_posted_at xếp lại các dòng theo đúng thứ tự index, đưa correlation lên 1. Không thêm index nào, không sửa câu truy vấn nào:

Chỗ tiết kiệm nằm ở số trang phải lật, và nó nhìn được:

Vẫn chừng ấy con trỏ từ index, nhưng khi các dòng của 90 ngày nằm cạnh nhau trên đĩa thì chúng chỉ chiếm 16.551 trang thay vì rải khắp 83.334 trang. Index lúc này tiết kiệm thật, nên planner dùng. Ngưỡng vì thế dịch đi rất xa: sau khi xếp lại, ngay cả khoảng 180 ngày với 2.467.276 dòng, gần một phần tư bảng, planner vẫn chọn index và chạy hết 113,6 ms.

Chi tiết này nghiêng về phía bảng thật nhiều hơn bảng test: bút toán ngoài đời được ghi vào theo thứ tự thời gian, nên posted_at gần như đã sẵn correlation cao mà không ai phải làm gì. Đó là lý do một index theo thời gian trên bảng chỉ-thêm thường sống khoẻ hơn nhiều so với những gì con số 10% gợi ý, và cũng là lý do đừng bê ngưỡng của bảng này sang bảng khác.

Nhưng phép thử ấy còn một vế thứ hai, và vế đó mới là chỗ đáng nhớ. Sau khi CLUSTER, tôi chạy lại đúng câu bọc DATE() ở đầu bài, trên một cái bảng giờ đã ở điều kiện lý tưởng nhất mà index có thể mơ tới.

163,4 ms. Vẫn Parallel Seq Scan. Không nhúc nhích một milimét.

Khác biệt giữa hai bệnh nằm gọn ở đó. Bệnh thứ hai nhạy với đủ thứ: lượng dòng khớp, cách dữ liệu nằm trên đĩa, work_mem, bộ nhớ đệm. Bệnh thứ nhất chẳng nhạy với gì cả. Predicate đã bọc cột trong một hàm thì index đứng ngoài cuộc chơi, và không có cách sắp xếp nào cứu nổi.

-- CHAM: ham boc quanh cot, index tren posted_at nam ngoai cuoc
WHERE DATE(posted_at) = DATE '2026-05-04'

-- NHANH: cung ket qua, nhung hoi dung truc ma index da sap
WHERE posted_at >= TIMESTAMP '2026-05-04'
  AND posted_at <  TIMESTAMP '2026-05-05'

CREATE INDEX idx_postings_ngay ON postings((DATE(posted_at)));

Bốn ngôi mộ hôm đó chia làm hai nhóm không đều nhau. Ba cái sinh ra cho những câu truy vấn bọc hàm quanh cột, nên chúng chưa từng có cơ hội được dùng dù chỉ một lần; sửa predicate xong là xoá được cả ba. Cái thứ tư thì phục vụ một báo cáo quét quá rộng, và planner sẽ tiếp tục không dùng nó kể cả khi tôi không xoá. Bảng nhẹ đi, và mỗi lần ghi bút toán bớt được bốn cây B-tree phải cập nhật theo.

Câu truy vấn pg_stat_user_indexes ở đầu bài giờ là mục thứ nhất trong checklist rà soát hằng quý của team, kèm một dòng chú thích tôi viết cho chính mình đọc lại: cột idx_scan không chấm điểm index của bạn, nó chấm điểm hình dạng câu hỏi bạn đặt cho database. Quý sau, người mở checklist ra chạy mục đó không phải tôi mà là cậu junior hôm ấy, và câu hỏi cậu mang sang lần này khó hơn hẳn: có cách nào biết trước một index sẽ vô dụng, trước khi tạo nó không?

Tôi chưa trả lời được ngay, nên phải đi học tử tế phần mình còn thiếu: đọc một plan thì tìm manh mối ở đâu, cost được tính ra từ những gì, vì sao thống kê lệch lại đẻ ra kế hoạch tồi. Bấy nhiêu nằm ở Các chiến lược scan và Statistics và cost model, hai bài liền nhau trong khoá PostgreSQL Internals.

Còn Chẩn đoán plan tồi thì là bài tôi ước mình đã đọc trước cái buổi chiều hôm ấy.