Hình dung một cuốn danh bạ sắp theo họ, viết đúng nguyên dạng có hoa có thường. Bây giờ ai đó hỏi "tìm mọi người có họ, viết-thường ra, bằng 'nguyen'". Cuốn danh bạ chịu thua: nó được sắp theo dạng gốc của cái họ, chứ không theo dạng đã viết-thường, nên muốn trả lời bạn phải viết-thường từng cái họ một rồi dò — tức là lật cả cuốn. Muốn nhanh, bạn phải giữ thêm một cuốn danh bạ thứ hai sắp theo dạng viết-thường. Và — đây là cái bẫy tinh vi — cuốn thứ hai chỉ giúp đúng cái phép biến đổi ấy: một cuốn sắp theo viết-thường không giúp gì cho câu hỏi "sắp theo viết-hoa" hay "cắt khoảng trắng rồi viết-thường". Chỉ mục biểu thức của PostgreSQL là chính cuốn danh bạ thứ hai đó. Chỉ mục lưu giá trị của cột. Khi truy vấn hỏi về giá trị của một hàm áp lên cột, chỉ mục đó trở nên vô dụng — và đây là một trong những cách phổ biến nhất khiến chỉ mục có mà không ai dùng.
Một hàm là đủ để mất chỉ mục
Bảng 3.000.000 dòng, cột email đã có chỉ mục:
| Truy vấn | Kế hoạch | Thời gian | Số trang |
|---|---|---|---|
WHERE email = '...' |
Index Only Scan | 0,22 ms | 5 |
WHERE lower(email) = lower('...') |
Seq Scan | 197,67 ms | 34.081 |
Chậm 900 lần. Chỉ mục vẫn nằm đó, vẫn chiếm chỗ, vẫn làm mọi lần ghi tốn thêm — và không phục vụ truy vấn này.
Lý do: chỉ mục xếp theo email, tức theo 'Nguoi.Dung1@ViDu.COM'. Truy vấn hỏi về 'nguoi.dung1@vidu.com'. PostgreSQL không có cách nào biết hai chuỗi đó liên quan mà không tính lower() cho từng dòng — và tính cho từng dòng chính là quét toàn bảng.
Cách chữa là đánh chỉ mục lên chính biểu thức:
CREATE INDEX ix_lower ON dh (lower(email));
| Kế hoạch | Thời gian | |
|---|---|---|
| Sau khi thêm chỉ mục biểu thức | Index Scan | 0,17 ms |
Nhanh lại 1.163 lần. Chỉ mục tốn 142 MB và mất 1,84 s để dựng.
Biểu thức phải khớp chính xác
Đây là chỗ hay gây bất ngờ. Với chỉ mục ON dh (lower(email)):
| Điều kiện | Kết quả |
|---|---|
lower(email) = ... |
Index Scan, 0,05 ms |
upper(email) = ... |
Seq Scan, 188,80 ms |
lower(trim(email)) = ... |
Seq Scan, 259,68 ms |
email ILIKE ... |
Seq Scan, 318,68 ms |
PostgreSQL không suy luận rằng upper() và lower() cùng phục vụ mục đích so sánh không phân biệt hoa thường. Nó so sánh cây biểu thức, và cây phải giống hệt.
Dòng thứ ba đáng chú ý nhất: chỉ thêm một trim() vào giữa là mất chỉ mục. Đây là lỗi rất dễ mắc khi ai đó "làm sạch dữ liệu cho chắc" trong một truy vấn đã chạy tốt nhiều tháng.
Và dòng cuối: ILIKE không tương đương lower(...) = lower(...) với bộ tối ưu, dù về kết quả thì tương đương. Muốn ILIKE nhanh cần pg_trgm với chỉ mục GIN — phần 16.
Hai giới hạn phải biết trước
Hàm phải là IMMUTABLE
Tôi thử tạo chỉ mục cho việc lọc theo tháng:
CREATE INDEX ON dh ((to_char(ngay, 'YYYY-MM')));
ERROR: functions in index expression must be marked IMMUTABLE
to_char trên kiểu ngày là STABLE, không phải IMMUTABLE — kết quả của nó phụ thuộc DateStyle và lc_time của phiên làm việc. Một chỉ mục xây bằng nó có thể sai ngay khi ai đó đổi cấu hình.
Kiểm tra trước khi thiết kế:
SELECT proname, provolatile FROM pg_proc WHERE proname = 'ten_ham';
| Giá trị | Nghĩa | Dùng được trong chỉ mục? |
|---|---|---|
i |
immutable | ✅ |
s |
stable | ❌ |
v |
volatile | ❌ |
Đo được: lower và date_part là i; now là s; random là v.
Tôi suýt bỏ qua chuyện này. Lần chạy đầu tôi viết CREATE INDEX ... >/dev/null cho gọn, rồi thấy truy vấn vẫn Seq Scan và tưởng chỉ mục "không được chọn". Thật ra nó chưa bao giờ được tạo — thông báo lỗi đã bị nuốt mất. Đây là lần thứ n trong hai sê-ri liền việc giấu đầu ra làm hỏng một phép đo.
Chỉ mục biểu thức vẫn phải đủ chọn lọc
Chỉ mục ON dh ((extract(year from ngay))) tạo được. Nhưng nó chỉ được dùng khi đáng dùng:
| Năm | Tỉ lệ dòng | Kế hoạch |
|---|---|---|
| 2023 | 11,3% | Bitmap Heap Scan dùng chỉ mục |
| 2026 | 21,9% | Bitmap Heap Scan dùng chỉ mục |
| 2024 | 33,4% | Seq Scan |
| 2025 | 33,3% | Seq Scan |
Đúng ngưỡng mà phần 10 đã đo. Chỉ mục biểu thức không phải phép màu — nó chỉ đưa biểu thức vào cuộc chơi bình thường của bộ tối ưu.
Với lọc theo năm hay theo tháng, cách tốt hơn vẫn là viết thành khoảng trên cột gốc:
WHERE ngay >= '2025-01-01' AND ngay < '2026-01-01'
Nó dùng được chỉ mục thường trên ngay, không cần chỉ mục riêng, và không dính giới hạn IMMUTABLE.
Cột sinh: cách thay thế dễ đọc hơn
Từ PostgreSQL 12, bạn có thể lưu hẳn giá trị đã tính thành một cột:
CREATE TABLE gc (
id bigserial PRIMARY KEY,
email text,
email_thuong text GENERATED ALWAYS AS (lower(email)) STORED
);
CREATE INDEX ON gc (email_thuong);
SELECT count(*) FROM gc WHERE email_thuong = lower('...');
-- Index Only Scan, 0,07 ms
Ưu điểm: truy vấn đọc dễ hơn, và không có nguy cơ ai đó viết upper() rồi mất chỉ mục — cột đã mang sẵn dạng chuẩn hoá.
Nhược điểm: tốn thêm chỗ trong bảng chứ không chỉ trong chỉ mục. Bảng thử của tôi 89 MB dữ liệu kèm 69 MB chỉ mục.
Chọn cột sinh khi giá trị đã tính là một khái niệm nghiệp vụ thật (email chuẩn hoá, slug, mã tìm kiếm); chọn chỉ mục biểu thức khi nó chỉ là chi tiết tối ưu.
Ba biểu thức đáng đánh chỉ mục
-- tim khong phan biet hoa thuong
CREATE INDEX ON nguoi_dung (lower(email));
-- tim theo phan cuoi chuoi (dao nguoc roi dung tien to)
CREATE INDEX ON dien_thoai (reverse(so));
-- truong trong JSONB
CREATE INDEX ON su_kien ((du_lieu ->> 'ma_khach'));
Cái thứ ba đặc biệt hữu ích và hay bị bỏ qua — phần 34 sẽ đo kỹ JSONB.
Mẫu số chung
Cuốn danh bạ thứ hai ở đầu bài chỉ ra một luật đúng với mọi bộ tăng tốc tra cứu, không riêng chỉ mục: nó được dựng trên một dạng chuẩn của khoá, nên muốn hưởng lợi thì đường ghi và đường đọc phải sinh ra dạng chuẩn giống hệt nhau tới từng byte, bằng đúng một hàm. Chuẩn hoá phải xảy ra ở cả hai phía. Và hệ thống kiểm chuyện đó bằng cách khớp cú pháp — nó không bao giờ suy luận rằng upper và lower là hai cách nói của cùng một ý.
Cùng cái bẫy ấy, đổi ngăn xếp:
- Trong Java, một
HashMap<String, V>khoá bằng chuỗi gốc không trả lời được câu "tìm theo khoá đã viết-thường". Bạn phải chuẩn hoá khoá —key.toLowerCase().trim()— cả lúcputlẫn lúcget. Lỗi kinh điển là chuẩn hoá lúc ghi mà quên lúc đọc (hoặc ngược lại), rồi ngồi tìm vì saogetluôn trảnulldù dữ liệu rõ ràng có đó. Đúng như thêm mộttrim()làm mất chỉ mục. - Trong Elasticsearch, đây là cái footgun nổi tiếng "lệch analyzer": văn bản được phân tích (viết thường, tách gốc từ) lúc lập chỉ mục, và câu truy vấn phải đi qua đúng analyzer đó — dùng analyzer khác lúc truy vấn là không khớp gì cả, y hệt
uppervớilower. - Với mọi cache hay memo, khoá phải được đưa về một dạng chính tắc y như nhau ở mọi lối vào; chuẩn hoá khoá không nhất quán là nguồn của những lần "miss" bí ẩn mà dữ liệu rõ ràng đã nằm sẵn.
Nên câu hỏi đúng khi một truy vấn có chỉ mục mà vẫn quét toàn bảng không phải "chỉ mục có tồn tại không" mà là "cái dạng tôi đang hỏi có khớp đúng cái dạng mà bộ tăng tốc được dựng trên không". Muốn tìm mọi chỗ đang bọc hàm quanh cột trong hệ của bạn, soát nhanh:
SELECT calls, round(mean_exec_time::numeric,1) AS trung_binh_ms, query
FROM pg_stat_statements
WHERE query ~* '(lower|upper|trim|to_char|date_trunc|extract)\s*\('
AND query ~* 'where'
ORDER BY total_exec_time DESC
LIMIT 10;
(Cần bật pg_stat_statements — phần 54 sẽ đo.) Và với mỗi truy vấn nghi ngờ, chạy EXPLAIN rồi xem dòng Filter. Điều kiện nằm ở Filter thay vì Index Cond nghĩa là PostgreSQL đang đọc dòng lên rồi mới tính hàm — chính là thứ chỉ mục biểu thức chữa được.
Phần sau đo Index Only Scan: điều kiện để nó xảy ra, và vì sao đôi khi nó không xảy ra dù đủ cột.
