Chỉ mục biểu thức

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:

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));

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)):

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';

Đ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:

Đú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.

Thử ba mươi giây

Tìm truy vấn đang bọc hàm quanh cột trong hệ thống của bạn:

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.