Vì sao PostgreSQL không dùng index? Cơ bản

Vấn đề

Bảng account_demo 100.000 dòng, có index trên email và status:

SELECT id FROM account_demo WHERE status = 'active';   -- khớp 90.000/100.000 dòng

EXPLAIN cho Seq Scan. Phản ứng quen thuộc: “PostgreSQL bỏ qua index của tôi”. Câu hỏi đúng: nếu ép dùng index, có nhanh hơn không? Câu trả lời quyết định bạn đang sửa cái gì.

Quy trình chẩn đoán

Đang tải diagram…

Nguyên tắc nền: index chỉ là một lựa chọn cho planner. Planner ước lượng cost mọi phương án và chọn rẻ nhất theo ước lượng. “Không dùng index” nghĩa là: theo ước lượng hiện tại, có cách rẻ hơn.

Bước 1: predicate có khớp index không?

Nhìn Index Cond trong plan. Điều kiện chỉ xuất hiện ở Filter nghĩa là PostgreSQL đọc dòng lên rồi mới kiểm tra, index không giúp định vị.

Dạng không khớp	Ví dụ	Cách sửa
Hàm bọc cột	lower(email) = ..., date(created_at) = ...	Expression index đúng biểu thức (bài này) hoặc viết lại thành khoảng
Ép kiểu phía cột	cột bigint so với tham số numeric, id::text = '42'	Truyền đúng kiểu; ép kiểu phía hằng số
Thiếu cột đầu composite	WHERE status = ... trên index (tenant_id, status, ...)	Xem thứ tự cột
LIKE sai dạng/locale	LIKE 'abc%' với locale không phải C	text_pattern_ops, xem B-tree
OR giữa các cột	a = 1 OR b = 2	Mỗi nhánh cần index riêng để planner gộp bằng BitmapOr

Ép kiểu phía cột: bẫy ORM kinh điển

Bảng orders_demo 300.000 dòng, user_id bigint có index. Chỉ khác kiểu của hằng số:

Query	Plan	Thời gian
user_id = 42::bigint	Index Cond: (user_id = '42'::bigint)	0.049 ms
user_id = 42::int	Index Cond: (user_id = 42)	0.025 ms
user_id = 42::numeric	Filter: ((user_id)::numeric = '42'::numeric), loại 299.994 dòng	14.7 ms
user_id::text = '42'	Filter: ((user_id)::text = '42'::text), loại 299.994 dòng	12.5 ms

int so với bigint an toàn vì operator family của B-tree số nguyên có sẵn toán tử so sánh chéo kiểu (bigint = integer), nên cột giữ nguyên và index định vị được (plan hiện user_id = 42, không cast).

numeric thì phải ép cột sang numeric, nên index trên bigint không dùng để định vị được. Ở đây index vẫn được duyệt (Index Only Scan) nhưng toàn bộ 300.000 entry, chỉ khác là điều kiện nằm ở Filter. Thời gian tăng tuyến tính theo kích thước bảng.

Ví dụ thật: ứng dụng truyền user_id dưới dạng số thập phân hoặc chuỗi qua driver, query chạy tốt ở staging 10.000 dòng rồi chậm dần theo bảng. Dấu hiệu duy nhất là Filter có ::numeric hoặc ::text bao quanh cột.

Predicate khớp sau khi sửa: expression index

Index trên email không sắp theo lower(email):

Seq Scan on account_demo  (cost=0.00..2333.00 rows=500 width=4) (actual time=5.405..12.695 rows=1 loops=1)
  Filter: (lower(email) = 'user42000@example.test'::text)
  Rows Removed by Filter: 99999
Execution Time: 12.704 ms

Thêm CREATE INDEX ON account_demo (lower(email)):

Index Scan using account_demo_lower_email_idx on account_demo  (cost=0.42..8.44 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=1)
  Index Cond: (lower(email) = 'user42000@example.test'::text)
Execution Time: 0.038 ms

12.704 ms thành 0.038 ms, nhanh hơn 334 lần. Chú ý ước lượng đổi từ rows=500 sang rows=1: ANALYZE sau khi tạo index đã thu thống kê cho biểu thức, nên planner hết đoán mò. Biểu thức trong index phải IMMUTABLE.

Bước 2: index có hợp lệ không?

CREATE INDEX CONCURRENTLY thất bại giữa chừng (ví dụ lỗi trùng key khi tạo unique) không tự xoá index, mà để lại ở trạng thái INVALID. Planner bỏ qua nó hoàn toàn, nhưng nó vẫn tốn chi phí ghi.

ERROR:  could not create unique index "dup_demo_code_uq"
DETAIL:  Key (code)=(1) is duplicated.

 relname          | indisvalid
------------------+-----------
 dup_demo_code_uq | f

Seq Scan on dup_demo
  Filter: (code = 7)

Tìm index invalid: SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;. Xử lý: DROP INDEX CONCURRENTLY rồi tạo lại sau khi sửa dữ liệu.

Bước 3: ép index, đo lại

Nếu predicate khớp và index hợp lệ, hãy hỏi planner có đúng không. SET enable_seqscan = off chỉ cộng một khoản cost rất lớn vào Seq Scan, không cấm hẳn. Tài liệu viết: không thể loại bỏ hoàn toàn sequential scan. PG18 còn đánh dấu node bị disable ngay trong EXPLAIN.

Trường hợp 1: planner đúng (selectivity 90%)

Query status = 'active', planner tự chọn:

Seq Scan on account_demo  (cost=0.00..2083.00 rows=90160 width=4) (actual time=0.004..4.517 rows=90000 loops=1)
  Filter: (status = 'active'::text)
  Rows Removed by Filter: 10000
Execution Time: 6.243 ms

Sau SET enable_seqscan = off:

Bitmap Heap Scan on account_demo  (cost=1015.03..2975.03 rows=90160 width=4) (actual time=1.049..4.669 rows=90000 loops=1)
  Recheck Cond: (status = 'active'::text)
  Heap Blocks: exact=833
  ->  Bitmap Index Scan on account_demo_status_idx
Execution Time: 6.444 ms

6.243 ms so với 6.444 ms: không nhanh hơn (chênh 3% là nhiễu). Phải đọc 90% bảng thì không cách nào rẻ hơn đọc tuần tự. Planner không sai; sửa ở tầng query: lấy ít dòng hơn, phân trang, hoặc partial index cho phần hiếm.

Trường hợp 2: planner đúng nhưng cost model nhạy với random_page_cost

Bảng event_demo 500.000 dòng xếp ngẫu nhiên (correlation của score là -0.0015), WHERE score < 500000 (khoảng 50%), bảng nằm trong cache:

Cấu hình	Plan	Cost	Thời gian
Mặc định (random_page_cost = 4)	Seq Scan	14.890	21.5 ms
enable_seqscan = off	Bitmap Heap Scan	16.270	30.4 ms
random_page_cost = 1.1	Bitmap Heap Scan	14.396	24.8 ms

Hạ random_page_cost làm bitmap rẻ hơn Seq Scan trên giấy (14.396 so với 14.890) và plan đổi theo. Trong lần đo này Seq Scan vẫn nhanh nhất (số thời gian dao động giữa các lần chạy, đo lại trên máy bạn). Bài học: đổi hằng số cost phải kiểm chứng bằng thời gian thật, không tin con số cost.

Trường hợp 3: ép index nhanh hơn rõ rệt, nhưng planner không tự chọn

Đây mới là dấu hiệu cost model hoặc thống kê sai. Kiểm tra theo thứ tự:

1. Thống kê cũ hoặc chưa có: chạy ANALYZE; autovacuum không phân tích được TEMP table. Xem planner statistics.
2. Cột tương quan: ước lượng nhân xác suất sai; dùng CREATE STATISTICS.
3. random_page_cost: mặc định 4.0 giả định phần lớn truy cập ngẫu nhiên đã nằm trong cache. Tài liệu nói hạ giá trị này có thể phù hợp khi dữ liệu nằm hết trong cache; nhiều đội production chỉnh về khoảng 1.1 cho SSD, nhưng hãy đo trên workload của bạn.
4. effective_cache_size: mặc định 4GB; đặt gần phần RAM thật sự dành cho cache để planner đánh giá index đúng.
5. Generic plan: prepared statement dùng 5 lần custom plan đầu, sau đó có thể chuyển sang generic plan không biết giá trị tham số (xem bên dưới).
6. LIMIT làm lệch ước lượng: ORDER BY ... LIMIT có thể khiến planner quét ngược theo index khoá chính, hy vọng gặp đủ dòng sớm.

Generic plan: partial index và prepared statement

Với plan_cache_mode = auto (mặc định), 5 lần thực thi đầu dùng custom plan, sau đó PostgreSQL so cost generic plan với trung bình custom plan và có thể dùng generic cho các lần sau.

Generic plan không biết giá trị tham số, nên ước lượng theo giá trị trung bình và có thể chọn sai khi dữ liệu lệch, hoặc không dùng được partial index.

Chẩn đoán bằng SET plan_cache_mode = force_custom_plan rồi force_generic_plan và so hai plan. (Demo ở bài không tái hiện được sự khác biệt này trên dữ liệu nhỏ.)

Khi nào dùng / không dùng enable_*

Dùng	Không dùng
Trong một session để chẩn đoán một query	Đặt thường trực trong ứng dụng hoặc connection pool
Trả lời “index plan có nhanh hơn không”	Làm giải pháp lâu dài thay cho ANALYZE hoặc đúng index

🎮 Thử tay

• Query chậm dần: index sai cột, enable_seqscan = off và vì sao không giúp.

Checklist production

1. Chạy EXPLAIN (ANALYZE, BUFFERS) với tham số thật lấy từ log, không phải giá trị đồ chơi.
2. Tìm Filter có ::numeric, ::text hoặc hàm quanh cột: sửa kiểu hoặc thêm expression index.
3. SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; định kỳ.
4. Kiểm tra pg_stat_user_tables.last_autoanalyze; ANALYZE sau khi nạp lượng lớn dữ liệu.
5. Ép bằng enable_seqscan = off trong một session, so thời gian thật.
6. Nếu ép nhanh hơn: xem thống kê, random_page_cost, effective_cache_size, generic plan.
7. Nếu ép không nhanh hơn: sửa ở tầng query (ít dòng hơn, keyset pagination).

Ví dụ thực tế

Endpoint chậm dần sau khi bảng orders lên 5 triệu dòng

Query WHERE customer_id = $1 chạy tốt ở staging, chậm dần ở production. EXPLAIN hiện Filter: ((customer_id)::numeric = '42'::numeric): driver gửi tham số numeric còn cột là bigint. Index vẫn tồn tại nhưng không dùng để định vị được.

Cách chẩn đoán: lấy tham số thật từ log, EXPLAIN (ANALYZE, BUFFERS), thấy Filter có cast quanh cột. Sửa: ép kiểu tham số ở phía ứng dụng. Không cần thêm index, chỉ cần để query khớp index đã có.

Trade-offs

Ưu điểm

• enable_seqscan = off trả lời được bằng số: ép index có nhanh hơn hay không, thay vì đoán
• Phân biệt được “không khớp cấu trúc”, “không có lợi” và “cost model sai”: ba bệnh cần ba thuốc khác nhau
• Index Cond so với Filter cho biết điều kiện định vị hay chỉ lọc sau

Nhược điểm

• EXPLAIN ANALYZE chạy query thật; cẩn trọng với tải và side effect trên production
• Kết quả phụ thuộc dữ liệu và thống kê hiện tại, bảng nhỏ cho kết luận khác bảng lớn
• enable_* chỉ cộng cost, không cấm; không dùng làm cấu hình lâu dài

Sai lầm thường gặp

Sai lầm thường gặp

Để SET enable_seqscan = off chạy thường trực vì nó “sửa” một query chậm. Nó chỉ cộng một khoản cost lớn cho Seq Scan, ảnh hưởng mọi query khác cùng session hoặc connection pool. Nếu ép index thật sự nhanh hơn, hãy sửa nguyên nhân (thống kê, đúng index, random_page_cost) rồi trả cấu hình về mặc định.

Tóm tắt

Tóm tắt

• Index chỉ là một lựa chọn; “không dùng” nghĩa là theo ước lượng có cách rẻ hơn, không phải lỗi
• Kiểm tra theo thứ tự: predicate khớp (Index Cond hay Filter), index hợp lệ, rồi mới ép enable_seqscan = off so sánh
• Ép không nhanh hơn: planner đúng, sửa ở tầng query. Ép nhanh hơn rõ rệt: cost model hoặc thống kê sai
• Bẫy phổ biến nhất: ép kiểu phía cột (numeric, text). Trong demo 14.7 ms so với 0.049 ms trên 300.000 dòng, tỉ lệ theo kích thước bảng
• enable_* chỉ cộng cost rất lớn, không cấm; hạ random_page_cost đổi plan nhưng phải kiểm chứng bằng thời gian thật

Tự kiểm tra

1. Vì sao bigint = 42::int dùng được index còn bigint = 42::numeric thì không?
2. Ép bằng enable_seqscan = off mà plan chậm hơn thì kết luận gì? Nhanh hơn thì kết luận gì?
3. Index INVALID xuất hiện thế nào và planner xử lý ra sao?
4. Vì sao hạ random_page_cost có thể làm plan đổi nhưng chưa chắc nhanh hơn?

Tham khảo

• PostgreSQL 17 — Examining Index Usage
• PostgreSQL 17 — Planner Method Configuration
• PostgreSQL 17 — Planner Cost Constants
• PostgreSQL 17 — PREPARE